# Table transform in Excel

20 Reputation points
2023-05-25T14:58:38.27+00:00

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

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,689 questions

1. 27,596 Reputation points
2023-05-26T06:10:15.2633333+00:00

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.

1. 2,511 Reputation points
2023-05-25T18:17:43.4166667+00:00

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

2. 1,191 Reputation points
2023-06-02T23:11:24.6266667+00:00

Excel 365 Pro Plus with Power Pivot and Power Query.
With PivotTable and Slicer.
No formulas, no VBA macro.