Many Sort Keys

  • Thread starter Thread starter yolanda.silva
  • Start date Start date
Y

yolanda.silva

Is there a way to have a macro written to include many sort keys? The
one I have now sorts for 3 things - but it seems when I try to sort
for 4, it gives me an error?

This is what I currently have:

Sub SortMe()

Cells.Sort Key1:=Range("F1"), Key2:=Range("B1"),
Key3:=Range("C1"), Header:=xlYes

End Sub


I tried just adding in to make it:

Sub SortMe()

Cells.Sort Key1:=Range("F1"), Key2:=Range("B1"),
Key3:=Range("C1"), Key4:=Range("D1"), Header:=xlYes

End Sub

But it says "Named Argument Not Found"... am I doing something wrong?

Can someone help??

(I didn't write the macro - someone else just told me what to put and
where it went!)

Thanks :)
 
Just as in XL's Sort command, you can only sort 3 columns at a time, so
sort the 4th column first, then the other three. Try:


Public Sub SortMe()
Cells.Sort Key1:=Range("D1"), Header:=xlYes
Cells.Sort Key1:=Range("F1"), Key2:=Range("B1"), _
Key3:=Range("C1"), Header:=xlYes
End Sub
 
Just as in XL's Sort command, you can only sort 3 columns at a time, so
sort the 4th column first, then the other three. Try:

Public Sub SortMe()
Cells.Sort Key1:=Range("D1"), Header:=xlYes
Cells.Sort Key1:=Range("F1"), Key2:=Range("B1"), _
Key3:=Range("C1"), Header:=xlYes
End Sub















- Show quoted text -
Click to expand...

This worked :) Thanks! But... I forgot... I actually need it to sort
not alphabetically but by an order considered "in severity". The
database is listing levels of corrective action for employees... the
levels are verbal, written, final written and then termination.

Is there a way to use this to sort first by the 3 columns I had before
(which, for reference, are the supervisor name, last name of employee,
first name of employee - respectively) and then sort it by the level
of corrective action where verbal is the first one on the list,
written is second, final written is third and termination is last in
order?

Thanks! :)
 
You should introduce a helper column and allocate a code for the
descriptions of severity. You might like to set up a small table
somewhere (eg X1:Y4) like this:

verbal 7
written 5
final written 3
termination 1

then you could use a VLOOKUP formula to "translate" the description to
its value in the helper column. You would then use the helper column
as one of your sort keys. Note that I've left gaps in the numbers in
case you introduce other descriptions in the future, but you could
just use 1 to 4.

Hope this helps.

Pete
 
Actually, I've just re-read your last sentence, and I think you'll
want to change the numbers around so that verbal is 1 etc.

Incidentally, you could save doing the sort twice by joining together
the employee's last name and first name into one column, so that you
only have 3 sort keys.

Hope this helps.

Pete
 
One way:

Public Sub SortMe()
Dim nListCount As Long
With Application
.AddCustomList ListArray:=Array( _
"verbal", "written", "final written", "termination")
nListCount = .CustomListCount
End With
Cells.Sort Key1:=Range("D1"), _
Header:=xlYes, _
OrderCustom:=nListCount
Cells.Sort Key1:=Range("F1"), _
Key2:=Range("B1"), _
Key3:=Range("C1"), _
Header:=xlYes
Application.DeleteCustomList nListCount
End Sub
 
One way:

Public Sub SortMe()
Dim nListCount As Long
With Application
.AddCustomList ListArray:=Array( _
"verbal", "written", "final written", "termination")
nListCount = .CustomListCount
End With
Cells.Sort Key1:=Range("D1"), _
Header:=xlYes, _
OrderCustom:=nListCount
Cells.Sort Key1:=Range("F1"), _
Key2:=Range("B1"), _
Key3:=Range("C1"), _
Header:=xlYes
Application.DeleteCustomList nListCount
End Sub



- Show quoted text -
Click to expand...

I don't know if I'll get any more assistance with this now that so
much time has passed. I got busy yesterday and forgot all about
this...

I tried the macro that was written - it didn't work. Not sure if I
did something incorrect. I put it where I had the previous macro...

