Table transform in Excel

Question

Hello,

I have raw data that are extracted in complex design, so I need to transfer these data from below:

to below design:

There are thousands of Customers IDs and thousands of Parts and Parts type, each part has 2 subtypes, so subtypes need to be concatenated in single cell.

Can you help in this request?

Thank you

Answer 1

Hi @Ahmed Moursi

If you can use formula and Excel Power Query, I suggest you try following steps.

First make the Row and Column headers.

• For the column of Customer ID, you may copy ID and paste to other cells.
• For the row of Parts, you may use UNIQUE function, such as =UNIQUE(B2:M2,TRUE).

Please combine the values of the same Parts based on the column number, enter the formula =INDEX($B3:$M3,0,COLUMN()*2-3)&INDEX($B3:$M3,0,COLUMN()*2-2) then pull down and pull right.

And then import data into Excel Power Query and do unpivot columns operation.

• Select the range from A9:G13, click Data tab > From Table/ Range.

• Select Customer ID column > Click Transform tab > Click down arrow beside the Unpivot Column > Choose Unpivot Other Columns.

• Click File > Close & Load to, load the table to one Excel sheet.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

A macro should be able to do it easy enough with a pair of nested loops..

Loop through the rows

Extract the customer ID

Loop through the columns by 2

  Extract Part #

  Concatenate the contents of the cells

  Place ID, Part #, and concatenated data in next row of output

  Repeat

Repeat

Answer 3

Excel 365 Pro Plus with Power Pivot and Power Query.
With PivotTable and Slicer.
No formulas, no VBA macro.
https://www.mediafire.com/file_premium/rrgr64qlq2tl6to/06_02_23a.xlsx/file
https://www.mediafire.com/file_premium/qzxgi1nu7objmr9/06_02_23a.pdf/file