Sort Two Columns Simultaneously

• Hello all,
I'm not sure how to accomplish this seemingly simple task.

I'm trying to sort selected rows by two columns, but simultaneously instead of sorting by "Column A and THEN Column B". In my case, this is numerical data where there is ONLY data in one column or the other, never both.

For example:
5 _
10 _
_ 1
_ 6
_ 4

Should sort to:
_ 1
_ 4
5 _
_ 6
10 _

Thanks for any help.

• Re: Sorting By Two Columns Simultaneously

Hi UH_IT,

Welcome to the forum.

See if the following five steps help which are based on the sample data you provided in the range of A2:B6 using Excel 2003 and prior:

1. In cell C2 enter the formula =IF(A2="",B2,A2) and copy down to cell C6
2. Select the range A2:C6
3. From the Data menu select Sort
4. From the Sort dialog sort the data by Column C in Ascending sequence and click OK
5. Delete Column C (if desired).

HTH

Robert

• Re: Sorting By Two Columns Simultaneously

Hi and welcome to the forum. Are there other columns to the right or left of these two columns that need to be sorted with it? (i.e. dependent rows/Columns).

I presume your using an underscore (_) to represent a blank?

Ger

Check out our new reputation system. Click on the Like button under the post!
_______________________________________________

[SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

The BEST Lookup function of all time

Dynamic Named Ranges are your bestest friend[/SIZE]
_______________________________________________

• Re: Sorting By Two Columns Simultaneously

Quote

I'm trying to sort selected rows by two columns, but simultaneously instead of sorting by "Column A and THEN Column B". In my case, this is numerical data where [COLOR="Blue"]there is ONLY data in one column or the other, never both[/COLOR].

If there is only ever data in one column then why would you need to sort by two columns simultaneously? Obviously, you can't sort data that isn't there. Guess I'm missing something here.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Sorting By Two Columns Simultaneously

That's what I thought too. Makes no sense to me.

IF there is data in both Columns, you will need to do 2 sorts, one for each column. BUT, this will re-align any corresponding cells in the 2 Columns.

• Re: Sort Two Columns Simultaneously

Well, it makes sense to me, in a really messed up, too much coffee, kind of way... ::D

In reality, I had code already written as I faced this problem before... see attached for what I think the OP is trying to achieve...

Run Macro "Sort and Shuffle"

It was written a LONG time ago, so please, no flaming the code ::D

It was originally designed to "align" two lists of data... the lists could have been several columns wide.

As far as I can remember this works for names (text) as well as numbers...

Ger

: yes, it works for names (text) as well as numbers... ::D

Files

Check out our new reputation system. Click on the Like button under the post!
_______________________________________________

[SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

The BEST Lookup function of all time

Dynamic Named Ranges are your bestest friend[/SIZE]
_______________________________________________

• Re: Sort Two Columns Simultaneously

Quote

where there is ONLY data in one column or the other, never both

That confuses the heck out of me.

• Re: Sort Two Columns Simultaneously

More coffee dave... c'mon... catch up ::D

Check out our new reputation system. Click on the Like button under the post!
_______________________________________________

[SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

The BEST Lookup function of all time

Dynamic Named Ranges are your bestest friend[/SIZE]
_______________________________________________

• Re: Sort Two Columns Simultaneously

Thanks for all the replies! It dawned on me like half an hour after I posted to simply copy whichever value wasn't a NULL into a 3rd column and to use that to sort like Trebor76 was saying.

The reason for the blanks is that this is a budget/transaction history worksheet. In this particular case, one column is for encumbered transactions, while the others are actual transactions. A transaction in this case can only be of one type or another.

I'll admit I'm more of a DB person, and pulling this info from a query gets me what I want in the right format, but unfortunately I just have these giant worksheets to work with, and I don't use Excel much beyond basics.

Thanks again!

• Re: Sort Two Columns Simultaneously

Quote

where there is ONLY data in one column or the other, never both

So there could be data in both columns.

• Re: Sort Two Columns Simultaneously

QUESTION: Your macro is fantastic by the way! Kudos!! I have a question about it. Is it possible to modify the macro so that the results are all displayed on separate rows? For example, if there are two names that are the same it puts them on the same row. Would it be possible to have it alphabetize, but also place same names on different rows?

• Re: Sort Two Columns Simultaneously

dbjranger,

Welcome to the board, please take a moment to go over the rules you agreed to when you joined this board.

Please open your own thread and link back to this one if you like.

2a. Search for an answer to your question. If everyone follows rule number 1, you will get a surprisingly accurate result.
2b. Please dont open old Threads. Someone who posted a question 10 years ago is probably still not waiting for a solution, even if you are certain it is the best solution!
2c. Please dont start a new question in an existing thread. This is known as Thread hi-jacking. Start your own thread. Its Free! If you need to, you can link back to the thread that you thought might have been helpful.

Bruce :cool:

• Re: Sort Two Columns Simultaneously

I'm a bit confused as to how the thread works. Can you be more specific as to what the issue is? Is this informative? Do you want me to change something I did?

• Re: Sort Two Columns Simultaneously

Quote

Please open your own thread and link back to this one if you like.

Start your own new thread, this thread was started by the person in the first post, not you.

Rule 2C in the rules I included explains that.

I also asked you to read the rules you agreed to when you joined this board.

Down below the yellow band near the top of this page, you will see + Post New Thread, that is where you start your own thread.

This thread is now closed.

Bruce :cool: