Automatic sort and Highlight

[Celticshadow]

Hi

I have a set of figures in column D that I would like sorting automatically
from lowest to highest including those that are equal.When they are sorted is
it also possible to have the lowest six automatically highlighted as well (if
there are equals that would count as only one place in the rank)? The value
of these figures can be from 1 to 999. The adjacent columns either side of
the one I need sorting also need to move with the sorted column. There might
be 40 columns in the sheet in total (I think).


D
8
8
10
14
14
18
21
22
22
22
24
27
29
30
30
48
50
56
79
99


Kind Regards

Celticshadow

 

[Roger Govier]

Hi

Make sure that you select ALL of your data before sorting on column D,
otherwise you will have a disaster on your hands.

Select the range of data in column D>Format>Conditional Formatting>use
dropdown to select Formula Is>
=$D1<=SMALL($D$1:$D$100,6)
Format>choose whatever colour you wish>OK

 

[Celticshadow]

Hi Roger

That does work kind of but it does not count equal as just one place in the
ranking instead it counts them as part of the top six ie

8 1
8 2
10 3
14 4
14 5
18 6

I need to rank the two eights as 1 then ten as 2 the two fourteens as 3 etc.
It also highlights the whole row whereas I require just the column to be
highlighted. My apologies for not making the example as clear first time
ariund.

Kind Regards

Celticshadow

 

[Roger Govier]

Hi

It also highlights the whole row whereas I require just the column to be
highlighted.

I did say as far as the Conditional formatting part was concernedwhich would format only the cells in column D.

As far as you other point is concerned, I think you will need to use a
helper column.
It can be anywhere on the sheet, say column X.
in X1 enter
=IF(COUNTIF($D$11,D1)<2,D1,999)

Select your range of cells in column D, but change the CF formula to
=$X1<=SMALL($X$1:$X$100,6)

 

[Ken Johnson]

Hi Roger

That does work kind of but it does not count equal as just one place in the
ranking instead it counts them as part of the top six ie

8        1
8        2
10      3
14      4
14      5
18      6

I need to rank the two eights as 1 then ten as 2 the two fourteens as 3 etc.
It also highlights the whole row whereas I require just the column to be
highlighted. My apologies for not making the example as clear first time
ariund.

Kind Regards

Celticshadow

This CF formula seems to work...

=SUMPRODUCT(--(D1>D$1$20),1/COUNTIF(D$1$20,D$1$20&""))+1<=6

it highlights all values from 8 up 22 (10 values making up the top 6)

Ken Johnson

 

[ShaneDevenshire]

Hi,

It appears that the following formula in conditional formatting will work:

=SUM(1/COUNTIF(D$22,D$22))<7

Where D2 is the first cell with values and this is the formula for that
first cell's conditional formatting. This formula works as long as the
values are sorted ascending or descending but not if they are unsorted.

Regardng sorting - you do not need to select the entire range to sort if
your data is set up "correctly". The rule is that you must select either a
single cell or the entire data set or else you may scramble your data. The
rules for setting up your data to allow single cell sorting (you put your
cursor into a single cell of the column you want to sort on) are 1. One row
of titles, the titles may need to be formatted differently from the data if
the data type of all the titles are the same as the data under them, no data
that is not to be sorted should be directly adjacent to the sort range (there
should be a mote around the data), no entire row or column within the data
area can be entirely empty. (I think I've caught everything). If you obey
these rules you select a single cell in the column you want to sort on and
click the Ascending or Descending sort buttons.

 

[Roger Govier]

Hi Shane

Whilst I don't doubt that your rules about sorting are correct, unless the
user is using a List in Xl2003 or a table in XL2007 I have never felt
comfortable with Excel "guessing" correctly the total range to be sorted.
I have seen too many people get their data totally "stuffed up" in the past,
which is why I always recommend selecting the entire data set before
performing any sort.

Your and Ken's formulae for the CF are much more efficient than using a
extra columns as I suggested, although I believe Ken's needs to set to <=7
(to allow for the 1 being added in the formula) otherwise the OP will not
get his top 6

 

[Celticshadow]

Hi Roger

I am a little confused now, may I ask if you would be kind enough to run
this by me again. Forgive me if I seem a little slow on the uptake. Should
put the other contributors formula in the Formula is bar for conditional
formatting?

Kind Regards

Celticshadow

 

[Ken Johnson]

Hi Shane

Whilst I don't doubt that your rules about sorting are correct, unless the
user is using a List in Xl2003 or a table in XL2007 I have never felt
comfortable with Excel "guessing" correctly the total range to be sorted.
I have seen too many people get their data totally "stuffed up" in the past,
which is why I always recommend selecting the entire data set before
performing any sort.

Your and Ken's formulae for the CF are much more efficient than using a
extra columns as I suggested, although I believe Ken's needs to set to <=7
(to allow for the 1 being added in the formula) otherwise the OP will not
get his top 6

Hi Roger,

The +1 in the formula is to make the ranks start at 1 rather than 0 so
I think it's OK as is.

Ken Johnson

 

[Ken Johnson]

Hi Roger

I am a little confused now, may I ask if you would be kind enough to run
this by me again. Forgive me if I seem a little slow on the uptake. Should  
put the other contributors formula in the Formula is bar for conditional
formatting?

Kind Regards

Celticshadow

Hi Celticshadow,

I used the formula I posted in the FormulaIs box on the CF dialog
(after selecting D120) and the top 6 of the data you supplied were
highlighted. It also worked on your an unsorted version of your data.

Ken Johnson

 

[Roger Govier]

Hi Ken

I tested it with the series of numbers
5,8,12,12,12,12,20,23,45,46,47

With the formula using <=6, it highlighted down to number 23 i.e. the first
5 unique numbers
Substituting 6 with 7 it highlighted the first 6 unique numbers, i.e down to
number 45 - the same result as I got with Shane's formula

 

[Ken Johnson]

Hi Ken

I tested it with the series of numbers
5,8,12,12,12,12,20,23,45,46,47

With the formula using <=6, it highlighted down to number 23 i.e. the first
5 unique numbers
Substituting 6 with 7 it highlighted the first 6 unique numbers, i.e down to
number 45 - the same result as I got with Shane's formula

Hi Roger,

I've found my problem.

My formula will only return the correct rank when the ranges used in
the various arguments have the same number of rows as there are values
to rank...

D120 has 20 rows the array of values in your test has 11 rows.

This means rank 1 was taken by the 9 blank cells below your test
values.

So, I have changed to...

=SUMPRODUCT(--(D$1$20<>""),--(D1>D$1$20),1/COUNTIF(D$1$20,D$1
$20&""))+1<=6

Thanks for pointing out my error.

Ken Johnson

 

[Roger Govier]

Hi Ken

I should have thought it through myself. Of course, the blank cells would
mess it up.
Adding the extra term to Sumproduct sorts it nicely.

 

[Celticshadow]

Dear Roger, Ken and Shane

Many thanks for your replies. I can now confirm that I have sussed the
formula and can also report that it works magnificently, top draw help once
more.

Thanks again.

Celticshadow