cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
SOLVED

Maybe an array formula to transpose records?

Jpey65
Copper Contributor

‎Mar 20 2024 11:27 AM - edited ‎Mar 20 2024 11:34 AM

‎Mar 20 2024 11:27 AM - edited ‎Mar 20 2024 11:34 AM

Maybe an array formula to transpose records?

I have a table of data. Note that the number of rows per index can vary and the total number of unique index values will vary and not be named TestX:

IndexConcat
Test1DataValue1
Test1DataValue2
Test1DataValue3
Test1DataValue4
Test1DataValue5
Test1DataValue6
Test1DataValue7
Test1DataValue8
Test1DataValue9
Test2DataValue10
Test2DataValue11
Test2DataValue12
Test2DataValue13
Test2DataValue14
Test2DataValue15
Test2DataValue16
Test2DataValue17
Test2DataValue18
Test2DataValue19
Test3DataValue20
Test3DataValue21
Test3DataValue22
Test3DataValue23
Test3DataValue24
Test3DataValue25
Test3DataValue26
Test3DataValue27
Test3DataValue28
Test3DataValue29

 

I would like to transpose the data so each index item shows the related data on one row:

IndexConcatNew Column1New Column2New Column3New Column4New Column5New Column6New Column7New Column8New Column9New Column10New Column11
Test1DataValue1DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue2DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue3DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue4DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue5DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue6DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue7DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue8DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue9DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue10DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue11DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue12DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue13DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue14DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue15DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue16DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue17DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue18DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue19DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue20DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test3DataValue21DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue22DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue23DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue24DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue25DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue26DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue27DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue28DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue29DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue30DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 

 

Ultimately, I would like to filter it down to unique indexes, but I can do that myself:

IndexConcatNew Column1New Column2New Column3New Column4New Column5New Column6New Column7New Column8New Column9New Column10New Column11
Test1DataValue1DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test2DataValue11DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test3DataValue21DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 

 

My question is can I do at least the column to row (second step/picture) conversion with a formula? 

 

Thanks for any help you can provide.

566 Views
0 Likes
7 Replies
Reply
7 Replies
OliverScheurich

‎Mar 20 2024 01:05 PM

‎Mar 20 2024 01:05 PM

Re: Maybe an array formula to transpose records?

@Jpey65 

=LET(ind,A2:A34,

conc,B2:B34,

HSTACK(UNIQUE(ind),IFNA(DROP(REDUCE("",UNIQUE(ind),LAMBDA(u,v,VSTACK(u,TOROW(FILTER(conc,ind=v))))),1),"")))

 

Does this return the intended result?

records.png

 

0 Likes
Reply
Jpey65

‎Mar 25 2024 10:43 AM

‎Mar 25 2024 10:43 AM

Re: Maybe an array formula to transpose records?

@OliverScheurich it does not work. It returns 0. As mentioned above the data is in a table, so I modified the formula: 

=LET(ind,A2:A34,conc,B2:B34,HSTACK(UNIQUE(ind),IFNA(DROP(REDUCE("",UNIQUE(ind),LAMBDA(u,v,VSTACK(u,TOROW(FILTER(conc,ind=v))))),1),"")))

 

0 Likes
Reply
OliverScheurich

‎Mar 25 2024 11:19 AM

‎Mar 25 2024 11:19 AM

Re: Maybe an array formula to transpose records?

@Jpey65 

=LET(ind;Tabelle4[Index];
conc;Tabelle4[Concat];
HSTACK(UNIQUE(ind);IFNA(DROP(REDUCE("";UNIQUE(ind);LAMBDA(u;v;VSTACK(u;TOROW(FILTER(conc;ind=v)))));1);"")))

 

If you have a dynamic table you can apply this formula. In my example the name of the table is Tabelle4. In your worksheet you have to replace Tabelle4 with the name of your table.

transpose.png

0 Likes
Reply
Jpey65

‎Mar 25 2024 02:45 PM

‎Mar 25 2024 02:45 PM

Re: Maybe an array formula to transpose records?

=LET(ind;Table1[Index];conc;Table1[Concat];HSTACK(UNIQUE(ind);IFNA(DROP(REDUCE("";UNIQUE(ind);LAMBDA(u;v;VSTACK(u;TOROW(FILTER(conc;ind=v)))));1);"")))

Returns an error:
The first argument of LET must be a valid name.
0 Likes
Reply
OliverScheurich

‎Mar 25 2024 03:13 PM

‎Mar 25 2024 03:13 PM

Re: Maybe an array formula to transpose records?

@Jpey65 

=LET(ind,Table1[Index],
conc,Table1[Concat],
HSTACK(UNIQUE(ind),IFNA(DROP(REDUCE("",UNIQUE(ind),LAMBDA(u,v,VSTACK(u,TOROW(FILTER(conc,ind=v))))),1),"")))

 

Does this work? I forgot to replace the semi-colons with commas in my last reply.

 

Otherwise you can try the below formula which uses "abcdefg" instead of "ind". However i don't think that "ind" is an invalid name in the english version of Excel.

 

=LET(abcdefg,Table1[Index],
conc,Table1[Concat],
HSTACK(UNIQUE(abcdefg),IFNA(DROP(REDUCE("",UNIQUE(abcdefg),LAMBDA(u,v,VSTACK(u,TOROW(FILTER(conc,abcdefg=v))))),1),"")))

 

 

If it still doesn't work in your sheet can you attach a screenshot without sensitive data which shows all the relevant data just like in my screenshots?

0 Likes
Reply
best response confirmed by Jpey65 (Copper Contributor)
Jpey65

‎Mar 26 2024 08:09 AM

‎Mar 26 2024 08:09 AM

Solution

Re: Maybe an array formula to transpose records?

https://www.ablebits.com/office-addins-blog/textjoin-function-excel-merge-text-multiple-cells/#:~:te....

=TEXTJOIN(", ", TRUE, IF([Index]=[@Index], [Concat], ""))


0 Likes
Reply
1 best response

Accepted Solutions
best response confirmed by Jpey65 (Copper Contributor)
Jpey65

‎Mar 26 2024 08:09 AM

‎Mar 26 2024 08:09 AM

Solution

Re: Maybe an array formula to transpose records?

https://www.ablebits.com/office-addins-blog/textjoin-function-excel-merge-text-multiple-cells/#:~:te....

=TEXTJOIN(", ", TRUE, IF([Index]=[@Index], [Concat], ""))


View solution in original post

0 Likes
Reply