Organize and Sum Data

Hello,

I receive up to 1500 pdf documents that look like the attached document. Normally I go through by hand and add up certain information under the Fabrication Materials list. I had an idea to crop the Fab Materials list only and export it to excel.

The information that adobe spit out into excel is the data on Tab Table 1. Column A means nothing, Column B is footage or quantity, Column C is pipe size (if it says 6"x1", the pipe size is 6").

I'm looking for a way to automatically sum the information in excel on tabs Line # and Summary.

The line number in the spreadsheet example is 6"-CW25406-E-SS01-1. Adobe spits this out at the bottom of the Fab Mat list in random columns A-D. The headers such as PIPE, FITTINGS, VALVES also get spit out in random columns A-D.

The last number in the line number is the insulation thickness, so the above line number, every pipe size has 1" insulation.

Please find the attached spreadsheet and let me know what questions you have or if this is even possible. Thanks for the help!!

The Fabrication Materials for the Line Number are listed above the line number. So on Table 1, the materials for line number (row 11) 6"-CW2406-E-SS01-1 are rows 1-10. This also equals 1 page of the pdf drawings. In this example, there are 2 pdf drawings that have the same Line Number which is why it is listed twice. (rows 12-19)

On the Line # tab, I would like it to automatically list every single line number on Table 1, and then list the Fabrication Materials above each line number.

Column A on Table 1 tab means nothing.

So for Pipe Size, the info on Table 1 is column C. The Pipe Sizes listed are 1, 2, 3, and 6.

I do not care about materials under the header OLETS or FASTENERS.

For the Insulation Thickness column, it equals the last number in the line number which in this example is 1.

The linear feet of pipe is listed on Table 1, Column B, under the header PIPE.

The 90's are listed in column B, under header FITTINGS, with the description (column D) containing the text "Elbow 90". This is the same for the 45's.

The Fittings are listed in column B, under header FITTINGS, excluding the description (column D) that contains the text "Elbow 90" or "Elbow 45".

The Valves are listed in column B, under header VALVES.

The Flanges are listed in column B, under header FLANGES. (there are no flanges in this example).

On the SUMMARY TAB, I would simply like a summary based on Pipe and Insulation size ONLY. So for example, every 6" pipe size with 1" insulation, I want it to automatically summarize on the Summary Tab.

The 90's and 45's are under the FITTINGS header, but I don't want them counted as fittings. I need those separate. So under the header FITTINGS, there would be quantities for 90s, 45s, and then the total number of fittings minus the 90's and 45's.

It looks like you are headed in the right direction. For the pipe footage, its listed as 19'-9", can you have it automatically convert to feet (19.75') on the Line # and Summary tabs?

If there is no length for a pipe size that has fittings, valves or flanges, please have it default to 3'. Thanks!

Wow, that's really impressive. Thank you.

I added FLANGES (highlighted in yellow) to Table 1 tab so you can incorporate that as well.

Yes, that is correct. The existence of a fitting, valve, or flange implies existence of a pipe. ONLY if there is no pipe length for that particular pipe size, for every Quantity of 1, there should be 3' of pipe length.

On a side note, do you know anything about how adobe pro exports files into excel? Can this be controlled? Some of the files export perfectly and some export goofy. Maybe try an adobe forum for this one?

Yes, it is possible.

Would you be able to add an Adjusted Length column that adds in the footages for the charts on the spreadsheet? So it would be pipe length + length of 90, 45, valve, flange?

Oh and I'm not sure if it matters but there will be a description for every item in column D under the Main Headers.

Thank you! I think you forgot to attach the updated spreadsheet?

I inserted additional information into Table 1 and clicked on the Make Report button and I got a "Run time error 9 - Subscript out of range".

So the Line # tab only shows 1 line number at a time? And the Snapshot tab shows all the line numbers on Table 1?

When you look for the 45 or 90 in column D, you currently have it as the second "word". Is it possible to have it just look for the 45 or 90 in the cell? I am not positive if the 45 or 90 will always be the second word in the description.

Table 1 can be called ISO's, Data can be left or changed to Calculations, Pivot can be Pipe Size

Here it is.

I'm glad you're up for the challenge! Thank you for all the help.

Oh, and the same line number can happen more than 3 times.

