How to rearrange values from several cells to a single row in Excel

Question

How do I change my data from this form

ID	Value1	Value2	Value3
A	A1a	A1b	A1c
A	A2a	A2b	A2c
B	B1a	B1b	B1c
B	B2a	B2b	B2c
B	B3a	B3b	B3c

into this

ID	1	2	3	4	5	6	7	8	9
A	A1a	A1b	A1c	A2a	A2b	A2c
B	B1a	B1b	B1c	B2a	B2b	B2c	B3a	B3b	B3c

I expect there to be about 10 000 unique IDs, with maybe up to 100 unique data points for each ID. Also, this example has three columns for those values, but what if I had more? What would a more generic solution look like?

Here I asked a similar question earlier with a slightly simpler table. Of the answers I got I liked best the Power Query -method. Would a variation of that work here? Another approach is also possible.

Answer 1

Transform Data: 'Pivot a Table Horizontally' (Excel Formula)

=LET(table,A1:D6,
    data,DROP(table,1),
    ud,TAKE(data,,1),
    vd,DROP(data,,1),
    u,UNIQUE(ud),
    vc,MAX(COLUMNS(vd)*COUNTIFS(ud,u)),
    h,HSTACK(TAKE(table,1,1),SEQUENCE(,vc)),
    v,DROP(REDUCE("",u,LAMBDA(rr,r,
        VSTACK(rr,EXPAND(TOROW(FILTER(vd,ud=r)),,vc,"")))),1),
    d,HSTACK(u,v),
    result,VSTACK(h,d),
    result)

Sure, you will argue that's an ugly formula but you can rewrite it in the following way...

=LAMBDA(table,LET(
    data,DROP(table,1),
    ud,TAKE(data,,1),
    vd,DROP(data,,1),
    u,UNIQUE(ud),
    vc,MAX(COLUMNS(vd)*COUNTIFS(ud,u)),
    h,HSTACK(TAKE(table,1,1),SEQUENCE(,vc)),
    v,DROP(REDUCE("",u,LAMBDA(rr,r,
        VSTACK(rr,EXPAND(TOROW(FILTER(vd,ud=r)),,vc,"")))),1),
    d,HSTACK(u,v),
    result,VSTACK(h,d),
    result))

... and copy it into the name manager...

... to create your own Lambda function, e.g. HPivot,...

... that you can use anywhere in your workbook. In this particular case, use...

=HPivot(A1:D6)

Answer 2

This can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

---

To use Power Query follow the steps:

• First convert the source ranges into a table and name it accordingly, for this example I have named it as Table_1

---

• Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

---

• The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table_1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"ID"}, {{"All", each _, type table [ID=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ID", "Value", "Index"}, {"ID", "Value", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US")[Index]), "Index", "Value")
in
    #"Pivoted Column"

---

---

• Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

---

Using Excel Formulas with the help of dynamic array LAMBDA() helper functions, one could use the following:

---

=LET(
     _data, A2:D6,
     _id, TAKE(_data,,1),
     _vals, DROP(_data,,1),
     _uniqId, UNIQUE(_id),
     _output, IFERROR(DROP(REDUCE("",_uniqId, LAMBDA(x,y,VSTACK(x,HSTACK(y,TOROW(FILTER(_vals,y=_id)))))),1),""),
     VSTACK(HSTACK("ID",SEQUENCE(,COLUMNS(_output)-1)),_output))

---

One more way of using Dynamic Array Formulas with the help of LAMBDA() helper function called MAKEARRAY() will be more efficient and faster.

---

=LET(
     _data, A2:D6,
     _id, TAKE(_data,,1),
     _vals, DROP(_data,,1),
     _uniqId, UNIQUE(_id),
     _rows, ROWS(_uniqId),
     _columns, MAX(COUNTIFS(_id,_uniqId)*COLUMNS(_vals)),
     _body, MAKEARRAY(_rows,_columns,LAMBDA(r,c,IFERROR(INDEX(TOROW(FILTER(_vals,_id=INDEX(_uniqId,r))),,c),""))),
     _headers, HSTACK(A1,SEQUENCE(,_columns)),
     VSTACK(_headers,HSTACK(_uniqId,_body)))

---