Consolidate Multiple Columns into 1 in Pivot Table

If you are willing to use Power Query, then here is a solution for you.

Please Login or Register  to view this content.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H
1	Part Number	Department	Class Group	Subclass	Qty	Date	Month	Year
2	2521855	Furniture	Hard Furniture	Bedside Table	500	9/5/2021	Sep	FY22
3	2521855	Furniture	Hard Furniture	Bedside Table	250	10/5/2021	Oct	FY22
4	5821851	Furniture	Hard Furniture	Desk	200	9/20/2021	Sep	FY22
5	5821851	Furniture	Hard Furniture	Desk	150	10/30/2021	Oct	FY22
6	5821851	Furniture	Hard Furniture	Desk	150	11/30/2021	Nov	FY22
7	5824553	Furniture	Hard Furniture	Bedside Table	1000	9/5/2021	Sep	FY22
8	5824553	Furniture	Hard Furniture	Bedside Table	200	10/30/2021	Oct	FY22
9	2246384	Furniture	Hard Furniture	Desk	150	9/5/2021	Sep	FY22
10	2246384	Furniture	Hard Furniture	Desk	300	11/30/2021	Nov	FY22
11	5843314	Furniture	Hard Furniture	Desk	250	9/5/2021	Sep	FY22

Sheet: Table1

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.