Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 17:12.

Posting Permissions

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