CODEWITHSUNDEEP
excel
Rekcs
Rekcs

Reputation: 879

Transform table into column

I am trying to get the results of a basketball season. This is the table I have with random values:

enter image description here

What I want is transform that into something like this:

enter image description here

I am currently using this formula =A19 & " - " & C18 and in this case the results I get from it is:

enter image description here

And then I just copy the results and past it as Transpose.

I don't know if I was clear, something you need tell me and I will update the question.

Upvotes: 2

Views: 120

Answers (1)

basic
basic

Reputation: 11968

You can try the following formulas (edited to make formulas work from the second row) -

For the first team:

=INDEX($B$1:$E$1;1;INT((ROW()+2)/(ROWS($A$2:$A$5))))

For the second team:

=INDEX($A$2:$A$5;ROW()-1-ROWS($A$2:$A$5)*INT((ROW()-2)/ROWS($A$2:$A$5));1)

For result:

=INDEX($B$2:$E$5;MATCH(INDEX($A$2:$A$5;ROW()-1-ROWS($A$2:$A$5)*INT((ROW()-2)/ROWS($A$2:$A$5));1);$A$2:$A$5;0);MATCH(INDEX($B$1:$E$1;1;INT((ROW()+2)/(ROWS($A$2:$A$5))));$B$1:$E$1;0))

The tournament table must be symmetrical.

If the first two formulas are in separate columns (as in my example), in the third formula, the inner INDEX (...) functions can be replaced by a reference to the corresponding column.

enter image description here

Upvotes: 1

Related Questions