As for the table option - can I put the table on a separate sheet with
the other info for the drop down boxes?

The reason I am separating first and last name is that if I leave it
as one box, I imagine people will inevitably do it incorrectly by
putting first name, last name while others put last name, first
name... there's no way to ask last name then first name within one
cell is there?
 
One way:

Public Sub SortMe()
Dim nListCount As Long
With Application
.AddCustomList ListArray:=Array( _
"verbal", "written", "final written", "termination")
nListCount = .CustomListCount
End With
Cells.Sort Key1:=Range("D1"), _
Header:=xlYes, _
OrderCustom:=nListCount
Cells.Sort Key1:=Range("F1"), _
Key2:=Range("B1"), _
Key3:=Range("C1"), _
Header:=xlYes
Application.DeleteCustomList nListCount
End Sub



- Show quoted text -
Click to expand...

Oh... also - I think I may have miscommunicated how I wanted it
sorted...

I want it to first sort by column F... after that, I want the D column
(which has the level of correct action) to put all the verbals
together, then written, final written and finally termination... after
those are sorted... I'd like them to be sorted by last name, first
name WITHIN that category..

Does that make sense?

Thanks!
 
You could put the table on a separate sheet. However, as you only have
four items you can build these into the formula like so:

=LOOKUP(D2,{"final written","termination","verbal","written"},
{3,4,1,2})

I am assuming that you have these values in column D - just copy the
formula down your helper column and then sort on this column.

You could put a comment in each cell (or use Data Validation - input
message) to tell your Users about Last Name first, but what I was
suggesting is to leave your columns as they are and to use another
helper column (which could be hidden from view) - in this column you
can just join the Last Name with the First Name using &, and then have
this as one sort field.

If you don't join them then I would suggest that you do a sort using
Last Name and First Name columns as the sort keys first, and then do a
sort on column F and the helper column for actions.

Hope this helps.

Pete
 
You could put the table on a separate sheet. However, as you only have
four items you can build these into the formula like so:

=LOOKUP(D2,{"final written","termination","verbal","written"},
{3,4,1,2})

I am assuming that you have these values in column D - just copy the
formula down your helper column and then sort on this column.

You could put a comment in each cell (or use Data Validation - input
message) to tell your Users about Last Name first, but what I was
suggesting is to leave your columns as they are and to use another
helper column (which could be hidden from view) - in this column you
can just join the Last Name with the First Name using &, and then have
this as one sort field.

If you don't join them then I would suggest that you do a sort using
Last Name and First Name columns as the sort keys first, and then do a
sort on column F and the helper column for actions.

Hope this helps.

Pete





- Show quoted text -
Click to expand...

Maybe I should mention that I don't know a whole lot about excel and
we should go from there :)

Ok... I love the idea of being able to put Last Name, First Name
linked together - can you explain (simply?) how to do that?

Also... in the D column where level is - there is already a validation
in place (a drop down box that contains the 4 choices of corrective
action). Is it possible to have this validation as well as the Lookup
tool? I was trying to do the VLookup thing and it wouldn't work
because there is already validation there? But, it might be that I
don't understand how to do it... I was reading from contextures.com to
try and figure it out but... most of it's "greek to me"... so I get
lost trying to understand what goes where...

Maybe more info is needed...

I'm not sure if you need all the columns or not - but...

A - Rep ID
B - Last Name
C - First Name
D - Level of C.A.
E - Date Issued
F - Supervisor
G - Delivered To
H - Date Delivered
I - Hire Date

So, if we have 200 people listed here, I want it to sort by the
supervisor first - all of John Doe's people grouped together... but,
within John Doe's list of people, I want the people on verbal's to be
on top, then written, then final written, then termination... once
those are grouped accordingly, I want them sorted by last name then
first name...

So much! Sorry :) Can you help? :)
 
Okay. Use columns J and K as helper columns. I'm assuming you have a
row of headers, so put "Full Name" in J1 and "Action Type" in K1.
Enter this formula in J2:

=B2&", "&C2

