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
