SOLVED

Partially transpose a table

Occasional Contributor

Hi fellow Excellers,

 

I am dealing with a table that I want to partially transpose. See table I below as an example. I want to include the weeks in the rows (see table II). I can't figure it out, can someone help me?

 

Cheers!

 

BrandModelColourWeek 1-2023Week 2-2023Week 3-23
AppleS32022Black100013911708
Apple13 PROBlack134167195
Apple8White397413651
SamsungS20Black537856895290
SamsungS10Black809881348123
SamsungS10eBronze625466376587
SamsungS8White426481443
NokiaG21Black129176198
Nokia210Black753276127751

Table I

 

BrandModelColourDate
AppleS32022Black1000
AppleS32022Black1391
AppleS32022Black1708
Apple13 PROBlack134
Apple13 PROBlack167
Apple13 PROBlack195
Apple8White397
Apple8White413
Apple8White651
SamsungS20Black5378
SamsungS20Black5689
SamsungS20Black5290
SamsungS10Black8098
SamsungS10Black8134
SamsungS10Black8123
SamsungS10eBronze6254
SamsungS10eBronze6637
SamsungS10eBronze6587
SamsungS8White426
SamsungS8White481
SamsungS8White443
NokiaG21Black129
NokiaG21Black176
NokiaG21Black198
Nokia210Black7532
Nokia210Black7612
Nokia210Black7751

 

2 Replies
best response confirmed by PBrabander (Occasional Contributor)
Solution

@PBrabander 

Click in the source range.

On the Data tab of the ribbon, in the Get & Transform Data group, click From Table/Range.

Select all the week columns.

In the query editor, activate the Transform tab of the ribbon.

Select Unpivot Columns > Unpivot Only Selected Columns.

If you don't want the week in the final result, right-click the column with the weeks and select Remove from the context menu.

Switch back to the Home tab of the query editor, and click Close & Load.

 

Big thanks Hans!