2 Column sorting question

**cwg** · 01-19-2008, 01:31 PM

Hello,

(I did a search on subsort and on sort, and didn't quite find what answered my question)

I have a spread sheet with lots of columns. I need to sort column A. No problems there. but I need to subsort column B .

PO Cust Item (column B)
4500025611 R518851
4500089149 R518437
4500089149
4500089152 R86007-E
4500089152 R86007-E
4500107827 R86007-E
4500107827 R518436-F
4500107827 R518436-F
4500107827 R518851-G
4500107828 R86007-E
4500107828 R86007-E
4500107828 R86007-E
4500108239 R86007-E
4500109038 R86007-E
4500110406 R86007-E
4500113438 R518851-G
4500113439 R86007-E
4500113439 R505626-B
4500113440 R86007-E

I can't for the life of me figure this out, could anyone shed some light on this please?

Thanks much for looking and any help.

I removed from the other forum.

**oldchippy** · 01-19-2008, 01:51 PM

Hi,

You need a few helper columns with this one to extract the number in B to do the second sort.

Select all the data A2:F20, then sort by A then by F

**cwg** · 01-19-2008, 02:39 PM

Thanks chippy. It appears you have been around awhile and helped out lots of people.
While I don't have the knowledge in this area to help anyone, if the site takes donations, I would be happy to make one for you and all the people who help out here :-)

I am not sure I understand the answer (I kind of do)

The data starts in row 2 and goes to row 821
The columns start in A and go to P

It is currently sorted by column A and I cut and pasted to get column B next to A so that they were side by side (for easier reading) I got lost on the helper column part and what it was I was supposed to do.

**oldchippy** · 01-19-2008, 03:21 PM

Hi cwg,

All the help here is free, so it doesn't matter how many questions you have, there's always someone willing to offer an answer. If you can think of something you want to do in Excel, chances are someone will know how to do it - one way or another!

Right your data is in Row 2 down to row 821 - OK, insert five columns next to column A.

Now copy and paste your data you need next to B (for easier reading)

Copy the formulas from my example C2:F2 into row 2. You should now have the number (extracted) from B2 in F2. (If you haven't something is wrong)

Now select C2:F2 and autofill down the columns - the quickest way - with C2:F2 selected is to double-click on the little black square in the bottom right of cell F2. You should now have all 821 rows full.

Now select ALL of the rows from A2 to A821, go to Data > Sort and sort by column A then by column F. If you are now happy that the data is sorted correctly, you can now delete columns B:F.

You are now left with your original data, but sorted in the order you want - hopefully!

**cwg** · 01-19-2008, 07:03 PM

Thanks for the help oldchippy :-)

I had to take it to kinkos to get it printed and someone new was there (well new to me)

I was trying to go over the directions and this was suggested. Please in your opinion let me know if this is a proper way to sort or if it is just moving data in 2 columns

1. Highlight the entire document
2. Go to sort and fill
3. Select custom sort
4. Use the first drop down menu (labeled sort by) and select PO
5. Click the Add Level Button (which brings up another line called "then by"
6. Use the "then by" drop down menu to select "order"
7. Click OK

What I am worried about is that while it seems it may sort both A and B, I'm worried that it may not move the correlated data accordingly from the other columns.

Any comments or opinions?

Thanks

**oldchippy** · 01-20-2008, 08:30 AM

Hi,

From your discription you are using 2007 (are you?) and yes it is OK and will retain the data in the other columns with its associated data when it does the sort.

I can't see all of your data, only the sample you have posted, so I wasn't sure whether that when you have duplicate numbers in column A, whether the associated data in column B was in numerical order or not?

For example there are two in your data like so

4500113439 R86007-E
4500113439 R505626-B

but further down the list you may have something like this

4500113439 R505626-B
4500113439 R86007-E

So the method I was using would have sorted those with the smallest number in column B first like so

4500113439 R86007-E
4500113439 R505626-B

Hope this all makes sense to you and I'm not making it confusing?

2 Column sorting question

LinkBack

Thread Tools

Rate This Thread

Display

2 Column sorting question

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions