Results 1 to 11 of 11
Thread: Transpose column info (2003)

20070226, 17:26 #1
 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.

20070226, 18:22 #2
 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 controlc (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.jkpads.com
Professional Office Developers Association

20070226, 18:49 #3
 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.

20070227, 13:49 #4
 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....................

20070227, 14:10 #5
 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.

20070227, 16:18 #6
 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)

20070227, 17:16 #7
 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.

20070227, 17:27 #8
 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.

20070227, 17:35 #9
 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)

20070227, 17:39 #10
 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?

20070227, 17:41 #11
 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.....