Results 1 to 2 of 2
Thread: Sort a Vector Into a Matrix
2011-05-01, 16:28 #1
- Join Date
- Jun 2003
- Utah, USA
- Thanked 5 Times in 5 Posts
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.
2011-05-01, 18:09 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
If the original dataset is in A1:C6 and you want to create in A8 that output, you could create in A8 the formula:
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:
since Col C is offset 2 columns from Col A...
Last edited by sdckapr; 2011-05-01 at 18:12.