Combining multiple data columns in to one pivottable field

[gdyer13]

Hello,

I have a report that generates based on InfoPath forms filled out and downloaded from SharePoint. In this report, there are 3 columns each for part numbers and part descriptions (Part 1 #, Part 1 Description, Part 2 #, Part 2 Description, Part 3 #, Part 3 Description). In the report for the necessary department, these parts are listed in the pivot table under the merchandise they go with. I'm trying to figure out a way to combine the information from these columns to be able to list all of the requested parts based on a particular merchandise. If it's something that can be done in the tools for the pivot table, great, if it takes a formula on the data tab, that works too. I've tried searching all over and cannot find a solution that helps with this.

I'm trying to go from data pulls presenting like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Set Name
[/TD]
[TD]Part # 1
[/TD]
[TD]Part 1 Desc
[/TD]
[TD]Part # 2
[/TD]
[TD]Part 2 Desc
[/TD]
[TD]Part # 3
[/TD]
[TD]Part Desc 3
[/TD]
[/TR]
[TR]
[TD]Set 1
[/TD]
[TD]1234567
[/TD]
[TD]Widget
[/TD]
[TD]1234568
[/TD]
[TD]Doodad
[/TD]
[TD]7654321
[/TD]
[TD]Thingamajig
[/TD]
[/TR]
[TR]
[TD]Set 1
[/TD]
[TD]1234568
[/TD]
[TD]Doodad
[/TD]
[TD]1234567
[/TD]
[TD]Widget
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

To a pivot table reflecting like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Set Name
[/TD]
[TD]Part Number
[/TD]
[TD]Part Description
[/TD]
[TD]Total Needed
[/TD]
[/TR]
[TR]
[TD]Set 1
[/TD]
[TD]1234567
[/TD]
[TD]Widget
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1234568
[/TD]
[TD]Doodad
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7654321
[/TD]
[TD]Thingamajig
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fazza]

hi,

Pivot tables have always been able to do this. There are some old posts (many ~10 years ago) where I've explained it, btw.

You need to re-arrange the data. Which can be done using SQL such as, untested,

SELECT [Set Name], [Part # 1] AS [Part #], [Part 1 Desc] AS [Part Desc]
FROM [your sheet name$]
UNION ALL
SELECT [Set Name], [Part # 2], [Part 2 Desc]
FROM [your sheet name$]
WHERE [Part # 2] Is Not Null
UNION ALL
SELECT [Set Name], [Part # 3], [Part 3 Desc]
FROM [your sheet name$]
WHERE [Part # 3] Is Not Null

If you've given the data source a non-dynamic defined name you can use that instead of [your sheet name$]

Maybe easier to explain like this: CTRL-S to save the file. CTRL-N to open a new file. ALT-D-D-N to start a new query. Excel files & follow the wizard to select the data file, OK, if you haven't used a named range & get a message about no visible tables then OK to acknowledge & then via 'options' select 'system tables' & then you should see the worksheet names. select the named range or worksheet name and then a field or more. at the last step of the wizard choose the option to edit in MS Query. Then via the SQL button replace what you see by the above SQL, OK to enter, OK to acknowledge something about not being visible, then see the results set, via the 'open door' button exit MS Query and then be sure to select the pivot table option. Complete the pivot table. if you want you can move the resultant worksheet containing the pivot table into the file with the source data.

cheers