Combining multiple data columns in to one pivottable field

gdyer13

New Member
Joined
Jun 20, 2017
Messages
1
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]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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,
Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top