#1
|
|||
|
|||
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:
Index Concat Test1 DataValue1 Test1 DataValue2 Test1 DataValue3 Test1 DataValue4 Test1 DataValue5 Test1 DataValue6 Test1 DataValue7 Test1 DataValue8 Test1 DataValue9 Test2 DataValue10 Test2 DataValue11 Test2 DataValue12 Test2 DataValue13 Test2 DataValue14 Test2 DataValue15 Test2 DataValue16 Test2 DataValue17 Test2 DataValue18 Test2 DataValue19 Test3 DataValue20 Test3 DataValue21 Test3 DataValue22 Test3 DataValue23 Test3 DataValue24 Test3 DataValue25 Test3 DataValue26 Test3 DataValue27 Test3 DataValue28 Test3 DataValue29 I would like to transpose the data so each index item shows the related data on one row: Capture1 hosted at ImgBB — ImgBB Ultimately, I would like to filter it down to unique indexes, but I can do that myself: Capture2 hosted at ImgBB — ImgBB 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. |
#2
|
|||
|
|||
In your example data, Test1 and Test3 have 9 values for Concat, Test2 has 10 values. So the number of values for every TestX can vary? When yes, you have to determine, must be Concat columns filled in order (having empty fields at rightmost columns, or every Concat value must have a specified column it belongs into.
In my example (have to make it in written form, as I have not Excel available currently), I assume the 1st possibility. I assume, your table headers are in cells A1 and B1. 1. Into cell C1 enter a header for new column, e.g. TableRow; Into Cell C2 enter the formula =ROW()-1 and copy it down to bottom of table; 2. Define the datarange of Index (from A2 down until bottom of table) as named range (e.g nIndex); Define the datarange of Concat column as named renge (e.g. nConcat); Define the datarange of TableRow column as named range (e.g. nTableRow). 3. Into cell D1 enter a header for new column, e.g. TestRow; Into cell D2, enter the formula =COUNTIFS(nIndex, $A2,nTableRow, "<=" & $C2) and copy it down to bottom of table. You get all different concat values for every Index value numbered. 4. Define the datarange of TestRow column as named range (e.g. nTestRow). 5. On another sheet, create a table (NB! A regular table, not a Defined one) with header A1="Index", and into next columns enter numbers 1, 2, ... (at least as many Concat columns as was the biggest number of rows for any index value in 1st table); In 2nd table, fill the column Index with unique Index values from 1st table; Into cell B2 of 2nd table, enter the formula Code:
=IF(SUMIFS(nTableRow,nIndex,$A2,nConcat, B$1)=0,"",INDEX(nConcat,SUMIFS(nTableRow,nIndex,$A2,nConcat, B$1))) In case you want the new table to have normal headers instead of numbers: Replace all formulas with their values (PastSpecial>Values); Overwrite headers. |
#3
|
||||
|
||||
I can get it like this very easily (do you really need to keep the Concat column? and what does it contain anyway?):
2024-03-20_234208.jpg What version of Excel are you using? A workbook with your data would be very useful otherwise we're just guessing (probably wrongly) your setup. |
#4
|
|||
|
|||
When writing my previous post (#2), it was over midnight for me, and I did make it too difficult.
Forget about entering any Concat numbers into 1st row of 2nd table. Design table headers as you want. The formula you enter into B2 of 2nd table will be: Code:
=IF(SUMIFS(nTableRow,nIndex,$A2,nConcat, COLUMN()-1)=0,"",INDEX(nConcat,SUMIFS(nTableRow,nIndex,$A2,nConcat, COLUMN()-1))) |
#5
|
||||
|
||||
theuser may already have his answer here:
Maybe an array formula to transpose records? - Microsoft Community Hub |
#6
|
||||
|
||||
Perhaps the OP should take a moment to read A message to forum cross posters - Excelguru ?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Concatenating in an Array formula | Phil H | Excel | 6 | 02-25-2019 08:36 AM |
Array formula value not available error for one result | emarkona | Excel | 1 | 03-08-2018 03:00 PM |
Array Formula | Macacidic | Excel | 2 | 09-12-2017 08:19 AM |
Excel simplify formula - array of sequential numbers | Wries | Excel Programming | 1 | 05-12-2014 08:32 AM |
Complex array formula | andrei | Excel | 9 | 02-03-2012 03:40 AM |