Data Transformation Help

[twwareza]

Hi,

I have a collection of data that is ordered like this (see below). We have a lot of our older sheets setup this way and would like to develop a VBA script to help us make this light work. 

Most of the tables are like this:

Member	Member ID	Buyer ID	Jan	Feb	Dec	2019	Jan	Feb	Dec	2020
Reza	4	3	2	3	2	7	4	3	2	9
Reza	4	2	1	1	1	3	1	1	1	3
Shiya	3	3	2	3	3	8	5	6	3	14
Hassan	10	3	2	3	4	9	2	7	4	13

... and we would like the above data into this format:

Name

Member ID

Date

Year

Month

Volume

Buyer ID

Question is ... would you attempt this with formulas (index, match, sumifs) or is this a job for some VBA macros. Just need some advice which way to go to look up solutions or if there are ready made solutions out there we could adapt, great!

Thanks

Update: Looks like Power Query does help in exactly this situation: https://youtu.be/JMrfbv2h7p8

[#2]

@twwareza 

Looks like job for Power Query if you consider such option. 

As for the task itself it's not clear where are in first table Location, Product, Buyer Name. Better if you provide small sample file with first table as in your post and second one as it shall be, using data in first table.

[#3]

Thanks.
Sorry they were mockups. I appended a sample file with the main headings now matching.

I was not sure how to tackle the Jan, Feb columns, as these now need to be transformed into rows with the same member/etc.

PQ seems like the way to go though.

Thanks

[#4 · Accepted solution]

@twwareza 

Thank you. It's still not clear from here Date shall be taken, preliminary that's like

- name source data range, e.g. as Source and query it (as variant work with tables)

- use first rows as headers and unpivot other than first 3 columns

- duplicate column with month/years, convert to number and replace errors on nulls. Duplicate again

- fill years, filter not null in last column, extract first 3 characters from months

- cosmetic and return back to Excel table.