Transposing Data Across Multiple Rows To a Single Column

[Brytsyt]

Hi All,

Currently working on a piece that should take the information given and output the data into a very specific format - Idea is to ensure all item names for an individual are on one row and the item values are shown below. Issue is within the file there are multiple item names, values and all for multiple people. I have included a basic test table below to better visualise what I am dealing with (have also added some test data in the format of an excel file):

Data Currently:

Name	Date	Items_1	Items_2	Items_3	Items_4
Person 1	11/01/2019	Item Name 1	Item Name 2
Person 1	11/01/2019	3000	40000
Person 2	08/02/2019	Item Name 1	Item Name 2	Item Name 3	Item Name 4
Person 2	08/02/2019	65000	1000	200	900

How I need to get the data in:

Name	Items	January	February	March etc (Months of year across here)
Person 1	Item Name 1	3000
Person 1	Item Name 2	40000
Person 2	Item Name 1		65000
Person 2	Item Name 2		1000
Person 2	Item Name 3		200
Person 2	Item Name 4		900

I have tried to create record ID's and then transpose / cross-tab from there however it always becomes an issue that in the original data set the item names are in across the rows in the data but not a part of the header.

Any help would be fantastic and I'm looking forward to any advice,

All the best,

B

[afv2688 · Accepted solution]

There you go!

Cheers

[Brytsyt]

This worked fantastically!

Thank you so much.

B