sorting 6 columns in XL 2002

  • Thread starter Thread starter Carla
  • Start date Start date
C

Carla

I need to sort five columns with values 1-3 and the last
contains text. It's to rank people for a interview
(1=yes, 2=maybe and 3=no). Example:

column1 column2 column3 column4 column5 column6

1 1 2 1 2 applicant1
3 3 3 2 3 applicant2
1 1 1 1 1 applicant3
2 3 2 2 2 applicant4
3 2 2 2 2 applicant5

When I sort (my real table has 43 rows)it should look like:

column1 column2 column3 column4 column5 column6

1 1 1 1 1 applicant3
1 1 2 1 2 applicant1
2 2 2 2 2 applicant5
2 3 2 2 2 applicant4
3 3 3 2 3 applicant2

This means applicant3 and 1 would be interviewed. Thanks
for any help!
 
With your data in Cols A:F, use a helper Column (say G), and then assuming your data starts in Row
2, in G2 put the following formula:-

=--(A2&B2&C2&D2&E2) and copy down.

Now select all the data, including that column, and sort on Col G in ascending order.

You could do it with just

=A2&B2&C2&D2&E2

but it just saves you having to go through the warning messages about text looking like numbers.
 
I am assuming though, that your columns are weighted in the order they appear, only doing it this
way would have 11112 rank above 21111. If this is not the case, and those two responses would be
equal, then don't use my solution. In that case and assuming all cells are of equal weighting,
then simply sum the columns A:E in the helper column G, eg =SUM(A2:E2), and then sort on that
column in ascending order.
 
In fact, you could use two helper columns. First as previously stated, and then use the RANK
function in the second helper column, eg:-

=RANK(G1,$G$1:$G$1000,1)

Then select all the data and sort on the RANK column. This will rank 11112 and 21111 together
equally.
 
I am assuming though, that your columns are weighted in the order
they appear, only doing it this way would have 11112 rank above 21111.
If this is not the case, and those two responses would be equal, then
don't use my solution. In that case and assuming all cells are of
equal weighting, then simply sum the columns A:E in the helper column
G, eg =SUM(A2:E2), and then sort on that column in ascending order.
Click to expand...

Summing may be just as problematic. How should 11233 compare to 22222?
 
Back
Top