Organize and Sum Data

[showboat]

Hello, I am looking for help on this spreadsheet.

The ISO Entry tab is for manual data entry.

On the Line # tab, I would like all of the information from the ISO Entry tab relisted where any lines that have the same Area Code, Plan Code, System Code, Pipe Size, and Insulation Thickness; The information under Straight Pipe, 90's/45's, Misc, Valves, Flanges is summed up and shown as 1 line item instead of multiple line items.

On the Pipe Size tab, I would like the information listed where any line that has the same Pipe Size and Insulation Thickness; The information under Straight Pipe, 90's/45's, Misc, Valves, Flanges is summed up and shown as 1 line item instead of multiple line items.


On the ISO tab, the row that looks like this "3"-SC2500-Q-CS02-2" is the Line Number. Everything above that is the information for that line number.

The Line Number tells us 4 things. Area Code, Plan Code, System Code, and Insulation Thickness. I would like an entry table to be able to tell the program where each of those are located in the Line Number.

Example:
Area Code = Q or A or etc.
Plan Code = SC or SH or CW or etc.
System Code = 2554 or 2835 or etc.
Insulation thickness = First number from the right (so 2" in above Line Number - or sometimes it will say 2EG at the end where 2 is still the thickness)

If the value under the "Size" column is "#x#", then I would like it displayed as the larger number.

For the columns 90s, Misc, Valves, Flanges, 45s - you will need to search for keywords under the Description Column on ISO tab. I would like a table where I can tell the program what to look for under each. Up to 10 different words under each column.

Example:
90s = 90
Misc = Tee, Reducer, Stubend
Valves = Valve, Expansion Joint
Flanges = Flanges
45s = 45

I would like 1 more entry table where I can Enter in the Plan Code and then enter in a color, and all of those lines are then highlighted that color. EX: CW = Blue

Thanks for the help!!

[showboat]

Can anyone help me?

[jindon]

Example:
Area Code = Q or A or etc.
Plan Code = SC or SH or CW or etc.
System Code = 2554 or 2835 or etc.
Insulation thickness = First number from the right (so 2" in above Line Number - or sometimes it will say 2EG at the end where 2 is still the thickness)

What do you mean by etc?

If you upload a workbook with your desired result that clearly showing the logic, it would help to understand.

[showboat]

See attached workbook

[jindon]

Here's just to get correct format of Line # when any hyphen is missing.
The rest, I don't understand why each column gets such number/value.

Sub test()
    Dim a, i As Long, m As Object, n As Long, x, t, w
    Dim ACode As String, PCode As String, SCode As String, IThick As String
    With Sheets("iso entry")
        a = .Range("a3", .Range("a" & Rows.Count).End(xlUp)).Resize(, 4).Value: n = 1
    End With
    With CreateObject("VBScript.RegExp")
        .Pattern = "^(\d+"")-?([A-Z]{2}\d{4})-?([A-Z])-?([A-Z]+\d+)-?(\d+[A-Z]*)$"
        For i = 1 To UBound(a, 1)
            If .test(a(i, 1)) Then
                a(i, 1) = .Replace(a(i, 1), "$1-$2-$3-$4-$5")
                x = Split(a(i, 1), "-")
                n = n + 1
                Sheets("line #").Cells(n, 1).Resize(, 6) = Array(x(2), x(1), x(3), a(i, 1), "", Val(x(4)))
            End If
        Next
    End With
End Sub

[showboat]

Which columns are confusing? I'll try to explain

[jindon]

In Line #
1) Value in Column F:M
2) why only 1 row for 3"-SC2500-Q-CS02-2, 2 rows for 3"-SC2500-Q-CS02-2, also 2 rows for 2"-CW2554-Q-SS01-1EG?

[showboat]

Column F:M comes from the ISO Entry Tab.

Example:
Line # Tab, Row 2. Look at the Line Number 3"-SC2500-Q-CS02-2.
Now go to the ISO Entry Tab. Look at Cell A13. That is the same line number. The rows above that line number 3:12 contain the information for that line number. Cell A22 also contains that line number and rows 16:21 also contain information for that line number.
Now go back to the Line # tab. Columns G:M contain the summed up information for that line number found on the ISO Entry tab. The Line Number itself contains the Insulation Thickness value (column F). In this instance, the first # from the right hand side of the line number sequence is the Insulation Thickness. So for line number 3"-SC2500-Q-CS02-2, the first # from the right is 2.

2) The reason there are 2 rows for 3"-SC2500-Q-CS02-2, also 2 rows for 2"-CW2554-Q-SS01-1EG is because there are 2 different Pipe and Insulation Thickness Sizes. 3"-SC2500-Q-CS02-2 has 1 line because the only Pipe Size listed on the ISO Entry tab is 3"

Does this help? Any other questions?

[jindon]

Can you just breakdown the details about where the value in E, F:M come from for 2 rows of 3"-SH2500-Q-CS02-2, row 3,4 in Line # in your example?

[showboat]

Sure thing. I'll type it as a formula as that would probably be easiest. This applies for 3"-SH2500-Q-CS02-2

Line#E3=ISOEntryC25
Line#F3=The first # from the right in the Line Number sequence. 2 is the first number from the right in Line Number 3"-SH2500-Q-CS02-2
Line#G3=ISOEntryB25
Line#H3=Follow the formula that is entered into Cell H2.
Line#K3=ISOEntryB36

Line#E4=ISOEntryC26
Line#F4=The first # from the right in the Line Number sequence. 2 is the first number from the right in Line Number 3"-SH2500-Q-CS02-2
Line#G4=ISOEntryB26
Line#H4=Follow the formula that is entered into Cell H2.
Line#I4=ISOEntryB30
Line#J4=ISOEntryB28 (This value was counted because the word CAP is typed into ISOEntryG13 as a word to search for under Misc column)
Line#M4=ISOEntryB29

