How to convert a single column to multiple-CodePudding

I have the following data in this format:

I wish to transform the data to look like the following format:

I have tried the following:

Column F uses WEEKNUM to find the week number of the year, but the format is down one single column when I want it to be applied across the way as seen in the second screenshot. Using IF didn't work as it would refer to the same cell, nor VLOOKUPs. I also tried a PivotTable, but then the ID sort changes from (WG1, WG2, WG3 etc) to (WG10, WG11..., WG19, WG20 etc) which I do not want. It also combines the years (2022/2023).

Anyone any idea how I can do this without manually entering the data? A formula/VBA/excel action to quickly make the original screenshot look like the following? (The column headers - 48, 49, 50 etc are already in place) Bit stumped. I am capable with VBA as well hence the tag.

CodePudding user response：

I have started entering the values 48, 49, ... manually, but you might decide to use the UNIQUE() function for that purpose.
Once you have this, you can easily use the following simple formula:

=IF($B2=D$1,$C2,"")

In order to understand, hereby the corresponding screenshot:

Have fun :-)

CodePudding user response：

If you have Excel 365 current channel then you can use:

=LET(weeknum,TRANSPOSE(UNIQUE(Tabelle1[week num])),
values,IF(Tabelle1[week num]=weeknum,Tabelle1[qty due],""),
VSTACK(weeknum,values))

if you don't have current channel (but Excel 365) you can use two formulas:

E1: TRANSPOSE(UNIQUE(Tabelle1[week num]))
E2: IF(Tabelle1[week num]=E$1,Tabelle1[qty due],"")