Sorting 4 columns cohesively?

[zb61]

Is there a code or some way for me to sort 4 columns together? I want to sort a list of employees and for each employee theres a column with their Lost Business, customer satisfaction, and two more columns. I want to sort all the columns at the same time so that the best employees overall will go to the top of the list and the worst ones will be at the bottom. Whenever I use the sorting feature it does each column independant of the others so everytime I sort a new column it just moves around the last one I sorted. Any solution?

[shg]

Select all four columns, do Data > Sort, > Options, and make sure Sort top to bottom is checked.

[zb61]

I've tried that, but it seems to not put all the best values for each column at the top. It sorts the first column then sorts the second column which just cancels out the sorting done in the first column, and then the third one cancels out the first two columns and so on.

[shg]

You can specify three sort criteria to versions of Excel up to 2003. Sort from the most relevant parameter to the least.

The secondary and tertiary critera don't cancel the higher-level criteria; they sub-sort within equal values.

[zb61]

You can specify three sort criteria to versions of Excel up to 2003. Sort from the most relevant parameter to the least.

The secondary and tertiary critera don't cancel the higher-level criteria; they sub-sort within equal values.

what if all parameters are equally important?

[jasoncw]

It sounds like you might want to assign a value to each of the 4 criteria, and add these up in a new column, then sort on the new column.

HTH

Jason

[shg]

what if all parameters are equally important?

Then sort on the average, or some weighted sum, of the criteria.

[zb61]

I think I'm going to have to make separate sheets for each of the columns and sort each one, then use that cut and paste the top people into the right spots. Whats happening now is a guy might have the best scores in 3 of the columns, but then in that fourth column hes got a really bad score, and its still putting him at the top, but in reality the guy with pretty good scores in all four columns should be at the top.

[jasoncw]

Again, as was stated in both responses above, this can be done on the same sheet. Create a new column, and assign weight factors to all 4 criteria (or just add all 4 together). Then sort based on the new column.

Jason

[zb61]

Again, as was stated in both responses above, this can be done on the same sheet. Create a new column, and assign weight factors to all 4 criteria (or just add all 4 together). Then sort based on the new column.

Jason

Here's the data I'm trying to sort. I don't know how to weight it. Maybe you could look at this data and give me some examples for how to set weights.

[dlloyd]

What do the columns mean? Without knowing what LB, Del Ren, CSI and M Cost % is, and what importance each has, weighting them is impossible.

[ChemistB]

For starters, in Cell H2 (and dragging downward) you can put the formula:

Please Login or Register  to view this content.

What this does is sum the rank of the value in column D with other values in column D, and the rank of the value in column E with the other values in column E, etc. This assumes each column is equally important. If you feel the value in column E is twice as important as any of the other columns, multiply that RANK by 2 [...+2*RANK(E2,$E$2...)...].

Some things to know..
This is ranking each value from highest to lowest (e.g. 100% would be ranked higher than 98% and -2.3 would be ranked lower than 12.4). If you want a column ranked in the opposite order modify the function like so

Please Login or Register  to view this content.

Excel will give ties the same value and then will skip down to the number that would be next if there weren't ties. For example if the numbers are 100, 95, 100, 100, the ranks would be respectively 1, 4, 1, 1.

ChemistB

[skimpw]

the magic of a pivot table is what is needed.

[zb61]

the magic of a pivot table is what is needed.

I don't even know what a pivot table is.

[zb61]

For starters, in Cell H2 (and dragging downward) you can put the formula:

Please Login or Register  to view this content.

What this does is sum the rank of the value in column D with other values in column D, and the rank of the value in column E with the other values in column E, etc. This assumes each column is equally important. If you feel the value in column E is twice as important as any of the other columns, multiply that RANK by 2 [...+2*RANK(E2,$E$2...)...].

Some things to know..
This is ranking each value from highest to lowest (e.g. 100% would be ranked higher than 98% and -2.3 would be ranked lower than 12.4). If you want a column ranked in the opposite order modify the function like so

