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.

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
and copy this formula to all cells of datarange of all columns with numbers as headers.

In case you want the new table to have normal headers instead of numbers:
Replace all formulas with their values (PastSpecial>Values);
Overwrite headers.

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.

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:
You can keep formulas in 2nd table. Whenever you edit 1st table, when in 2nd table the value of Index is registered, and there is enough Concat columns present, the changes are available in 2nd table immediately.

theuser may already have his answer here:
Maybe an array formula to transpose records? - Microsoft Community Hub

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