That is really cool. Nice job!! A few things I noticed:

1. If there is a fitting, valve, flange for a pipe size that has no length under the PIPE header, can you have it default to 3' per fitting, valve, flange?

2. Can you add in the charts from the excel sheet with an adjusted pipe footage column?

3. Can you add another pivot tab that is summarized on Pipe and Insulation sizes only. Line numbers don't matter.

Thank you very much!

Actually don't worry about the default length. Once the charts are in there it will have the length of the valves, flanges, and fittings.

If you look at the first page of this thread, message dated 12-21-17 @ 10:51 am. It's attached here again. I would like an Adjusted Pipe Footage that = Pipe Length + (Valve Length * Valve Quantity) + (Fitting Length * Fitting Quantity) + (Flange Lenth * Flange Quantity)

In my previous post, I attached an excel file labeled CHART. That file has the pipe size and corresponding lengths in feet for the fittings, valves and flanges.

I'm sorry, I should have explained this better. The second pivot table, I would like it to look like the first pivot table with one exception. Instead of listing it based of line number, I would like it based off of the pipe size and insulation size. So I would like it to add up the Total feet, 90, fittings, valves, flanges, and 45 for every (for example) 6" pipe size by 1 inch insulation size that is listed on Table 1. The line number would not matter for this tab.

Is that a little more clear?

Use this excel file for the CHART. I added some pipe sizes and made one chart for both 90's and 45's. Thank you!!

Sorry. Here it is.

Did that make sense or do you need more information?

No problem. Thanks for the help!

Sorry, but the adjusted footage column is still wrong. Are you not able to see my previous posts with the excel attachment "chart"?? I would like this chart inserted into the spread sheet. See below formula.

Adjusted Footage = Pipe (Feet) + (Valves (each) * Valve length (from chart)) + (90s + 45s (each) * 90/45 length (from chart)) + (Flanges (each) * Flange length (from chart))

See the attached spreadsheet labeled summary. I would like the Pivot 2 tab to look like this please. The Line Number does not matter on this tab. I simply would like all of the information on Table 1 summarized by Pipe Size and Insulation Thickness.

I hope this makes more sense. Thank you!

Does that help explain what im looking for?

Do you need any additional information?

Please find the attached Spreadsheet. I added a lot of information to it and it is not counting the 90's correctly. See line number 1"-DW2564-Q-SS01-1 as an example. On the table tab it shows 5 90's but only 1 90 shows up on the Pivot tab. Please help! It's so close to being perfect. Thank you for everything.

Thank you!! No problem, just see my last posted message and excel attachment. The 90's are not counting correctly.

Any luck with getting the correct count on the 90's?

One more thing that I have noticed...

Example: 2"-CW2554-Q-SS01-1
One of the pipe sizes is listed as 3x2. This is not a "pipe size". It is classified as a fitting. So instead of having its own line item, can you have it count as a 3" fitting? So under the 3" pipe size, the fitting count would change from 3 to 4 and the row 3x2 would not exist. In summary, anything with a pipe size of "#x#", count this as a fitting under the pipe size of the larger number.

Thank you very much for all of your work. This is super cool and extremely helpful.

Any luck so far? Did my previous post make sense?

Any updates or questions?

No problem. I know how that goes. I have noticed an issue with line 1"-KW2565-Q-SS01-1. It is not reading correctly.

I think are numerous items not reading correctly as the spreadsheet has almost double the amount of 90s and misc. that I had when I did everything by hand.

Just curious if there was any progress made on the issue... thanks again for all the help!

Here is the most recent version that we were last working with.

Issues:

#1 - Example: 2"-CW2554-Q-SS01-1
One of the pipe sizes is listed as 3x2. This is not a "pipe size". It is classified as a fitting. So instead of having its own line item, can you have it count as a 3" fitting? So under the 3" pipe size, the fitting count would change from 3 to 4 and the row
3x2 would not exist. In summary, anything with a pipe size of "#x#", count this as a fitting under the pipe size of the larger number.

#2 - I have noticed an issue with line 1"-KW2565-Q-SS01-1. It is not reading correctly. I think are numerous items not reading correctly as the spreadsheet has almost double the amount of 90s and misc. that I had when I did everything by hand.

Any luck with this?

Any luck with this?