Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transpose column info (2003)

    I don't know if this is possible but I thought I would give it a chance. I have two columns of numbers. I want the numbers in column 1 to be larger than the number in column 2. Say I have 30 in column 1 and 80 in column 2 - that is what I want - the smaller number in column 1 going across and the larger number in column 2 going across. What I would like to do is when the number in column 1 is larger than the number in column 2, I would like it switched. If 80 is in column 1 and 30 is in column 2 going across, I would like the numbers switched so it reads 80 and then 30 going across. I hope this makes sense. Thanks for any help you can provide. I have over 20,000 rows - I was trying to do it manually but it would take me days.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column info (2003)

    Easiest is with two extra columns. In C2 enter:

    =IF(A2>B2,B2,A2)

    In D2:

    =IF(A2>B2,A2,B2)

    Copy both cells down to match your # of rows

    Now select columns C and D and select control-c (copy).

    Then select cell A1 and select Edit, paste special, values. Finally remove col. C and D.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose column info (2003)

    Alternatively, use the formulas

    =MAX(A2,B2)

    and

    =MIN(A2,B2)

    and proceed as described by Jan Karel Pieterse.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column info (2003)

    Thanks to both of you! Worked perfectly. Saved many hours of work....................

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column info (2003)

    Spoke to soon. Sometimes the second column will be blank. Like 50 and then the next column will be blank. In those cases, I would like the 50 to stay in column 1. I only want to transpose where there are two numbers going across. The first column will always have a number but the second column could be blank in which case that is correct order.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose column info (2003)

    You can use
    <code>
    =IF(B2="","",MIN(A2,B2))
    </code>
    instead of
    <code>
    =MIN(A2,B2)
    </code>
    (The formula with MAX can remain unchanged)

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column info (2003)

    Sorry to be back. It works for the blanks but where there are two numbers, it puts the large number first and the small number in the second column. It should be smaller number and then larger - If I have a 30 in column A and a 60 in Column B - nothing should happen - it is correct. If I have a 60 in column A, and a 30 in column B - then it should reverse - 30 in column A and 60 in column B.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose column info (2003)

    You originally asked "I want the numbers in column 1 to be larger than the number in column 2." That's what the formulas we provided do. If you want it to be the other way round, exchange MIN and MAX in the formulas.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column info (2003)

    I reread what I wrote and I asked for smaller in column 1 and larger in column 2. My example was wrong - I put 80 in column 1 and the smaller in column 2 as an example. Talk about ambiguous. Sorry.
    I fiddled around with what you sent and came up with the following that works:

    =MAX(AK2,AL2)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose column info (2003)

    Shouldn't the first one use MIN instead of MAX?

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column info (2003)

    Yes, I just came back to make the correction. I knew you would probably beat me to the punch. Thanks again.....

Posting Permissions

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