then copy this down the column for as many entries as you have - a
quick way of doing this is to select J2 and double-click the fill
handle, which is the small black square in the bottom right corner of
the cursor. This will give you the names joined together like "Doe,
John" down the column.

Enter the lookup formula I gave you earlier in K2 and copy that down -
that should give you values ranging from 1 to 4 down the column,
depending on the entries in column D.

Now you should highlight all the data and headings (including the
helper columns) from A1 to K200 and click on Data | Sort - in the
panel you should click Header Row if it is not already checked, and
select Supervisor as the first sort key, Action Type as the second
sort key, and Full Name as the third sort key. Click OK and you should
have what you want.

If you add any more data in the future you should remember to copy the
formulae in columns J and K to cover the extra data. I did mention
that you could hide the helper columns - click on the column
identifying letters to highlight columns J and K, then Format |
Columns | Hide.

Hope this helps.

Pete
 
Okay. Use columns J and K as helper columns. I'm assuming you have a
row of headers, so put "Full Name" in J1 and "Action Type" in K1.
Enter this formula in J2:

=B2&", "&C2

then copy this down the column for as many entries as you have - a
quick way of doing this is to select J2 and double-click the fill
handle, which is the small black square in the bottom right corner of
the cursor. This will give you the names joined together like "Doe,
John" down the column.

Enter the lookup formula I gave you earlier in K2 and copy that down -
that should give you values ranging from 1 to 4 down the column,
depending on the entries in column D.

Now you should highlight all the data and headings (including the
helper columns) from A1 to K200 and click on Data | Sort - in the
panel you should click Header Row if it is not already checked, and
select Supervisor as the first sort key, Action Type as the second
sort key, and Full Name as the third sort key. Click OK and you should
have what you want.

If you add any more data in the future you should remember to copy the
formulae in columns J and K to cover the extra data. I did mention
that you could hide the helper columns - click on the column
identifying letters to highlight columns J and K, then Format |
Columns | Hide.

Hope this helps.

Pete










- Show quoted text -
Click to expand...

Yay! This is working :)

Thanks for simplifying! Sometimes things just don't click! Numbers
are not my thing :-p

The only question I have... I need this to be accessible to 7
different supervisors to continue to add their data throughout time.
I have it set up so that it will automatically run the macro to sort
the data when the spreadsheet is opened...

But, is there a way to have it run without recognizing the "," in the
Last, First name section when there isn't a name entered yet? Or the
#N/A when the corrective action level has not been set yet? That way,
I can prefill the information on the spreadsheet and when they fill
in, it will automatically sort, leaving out all the empty rows that
haven't been filled yet :)

Is that possible?


Thank you SOOOO much for your help so far!! :) :)
 
Okay. Use columns J and K as helper columns. I'm assuming you have a
row of headers, so put "Full Name" in J1 and "Action Type" in K1.
Enter this formula in J2:

=B2&", "&C2

then copy this down the column for as many entries as you have - a
quick way of doing this is to select J2 and double-click the fill
handle, which is the small black square in the bottom right corner of
the cursor. This will give you the names joined together like "Doe,
John" down the column.

Enter the lookup formula I gave you earlier in K2 and copy that down -
that should give you values ranging from 1 to 4 down the column,
depending on the entries in column D.

Now you should highlight all the data and headings (including the
helper columns) from A1 to K200 and click on Data | Sort - in the
panel you should click Header Row if it is not already checked, and
select Supervisor as the first sort key, Action Type as the second
sort key, and Full Name as the third sort key. Click OK and you should
have what you want.

If you add any more data in the future you should remember to copy the
formulae in columns J and K to cover the extra data. I did mention
that you could hide the helper columns - click on the column
identifying letters to highlight columns J and K, then Format |
Columns | Hide.

Hope this helps.

Pete










- Show quoted text -
Click to expand...

Yay!!!!!!!!!!!! This is working!! I found a way to make it just be
blank if there is no data in the rows so that I can prefill all the
information so that the next 6 supervisors can come in whenever to add
data!


YOU ARE THE GREATEST!!! :)

Thank you so much for your help!! :) :)
 
Back
Top