Pivot Tables with Multiple Columns with Same Data

[mattshock]

OK, so I understand my title might be confusing, and that without being able to input tables in this textbox it might be even more confusing to try and share what I'm trying to ask, but I'll do my best.

If anyone thinks they could help, but aren't completely understanding my question, PM me or reply and I'll e-mail you a better explanation with table. Also, I tried to host images of the tables and link to them, but my work filter is blocking all image hosting sites.

So here's the issue:

I've exhausted my wits trying to force Excel to do my bidding. Our on-site IT guy is more useless than a Caps Lock key and online forums have thus far yielded nothing.

My issue is with a PivotTable.

I've attached what I've got, and I'll explain what I need.

OK, so let's say we have data like this: (Three columns, three headings), I put *** between columns to make it easier to see (hopefully)

Date Repaired***Truck Number***Part Repaired
1/5/2008***1***Brakes
2/6/2008***2***Headlights
2/15/2008***2***Transmission
3/28/2008***1***Paint
5/1/2008***3***Brakes
6/9/2008***1***Brakes

It's very simple to make the following PivotTable I want.

Page: Date
Row Fields: Truck Number
Column Fields: Part Repaired

and everything works out great.

So far, so good, as long as my Part Repaired is all in one column. However, there is more than one part on a truck, so what if I wanted this:

Date Repaired***Truck Number***Part Repaired***Part Repaired***Part Repaired
1/5/2008***1***BrakesPaint
2/6/2008***2***Headlights***Transmission
2/15/2008***2***Transmission
3/28/2008***1***Paint
5/1/2008***3***Brakes***Transmission***Paint
6/9/2008***1***Brakes

Now here we encounter my issue. I can't get Excel to recognize that the three columns should all represent the same input information. I end up getting ugly tables, none of which properly sort the information.

If I merge the top three columns, I get an error when trying to make the Pivot table.

If I give them the same heading title, Excel renames the Part Repaired, Part Repaired2, Part Repaired3, and doesn't recognize them as similar data.

The only potential solution I've been able to come up with is this:

Take the multiple columns and manually re-enter the data so that it's all one column (multiple entries for repair work with more than one part done), and then use the original PivotTable.

The issue with this is that it makes the work for our technicians so much more demanding. If it can't be done, I have no problem saying "screw them!", but my conscience would rest easier knowing I tried.
Can you merge/group/combine the data from two columns under one heading in PivotTables? Some strange "list" related function I don't know about?

I've spent a solid 2 days trying to figure it out, and I've come up with nothing.
Let me know guys, this is NOT my field of expertise (I'm an engineer!),

Help!

- Matt

PS, I don't actually work with trucks. The materials I'm concerned with documenting the work on sometimes have up to 15 parts being repaired at a time. It's critical that we can monitor which parts fail, so that we can interpret the effects of our different engineering trials that we're tinkering with. Also, it's not just date and Part #, there are more fields that are entered before the Part Repaired that need to be input. It's just a mess to deal with if it can't recognize that data in multiple columns.

 

[Fazza]

Hi, Matt.

We see that a pivot table from just 3 columns is easy. The problem is the extra columns.

So, one way is like you wrote, re-arrange the data from multiple columns back to three columns. This can be done manually pretty quickly. Take data from columns A:B and copy it multiple times below where it is now. Then take each of the data from the parts columns one block at a time. So, leave column C where it is, then parts2 down against the first copy of A:B, then parts3 against the next, etc. Then you would have one block of three columns: easy to work with. Filter out the blanks in column C and proceed as normal with a PT. This could easily be automated with VBA.

Also, there is a way to get the result directly from the original source data. Using some SQL to do the same work to rearrange the data for you. It can be done entirely without VBA but for flexibilty is best via VBA. It involves using the external data functionality of Excel, which can work within the one file despite the name. If you search old threads I've posted many times on using SQL and pivot tables. An approach would be to set this up by first closing your data file and using a new file. When you are done, move the resulting PT into your original file - this avoids memory leak problems when creating from an open workbook. You can either take the external data source at the first step of the pivot table wizard or start from menu path data, import external data, new database query. There are a few steps on the way to getting into MS Query and then the SQL needs to be edited to like below. Then exit MS Query and make the pivot table. I've been brief in the description, please search for examples where this is more fully explained and let me know if you want some links. With google, suggest you search for "examples of Excel external data queries". This will result in the desired pivot table directly from the source data.

HTH, Fazza

SELECT `Date Repaired`, `Truck Number`, `Part Repaired`
FROM source
UNION ALL
SELECT `Date Repaired`, Truck Number`, `Part Repaired2` AS `Part Repaired`
FROM source
WHERE `Part Repaired2` Is Not Null
UNION ALL
SELECT `Date Repaired`, Truck Number`, `Part Repaired3` AS `Part Repaired`
FROM source
WHERE `Part Repaired3` Is Not Null
UNION ALL
SELECT `Date Repaired`, Truck Number`, `Part Repaired4` AS `Part Repaired`
FROM source
WHERE `Part Repaired4` Is Not Null
etc

 

[mattshock]

Hi, Matt.

We see that a pivot table from just 3 columns is easy. The problem is the extra columns.

So, one way is like you wrote, re-arrange the data from multiple columns back to three columns. This can be done manually pretty quickly. Take data from columns A:B and copy it multiple times below where it is now. Then take each of the data from the parts columns one block at a time. So, leave column C where it is, then parts2 down against the first copy of A:B, then parts3 against the next, etc. Then you would have one block of three columns: easy to work with. Filter out the blanks in column C and proceed as normal with a PT. This could easily be automated with VBA.

Also, there is a way to get the result directly from the original source data. Using some SQL to do the same work to rearrange the data for you. It can be done entirely without VBA but for flexibilty is best via VBA. It involves using the external data functionality of Excel, which can work within the one file despite the name. If you search old threads I've posted many times on using SQL and pivot tables. An approach would be to set this up by first closing your data file and using a new file. When you are done, move the resulting PT into your original file - this avoids memory leak problems when creating from an open workbook. You can either take the external data source at the first step of the pivot table wizard or start from menu path data, import external data, new database query. There are a few steps on the way to getting into MS Query and then the SQL needs to be edited to like below. Then exit MS Query and make the pivot table. I've been brief in the description, please search for examples where this is more fully explained and let me know if you want some links. With google, suggest you search for "examples of Excel external data queries". This will result in the desired pivot table directly from the source data.

HTH, Fazza

SELECT `Date Repaired`, `Truck Number`, `Part Repaired`
FROM source
UNION ALL
SELECT `Date Repaired`, Truck Number`, `Part Repaired2` AS `Part Repaired`
FROM source
WHERE `Part Repaired2` Is Not Null
UNION ALL
SELECT `Date Repaired`, Truck Number`, `Part Repaired3` AS `Part Repaired`
FROM source
WHERE `Part Repaired3` Is Not Null
UNION ALL
SELECT `Date Repaired`, Truck Number`, `Part Repaired4` AS `Part Repaired`
FROM source
WHERE `Part Repaired4` Is Not Null
etc

Thanks, I'll try this out.