Sort a Vector Into a Matrix

Topic

New Reply

Ideally, I would like to do this without VBA … functions would be preferable.

If I have a range of several rows and several columns, it is easy for me to write those out into a single column with some structure.

But, I want to do the reverse. I have 3 columns, data in one, a value for a row in the second, and a value for the column in the third. How do I put them into a rectangular range with functions?

For example, I have:

Echo 2 2
Charlie 1 3
Delta 2 1
Alpha 1 1
Foxtrot 2 3
Bravo 1 2

And I want to create:

Alpha Bravo Charlie
Delta Echo Foxtrot

What I’d like to do is be able to put a flexible function in each of the 6 cells of the above range, that references the earlier set of 6 rows and 3 columns, and puts the right item in each spot.

Reply | Quote

Replies

If the original dataset is in A1:C6 and you want to create in A8 that output, you could create in A8 the formula:

=INDEX($A$1:$A$6,SUMPRODUCT((ROW()-7=$B$1:$B$6)*(COLUMN()=$C$1:$C$6)*ROW($A$1:$A$6)))

Copy this from A8 and Paste to A8:C9

If you start in a different row, The “7” should be changed to one less than the row you start in (the other way to look at is that you are offset 7 rows from row 1). If you start in a column >A then the formula needs to be adjusted for the offset as well. If for example you start in Col C then it should be:
…(COLUMN()-2=$C$1…

since Col C is offset 2 columns from Col A…

Steve

Reply | Quote