Pivot Table Columns Labels

[Vanda_a]

Hello all

I am new to Pivot Table. Below issue, i have no idea how to deal with it

	A	B	C	D	E	F	G	H	I
1	Product	Vendor	Vendor	Vendor	Vendor	Amount	Amount	Amount	Amount
2	Apple	Sup	EEG			10$	5$
3	Apple	EEG	HOJO			40$	1$
4	Orange	Sup				6$
5	Mango	Goh	SaSa	Sup	EEG	2$	2$	2$	5$

<tbody>
</tbody>

Above table is my report to track Vendors for products

My issue is how to combine vendors into one row at Column Labels?

Result:

	A	B	C	D	E	F
1	Row Labels	Sup	EEG	Goh	SaSa	HOJO
2	Apple
3	Mango
4	Orange

<tbody>
</tbody>

Thank you very much

 

[RudiS]

What about the values? DO you want the values to go into the table too?
If it is just about the Vendors, placing them in a row across the top, you can simple copy the 4 Vendor columns below each other, then run an the Remove Duplicates tool to remove duplicates, and lastly copy the column and paste special: Transpose.

 

[Vanda_a]

the value I also want them to show up correctly

 

[RudiS]

Hi,

See this image

1. This is your original data
2. Create a formula to concatenate the pieces of info
3. See formula in formula bar
4. Transpose the data and convert to values
5. Use Text-To-Columns to split the data and add column headings
6. Use a Pivot Table to compile the report as in image.

 

[Vanda_a]

ADVERTISEMENT

There is no easier way right? My raw data is so many rows

Thank you very much

 

[RudiS]

Your source data is very unconventional, so I cannot think of any other way to get to your desired output.
If you can send me a sample workbook with some desensitized data that simulates the structure of your actual table so I can see the table dimensions, I can assist in producing a macro to automate this process a bit more.

 

[Vanda_a]

ADVERTISEMENT

Here it is https://www.dropbox.com/s/k3xprqjprc1crsh/June.xlsx?dl=0

Lot List and Product are the same ID

 

[SpillerBD]

There is no easier way right? My raw data is so many rows

Thank you very much

If you have access to PowerQuery there can be.
You have data organized in a "pivot Table" like format that you want in a new Pivot Table.
Its not uncommon to see this. But frustrating once it is realized the power Pivot Tables bring to properly formatted transactional or list data...

 

[Vanda_a]

i have tried PowerPivit but failed on NET Frame. Although I have alrdy installed it, excel still requires it. No idea.
this PowerQuery is a kind of add in too. I might get it sucked as powerpivot

 

[RudiS]

If you have access to PowerQuery there can be.
You have data organized in a "pivot Table" like format that you want in a new Pivot Table.
Its not uncommon to see this. But frustrating once it is realized the power Pivot Tables bring to properly formatted transactional or list data...

Spiller, I have "played" around with this sample data in Power Query. starting off with the obvious action of unpivoting the Vendor and Amount columns, but the problem is that the values get combined and one ends up with inaccurate combinations of amounts being grouped together. It seems as if one would have to end up combuining the data even in PQ to end up with the correct grouping of vendors and amounts. So its possible, but there are only 6 steps in Excel, and writing a macro to do this wouldn't be too much effort... (I hope )