Sorting through large amounts of data (pivot table, sql)

Afternoon all,

Ive been rattling my brain on this problem all morning and Im afraid the answers are coming along rather slowly.

Basically I have been given an Excel workbook with two sheets within it. The first sheet represents pipework, there is around 8.5k rows and 10 columns.

The other sheet represents parts of the pipework, such as the bends, elbows, tees etc. This sheet has over 37k lines.

Now what ive been asked to do is add another worksheet which shows the lines, but also the number of components on the lines.

So an example on the new sheet would be line number 1 would have 5 bends and 2 elbows. Rather than have line number one on Sheet1 and then the components spread over 7 lines on Sheet2. So it basically skips out all the details about each component and just counts how many there are for each line and adds that to a new column on the new sheet. I hope that made sense!.

Unfortuantly to distinguish each unique line, you need to compare data from three columns. So the only way I could do this with a lookup of sorts would require me to use arrays, or at best, the sumproduct. As you can imagine this simply isnt possible and Excel quickly runs out of memory and waves the white flag.

My next idea was to attempt to use a pivot table. Unfortuantly though to represent the data with all the right columns, I need it to look at both sources.

If this is actually possible, then someone please let me know, as Iv not come across a way of doing it.

So my next step was try and get Excel to sort it for me using VBA and SQL. So far I am able to collect the main data from sheet1 and add that to the new sheet. I am also able to count how many components are associated with each line and add that to the new sheet as a seperate column. If it is important, this was done using an inner join. This at the moment is not accurate as it only matches up one of the criteria of the three it needs to be doing.

My next step now is trying to break that count down, so it shows the different types of components, rather than bundle them all together on one column. This I seem to be having difficulty with. So im basically after it creating a few columns that will do something along the lines of
'Count(Type) = 'Bend' etc.

My plan once I can seperate this data into seperate columns is then to try add the rest of the criteria.

So, if anyone is looking at this and thinking 'god damn dude, why the heck dont you just do a #insert super solution here' then please let me know!.

Else if someone could help me along with some SQL statements that grab data from two sources, does muliple counts on the same field but with different criteria, then please reply .

wow and I went for a shortened version.

Why don't you post attach a small sample workbook including desired results. All that text is a bit daunting!

Originally Posted by StephenR

Why don't you post attach a small sample workbook including desired results. All that text is a bit daunting!

Ok point taken. Right Ive attached a small chunk of the real data. Youll have to use your imagination abit as the ISO's shown dont actually match up. Sheet1 represents the sheet with the main lines. Sheet two represents the components. The column Type shows which type of component it is. Sheet3 shows an example of how I want the end result to look (for now). Basically a copy of sheet1, expect is also has the components on the end. Shown by the columns, bends, elbows etc.

So for bends, what will need to be done is match up the ISOMETRIC column on both sheets. Match the P1BOR on sheet2 to the HBOR on sheet1. Then count the number of bends on sheet2 that match that criteria. Then place that number on the bends column on sheet3.

That any better?

Not much I'm afraid. Can't you attach a sample which relates to your results? Also, you need to explain the rationale for what matches with what

So for bends, what will need to be done is match up the ISOMETRIC column on both sheets. Match the P1BOR on sheet2 to the HBOR on sheet1. Then count the number of bends on sheet2 that match that criteria. Then place that number on the bends column on sheet3.

I don't follow this.

I'll leave this to someone else I think.

Oki smokey, sorry for being a pain, its my bad for giving you guys limited data, however as you guys can imagine sometimes its just not possible to give it all out.

Righty lets tackle this another way, as I think Im almost there. Im just hoping I can do this neatly, without having to butcher the sql statement up in vba.

Righty, here is the magical SQL statement that currently grabs my data:

Please Login or Register  to view this content.

Ive tried to seperate that as much as possible for easy viewing. Now currently that count highlighted in bold will end up in a column showing be the number of components of any kind associated with that line. So if theres 2 bends and 1 elbow, itll show the number 3. What Id like to do (if possible) is actually have it just count the bends and stick that in a column, then in the next stick the elbows, after that the caps etc etc.

Failing this Im just going to have to butcher the recordset for my data in VBA, the only prob is Im worried about memory if I try to make it too complicated.

'EDIT the count decided it didnt want to be in bold, so its on the top line far right.