MS Excel Sorting

February 8, 2006 8:59 AM

I've got two columns in MS Excel. They each share 200 numbers. However, one of the columns has an additional 700 numbers. I want the column with 900 sorted in ascending order, with like numbers from the column of 200 beside those in the column of 900. How do I do that?
posted by panoptican to computers & internet (18 answers total)

Highlight the individual column then goto to the "data" pulldown menu => choose sort => then choose either ascending or descending. Repeat for the next column.
posted by jadepearl at 9:05 AM on February 8, 2006

If I understand you, you can just select both columns by clicking on the header letters. If the columns aren't next to eachother you can click one heading row and then CTRL-click the next one. Once the columns are selected, go under the Data Menu, choose which column to sort by [in this case, the 900 number one, whatever its name is Column A or whathaveyou] and choose Ascending. Tell Excel whether you have a header row or not and click okay. You can alays undo a sort by going under the Edit menu and choosing Undo, so don't feel bad trying this out to see if it works the way you want it to.
posted by jessamyn at 9:06 AM on February 8, 2006

I don't think I was completely clear. Column A has 900 unique numbers. Column B has 200 numbers which are also in Column A. I want to sort both of the columns so that, for instance, number 64538 in column B is beside 64538 in column A. Using either of the two above options just sorts column A and then column B in ascending order without regard to keeping like numbers on the same row.
posted by panoptican at 9:13 AM on February 8, 2006

In other words, once sorted, it would look something like this:

64536
64537
64538     64538
64539
64540
64541
64542     64542
64543
64544

And so on.
posted by panoptican at 9:15 AM on February 8, 2006

What jessamyn said.

1) Select both columns (CTRL-click the column names like A & C)
2) Data -> Sort -> Sort By ColumnA (or which ever has the 900 numbers)
3) Click OK


Which is just a different way of saying "what jessamyn said".
posted by raedyn at 9:18 AM on February 8, 2006

Wait, after reading your clarification one question:

The peices of data that you want next to one another at the end, are they currently together?

I mean, is it currently

64542 64542

or are they currently in no particular order and you want the like numbers to 'find' each other (for lack of better terminology).

If they are currently in the same row and you want to KEEP them together, what jessamyn describes will work. Try it.
posted by raedyn at 9:21 AM on February 8, 2006

So the numbers that are the same, let's say 64542 in column A and 64542 in Column B are not currently in the same row but you want them to be that way after the sort?
posted by jessamyn at 9:21 AM on February 8, 2006

(I don't know if I'm being clear, so here's another try)

Is the table currently

3 3
4
6
2
7 7
3
7
8
9 9
1

or is it currently
3 3
4 7
6 9
2
7
3
7
8
9
1
posted by raedyn at 9:23 AM on February 8, 2006

Nope. Not on the same row. In fact, both columns seem to be in random order at the moment. When sorting, I would indeed like the numbers in the 200 number column to find their buddies in the 900 number column.
posted by panoptican at 9:25 AM on February 8, 2006

Rather than "sorting" column B, I think it's going to be easier to determine whether a specified number is in column B. Here's a way to do that:

Insert a new column between A and B. This new blank column is now column B, and the set of 200 numbers is in column C. Go ahead and sort column A on its own.

Let's say the first number in column A is in A1, and the numbers in column C are in C1 through C200. In B1, enter the formula:

=IF(COUNTIF($C$1:$C$200,"="&A1)>0,A1,"")

Then drag the lower right corner of B1 down the column to B900, to fill the formula in all those cells.

I'm assuming here that the numbers in column A are unique (no duplicates), and the numbers in column C are unique. If either has duplicates, this may not work the way you want it to. (For example, if "28305" appears three times in column A, and only once in column C, this formula would cause 28305 to appear three times in column B, which may not be what you want.)
posted by DevilsAdvocate at 9:25 AM on February 8, 2006

To put things in context. What I have is a call list with 900 contacts that we used for a recent survey. 200 people completed the survey and so I have a seperate dataset. I want to be able to eliminate the 200 people who completed the sruvey from the call list. I am using phone numbers. I coped the phone number columns from my dataset and pasted it as a new column into my call list directly beside the phone numbers column.

DevilsAdvocate's solution won't work.
posted by panoptican at 9:28 AM on February 8, 2006

I did this:

Put the 900 column left (say col F, starting row 12), the 200 column right (G12) . Sort both ascending

Then in column H put the following

=+VLOOKUP(F12,G$12:G$25,1,FALSE)

(where the rows in G are the range of the numbers)

Copy this formula with the absolute references preserved

This will put the numbers from col G opposite their numbers in H and will leave "N/A" for all the other columns.
posted by sagwalla at 9:30 AM on February 8, 2006

This was my output

1 1 1
2 5 #N/A
3 8 #N/A
4 44 #N/A
5 5
6 #N/A
7 #N/A
8 8
9 #N/A
11 #N/A
22 #N/A
33 #N/A
44 44
51 #N/A
posted by sagwalla at 9:34 AM on February 8, 2006

DevilsAdvocate's solution won't work.

Why not?

If inserting an extra column causes problems, you can undo it at the end. After doing what I described above:
- highlight column B
- Edit - Copy
- (with column B still highlighted) Edit - Paste Special - Values
- delete column C

Then all the columns are back in their original places.

If it won't work because you do have duplicate values, let me know and I can try to find a way around that. If it's something else, it would be helpful to know why my solution doesn't work for you before attempting to come up with a different one.
posted by DevilsAdvocate at 9:34 AM on February 8, 2006

DA's solution works for me on a small sample set. Maybe not for phone numbers with hyphens?
posted by beagle at 9:36 AM on February 8, 2006

Actually it did work, I just did it wrong. sagwalla's worked too. Hooray.
posted by panoptican at 9:37 AM on February 8, 2006

sagwalla has it.

If you want to fill column H with blanks instead of "N/A"s for the non-matches use this template:

=if(VLOOKUP(F12,G$12:G$25,1,FALSE)=f12,f12,"")

If you're a Nervous Nelli you might want to do it all in a scrap worksheet and then copy/paste special/value columns F and H in back into the original spot.
posted by Opposite George at 9:40 AM on February 8, 2006

Oops. Y'all did it while I was previewing!
posted by Opposite George at 9:41 AM on February 8, 2006