Does this help?

[jindon]

I don't know the conversion in G & H.

Sub test()
    Dim a, i As Long, ii As Long, iii As Long, n As Long, t As Long
    Dim x, w, mySize, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    With Sheets("iso entry")
        a = .Range("a3", .Range("a" & Rows.Count).End(xlUp)).Resize(, 4).Value
    End With
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Pattern = "^(\d+"")-?([A-Z]{2}\d{4})-?([A-Z])-?([A-Z]+\d+)-?(\d+[A-Z]*)$"
        For i = UBound(a, 1) To 1 Step -1
            If .test(a(i, 1)) Then
                a(i, 1) = .Replace(a(i, 1), "$1-$2-$3-$4-$5")
            ElseIf a(i, 1) = "" Then
                a(i, 1) = a(i + 1, 1)
            End If
        Next
        For i = 1 To UBound(a, 1)
            If Not dic.exists(a(i, 1)) Then
                Set dic(a(i, 1)) = CreateObject("Scripting.Dictionary")
            End If
            If a(i, 4) <> "" Then
                If a(i, 3) Like "*X*" Then
                    x = Split(Replace(a(i, 3), """", ""), "X")
                    mySize = Application.Max(Evaluate(x(0)), Evaluate(x(1)))
                Else
                    mySize = Evaluate(Replace(a(i, 3), """", ""))
                End If
                If Not dic(a(i, 1)).exists(mySize) Then
                    x = Split(a(i, 1), "-")
                    ReDim w(1 To 13)
                    w(1) = x(2): w(1) = x(1): w(3) = x(3)
                    w(4) = a(i, 1): w(5) = mySize: w(6) = Val(x(4))
                    dic(a(i, 1))(mySize) = w
                End If
                .Pattern = "\b((PIPE)|(90s|90LR)|(Mics|TEE|CAP)|(Valves?|STRAINER)|(FLANGES?)|(45s|45LR))\b"
                If .test(a(i, 4)) Then
                    w = dic(a(i, 1))(mySize)
                    For ii = 1 To .Execute(a(i, 4))(0).submatches.Count - 1
                        If .Execute(a(i, 4))(0).submatches(ii) <> "" Then Exit For
                    Next
                    If ii = 1 Then ii = ii - 1
                    w(ii + 7) = w(ii + 7) + a(i, 2): dic(a(i, 1))(mySize) = w
                End If
            End If
        Next
    End With
    With Sheets("line #").Cells(1).CurrentRegion
        .Offset(1).ClearContents
        a = .Offset(1).Resize(UBound(a, 1)).Value
        For i = 0 To dic.Count - 1
            For ii = 0 To dic.items()(i).Count - 1
                n = n + 1
                For iii = 1 To 13
                    a(n, iii) = dic.items()(i).items()(ii)(iii)
                Next
            Next
        Next
        .Offset(1).Resize(n).Value = a
    End With
End Sub

[showboat]

Thank you jindon. It's close but there are a few issues I noticed.

On the Line # Tab: Cell G2 reads 25'-9"110'-10". I need those numbers to be added together. So the correct value should be 136'-7". I need this displayed in feet. To convert to feet you simply divide the inches by 12. So 136'+(7/12)=136.58 feet

On the ISO Entry tab, are you able to incorporate tables 1, 2, and 3 into the Macro so I can tell the program what values it is supposed to look for?

[FDibbins]

...bump to get back to top of list

[showboat]

How do I do that?

[FDibbins]

I just did it for you

[jindon]

If you show me how those 2 columns are calculted, it will be easier.

I'm out at the moment, so my reply will be late.

[showboat]

Thank you!

[showboat]

This applies for 3"-SH2500-Q-CS02-2

Line#E3=ISOEntryC25
Line#F3=The first # from the right in the Line Number sequence. 2 is the first number from the right in Line Number 3"-SH2500-Q-CS02-2
Line#G3=ISOEntryB25
Line#H3=Follow the formula that is entered into Cell H2.
Line#K3=ISOEntryB36

Line#E4=ISOEntryC26
Line#F4=The first # from the right in the Line Number sequence. 2 is the first number from the right in Line Number 3"-SH2500-Q-CS02-2
Line#G4=ISOEntryB26
Line#H4=Follow the formula that is entered into Cell H2.
Line#I4=ISOEntryB30
Line#J4=ISOEntryB28 (This value was counted because the word CAP is typed into ISOEntryG13 as a word to search for under Misc column)
Line#M4=ISOEntryB29

When you use the Marco you created there are 2 issues.

On the Line # Tab: Cell G2 reads 25'-9"110'-10". I need those numbers to be added together. So the correct value should be 136'-7". I need this displayed in feet. To convert to feet you simply divide the inches by 12. So 136'+(7/12)=136.58 feet

On the ISO Entry tab, are you able to incorporate tables 1, 2, and 3 into the Macro so I can tell the program what values it is supposed to look for?

[jindon]

Almost there, however your formula in H3 says

=G2+('Fitting Chart'!B3*I2)+('Fitting Chart'!F3*K2)+('Line #'!L2*'Fitting Chart'!J3)

Where Col.K & Col.L in Fitting Chart are all blank.

Are you sure about this formula?

[showboat]

That's strange. It should be =G2+('Fitting Chart'!B11*I2)+('Fitting Chart'!F11*K2)+('Line #'!L2*'Fitting Chart'!J11)

[jindon]

OOps, they are not from "Fitting Chart"...

I think I can finish now, wait a moment.

[jindon]

Sucuri blocks my code.

See if attached works.