Sorting in Excel 3

[AlsGal52]

I have a file in Excel that has 30 columns and over 3,000 rows of text and I need to sort by more than 3 columns as is usually the case because I'm looking for duplicates and it'll take more than 3 columns to decide which are duplicates.

Is there a way to sort by Column A, then Column B, then Column C, then Column D, then Column E, etc.?

 

[carrr]

You don't specify what version you're running, but have a look at this:

http://support.microsoft.com/?kbid=268007

 

[jdhilljr]

This will take through column I but can be continued.


Sub Macro1()
Cells.Select
Selection.Sort Key1:=Range("A1&quot, Order1:=xlAscending, Key2:=Range("B1&quot _
, Order2:=xlAscending, Key3:=Range("C1&quot, Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("D:IV&quot.Select
Selection.Sort Key1:=Range("D1&quot, Order1:=xlAscending, Key2:=Range("E1&quot _
, Order2:=xlAscending, Key3:=Range("F1&quot, Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("G:IV&quot.Select
Selection.Sort Key1:=Range("G1&quot, Order1:=xlAscending, Key2:=Range("H1&quot _
, Order2:=xlAscending, Key3:=Range("I1&quot, Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

 

[Chocobar]

Hello,

Actually what i will do is like this,
If you want to sort by A, then B, then C, then D, then E, then F, ... and so on, I will click on the last column, let say is Z and then click on the icon showing sorting A to Z, then I will click on Y and click on the sorting icon. Will repeat it until I reach Column A. This way I will have the correct sorting. I always start from the last sort to the first,

Furthermore, you could do a macro recording so that you don't need to click so much next time.

Hope it helps!

Chocobar

 

[jdhilljr]

DONT USE MINE I MADE AN ERROR.

***********************************
I WILL GET YOU THE CORRECT ONE.

THERE IS A CHANCE FOR A PROBLEM WITH THIS ONE.

 

[AlsGal52]

Thanks all for your help. Chocobar, yours was the easiest to work with. Thanks...it worked perfectly. A star for you!

 

[KenWright]

The other option is to use a helper column. Decide which group of columns you can rely on to determine whether or not the row is unique, concatenate them in the helper column, and then just sort on the helper column and then delete it.

Regards
Ken....................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[klmorgan]

If the column contents are not too great concatinate the colums into a single column in the order of the sort you require and then sort on the concatenated column only.

You can contatinate as many colums as you want so long as you dont break the column limit

Regards

Keith

 

[KenWright]

Echo????


----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[AlsGal52]

You guys are SO GREAT! I tried the concatenate option. Of course, the helper column looks weird but it works fabulous! Concatenated from column C to column S.

Thanks all...I LOVE this site! So many helpers...so much to learn!

Jane

 

[KenWright]

Glad it worked for you, and very much appreciate the feedback.

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[klmorgan]

Sorry about the echo, I read the more complicated answers and dived into an answer of my own not seeing yours at the bottom.

I used this a couple of years ago when a collegue was complaining that Excel only went to 3 levels of sort and Lotus had allowed as many as you liked.

Regards

Keith

 

[KenWright]

LOL - No Probs, but did look funny ;-)

Rgds
Ken.............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[ladyck3]

Chocobar and Ken... I give you BOTH stars...

As for Chocobar, its easy enough to do your method... it rocks because you don't have to do a formula ....

HOWEVER...

Ken...the idea to create a DUMMY row is ingenious... you have such common sense ideas sometimes that ya just wanna slap your forehead and say .... Why didn't *I* think of that!!! COOL BEANS (once again)

Stars to you both, cuz both are pretty cool!!!

Laurie

LadyCK3