# Thread: Sort a Vector Into a Matrix

1. ## Sort a Vector Into a Matrix

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.

2. 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•