Table transform in Excel

Ahmed Moursi 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:

User's image

to below design:

User's image

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
0 comments No comments
{count} votes

Accepted answer
  1. Emily Hua-MSFT 27,596 Reputation points
    2023-05-26T06:10:15.2633333+00:00

    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.

    Capture17

    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.

    Capture18

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

    Capture19

    • 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.


    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Barry Schwarz 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. Herbert Seidenberg 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.
    https://www.mediafire.com/file_premium/rrgr64qlq2tl6to/06_02_23a.xlsx/file
    https://www.mediafire.com/file_premium/qzxgi1nu7objmr9/06_02_23a.pdf/file

    0 comments No comments