Excel Pivot Multiple Columns

Africat

New Member
Joined
Aug 5, 2018
Messages
3
ItemListColour1Colour2Colour3Colour4Colour5Colour6Colour7Colour8
Item 1BlueYellowPurpleGreenOrangeRedCyanWhite
Item 2BlackMagentaCyanYellow

<tbody>
</tbody>

Hi I'm new here so sorry about the unconventional formatting. I have a item table with different coloured paints. I have created a unique list of colours and I am trying to create a relationship between the UniqueColourList and all 8 colours cloumns on the item table so that when expanded shows only the item numbers that has that colour. I've gotten it to "work" by creating a relationship with all 8 colours (some sorta crazy 1-to-many x 8).
Practical use would be to expand the pivot table at blue and see all the Items that require blue so that forecasting can be done on how much blue paint to order & is require for exisitng orders etc.

I was wondering if there is some better method to get a cleaner result without powerpivot and with powerpivot?
Thanks

UniqueColourList
Blue
Black
Yellow
....

<tbody>
</tbody>
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,301
Here are two possibilities....
Using Power Query (Get & Transform):
I named the data range: rngColors
- Select the range and use: Data.From_Table (The Power Query editor will open)
- Promote the first row to headers
- Select the first column and use Unpivot Other Columns
- Remove the first two columns (ItemList, Attribute)
- Remove duplicates

These are the M-language steps:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="rngColors"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ItemList", type text}, {"Colour1", type text}, {"Colour2", type text}, {"Colour3", type text}, {"Colour4", type text}, {"Colour5", type text}, {"Colour6", type text}, {"Colour7", type text}, {"Colour8", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ItemList"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"ItemList", "Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"
This is a manual approach:
- Use this Excel 2003 keyboard shortcut: ALT+DPP (to open a create Pivot Table window)
- Check: Multiple Consolidation Ranges.....Click: Next
- Check: I will create the page fields........Click: Next
- Select the data range....Click: Add....Click: Finish
- Remove the Column and Row fields
- Double-click the Value amount (to create a new sheet with the UN-pivoted data)
- Remove the first two columns
- Use Remove Duplicates (on the table ribbon tab)

Is that something you can work with?
 

Africat

New Member
Joined
Aug 5, 2018
Messages
3
Hi Ron,

Thanks for the response. I'm not sure if I have followed all the steps correctly.
I can get the unique list of colors but I am having difficulty creating a pivot table relating the two.

Ideally I am trying to make a pivot table below:
When Blue is expanded it shows only item 1 since it requires Blue
When Black is expanded it shows only item 2 since it requires Black
When Yellow is expanded it shows item 1 and item 2 since both require Yellow
I don't have trouble doing this if for example each item only has Colour1, but when trying to also relate all 8 colours i seem to be having difficulty.

UniqueColour Pivot
Blue
Item 1
Black
Item 2
Yellow
Item 1
Item 2
....

<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>
</tbody>

The only other way I can think of doing this is by rearranging the fields
Item Column
Item 1 - Blue
Item 1 - Yellow
Item
 

Africat

New Member
Joined
Aug 5, 2018
Messages
3
Continued...
The only other way I can think of doing this is by rearranging the fields
Item Column Colour
Item 1 Blue
Item 1 Yellow
Item 1 Purple
.....

But i would like to keep the item column as a key field with no duplicates
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,301
If you used Power Query...Getting the grouping is easy:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="rngColors"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ItemList", type text}, {"Colour1", type text}, {"Colour2", type text}, {"Colour3", type text}, {"Colour4", type text}, {"Colour5", type text}, {"Colour6", type text}, {"Colour7", type text}, {"Colour8", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ItemList"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Value"}, {{"Colors", each _, type table}}),
    #"Expanded Colors" = Table.ExpandTableColumn(#"Grouped Rows", "Colors", {"ItemList"}, {"ItemList"})
in
    #"Expanded Colors"
Then, if you want that data in a pivot table....
- Insert.Pivot_Table
- Use an External Data source
....Select the query you built
....Click: Open
....Continue building the pivot table as you normally would by putting the colors and items in ROWS

Does that help?
 
Last edited:

Forum statistics

Threads
1,081,752
Messages
5,361,085
Members
400,613
Latest member
Markdc123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top