Please Login or Register  to view this content.

Excel will give ties the same value and then will skip down to the number that would be next if there weren't ties. For example if the numbers are 100, 95, 100, 100, the ranks would be respectively 1, 4, 1, 1.

ChemistB

ChemistB: When you drag the formula down to fill, only the first cells in each rank before the first comma are supposed to change right? I.E. =RANK(D2,$D$2:$D$24)+RANK(E2,$E$2:$E$24)+RANK(F2,$F$2:$F$24)+RANK(G2,$G$2:$G$24)

And how do you even come up with something like this in the first place?

[ChemistB]

And how do you even come up with something like this in the first place?

LOL, I spend too much time on this board reading what the geniuses here are saying.

ChemistB

[zb61]

I'm having a bit trouble with the ranking, chem. I tried to do:

=4*RANK(D2,$D$2:$D$24)+2*RANK(E2,$E$2:$E$24)+3*RANK(F2,$F$2:$F$24)+RANK(G2,$G$2:$G$24)


Because the first column is the most important, the 3rd column is second important and the 2nd column is third important with the 4th column being last. But when I sort by the rankings there are guys that have at least one column in the red but are ranked ahead of someone with all 4 columns in green, which shouldn't happen because red colored cells are for scores that are really bad.

[ChemistB]

Okay, I will take a look at this today.

ChemistB

[ChemistB]

Sorry I didn't get to this yesterday.

The trick is that in some of your columns (D,E and G) low is good. In F, low is bad (100% is best). I chose to make it such that the lowest sum (with your weighing) is the best score (like golf). For columns D, E and G, the RANK formula needs to be modified with a third argument (any positive integer, I choose 1) like so =RANK(D2,$D$2:$D$24,1). This ranks in accending order. For Column F, it's =RANK(F2,$F$2:$F$24). In the example, to be clear, I set up each rank separately in columns H through K, Summed them with your weighting in L and Ranked that final Rank in M. You could just sort on column L to get the order. To combine all those formulas (H-L), it would be;

Please Login or Register  to view this content.

I added that formula in column N to compare it to L and they match.

Hope I didn't confuse you.

ChemistB

[zb61]

Chem, when I drag that formula down through the rest of the column, on the first D# is supposed to change right? not the rest in the middle and last section.

[ChemistB]

Right, the first number after each rank (D2, E2, F2, G2)

ChemistB

[zb61]

Chem, I'm still having some trouble with this formula. I've attached some example data sorted in the desired result, but if you apply the formula rankings to it and then sort it based off that, it won't end up sorted the same way. Basically anyone with all for columns green should be at the top, green and white below that and then the reds start falling in. I'm thinking maybe some type of if statements to get the reds below the greens. I'm just not sure how to go about it.

[ChemistB]

Hmmm, I can't open your zip file. Can you try again?

I'll do a little review on my own today to see how to improve it.

ChemistB

[jovialtom]

Based on the formula it is entirely possible that someone with fewer red colours can out rank someone with more green colours, but i don't think that this is an issue as counting the number of red/green colours is a rather arbitrary measure compared with the formulaic scientific approach we have used.

Nancy for example has one red colour, but because this is outweighed by her excellent performance in the first category she finishes higher than tom and jerry (!) who only perform averagely in the most important category.

Gary has only one red colour, but this is in the most important first category and attracts a greater penalty in terms of final ranking.

I have attached a spreadsheet to indicate how i expect the final ranking to look (it seems to agree with chems solution). You are able to alter the importance of each factor using row 2 parameters.

Regards,
Tom

[zb61]

Sorry I initially saved it as a rar and I guess I just renamed it to a zip somehow instead of doing it right.

Yea I guess as far as the formula is concerned its working as intended, but people that are green in all four areas have to be above those with a single red because that employee is the best all round not just in the one category. I can put them in the right order by copying and pasting them, but that just seems inefficient.