+ Reply to Thread
Results 1 to 22 of 22

Organize and Sum Data

  1. #1
    Forum Contributor
    Join Date
    05-02-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    199

    Organize and Sum Data

    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!!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    05-02-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    199

    Re: Organize and Sum Data

    Can anyone help me?

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Organize and Sum Data

    Quote Originally Posted by showboat View Post
    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.

  4. #4
    Forum Contributor
    Join Date
    05-02-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    199

    Re: Organize and Sum Data

    See attached workbook
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Organize and Sum Data

    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.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    05-02-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    199

    Re: Organize and Sum Data

    Which columns are confusing? I'll try to explain

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Organize and Sum Data

    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?

  8. #8
    Forum Contributor
    Join Date
    05-02-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    199

    Re: Organize and Sum Data

    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?

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Organize and Sum Data

    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?

  10. #10
    Forum Contributor
    Join Date
    05-02-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    199

    Re: Organize and Sum Data

    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?

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Organize and Sum Data

    I don't know the conversion in G & H.
    Please Login or Register  to view this content.
    Last edited by jindon; 07-24-2018 at 02:04 AM.

  12. #12
    Forum Contributor
    Join Date
    05-02-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    199

    Re: Organize and Sum Data

    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?

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Organize and Sum Data

    ...bump to get back to top of list
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  14. #14
    Forum Contributor
    Join Date
    05-02-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    199

    Re: Organize and Sum Data

    How do I do that?

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Organize and Sum Data

    I just did it for you

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Organize and Sum Data

    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.

  17. #17
    Forum Contributor
    Join Date
    05-02-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    199

    Re: Organize and Sum Data

    Thank you!

  18. #18
    Forum Contributor
    Join Date
    05-02-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    199

    Re: Organize and Sum Data

    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?

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Organize and Sum Data

    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?

  20. #20
    Forum Contributor
    Join Date
    05-02-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    199

    Re: Organize and Sum Data

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

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Organize and Sum Data

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

    I think I can finish now, wait a moment.
    Last edited by jindon; 08-20-2018 at 08:54 AM.

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Organize and Sum Data

    Sucuri blocks my code.

    See if attached works.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Organize and Sum Data
    By showboat in forum Excel Programming / VBA / Macros
    Replies: 55
    Last Post: 04-23-2018, 04:15 PM
  2. How would you organize this data?
    By C.j. in forum Excel General
    Replies: 3
    Last Post: 04-15-2014, 02:04 PM
  3. How To re-organize data
    By Daniel.S in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-08-2012, 07:24 PM
  4. how to organize the data ?
    By eran3185 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-21-2010, 04:49 AM
  5. how to organize data
    By sanlen in forum Excel General
    Replies: 3
    Last Post: 09-17-2010, 01:45 PM
  6. Organize data
    By rosef in forum Excel General
    Replies: 1
    Last Post: 11-24-2009, 08:22 PM
  7. How to organize this data?
    By adroadster in forum Excel General
    Replies: 3
    Last Post: 01-11-2009, 05:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1