Conditional formatting

[MitchS]

Lets see how I can explain this...I'm not good with XL formulas at all! I have a XL sheet I use for horse racing. Lots of numerical data on this sheet. I export the data from MS Access onto an XL sheet. A guy helped me with a formula that separates the races so I have a header on top of each race. Once the races are separated with headers on top I then copy and paste into a fresh XL sheet away form the sheet with the seperator code.

Here's what I want to do. I want to automatically highlight the top 3 numerical values in each race on various columns.

Now I know how to get the TOP RANKED highlighted IF the whole XL sheet was just ONE RACE...BUT my XL sheet gets separated into may 10 races AND I also want the top 3 highlighted not just the top one.

IF the sheet was one race and I Just wanted the top ranking I could use this...

"Equal to" =MAX($A$1:$A$200)


That would give me the top ranking on the whole sheet BUT....

The problem is that each sheet is separated in maybe 10 races so there is no EXACT constant with the column numbers as they change slightly with I seperate the races.


My question then is it possible to highlight the top 3 numerical values on a XL sheet where the column numbers are not constant as they change slightly when I separate the races for day to day use. The reason this is, is because there is no constant on the number of horses in each race.

Make sense? Is this possible?

Mitch

 

[MitchS]

Well, I'm using the mrExcelHtml.... Actually a pretty simple program, but its a pretty small sample only 13 across and 30 down. Not sure if i can explain what I'm trying to do in this snapshot... Anyway, I have a small glitch in trying to get the clipboard over to this forum? I see the code on the clipboard in my XL. How do i paste it over here? I mean there is a button on the clipboard that says paste all. If I hit that it pastes that code in my excel sheet on top of my sheet. If I double click that code the same thing happens. How do I get that code over here?

 

[Peter_SSs]

Well, I'm using the mrExcelHtml.... How do I get that code over here?

1. On your sheet, select the area you want for your snapshot and click the relevant 'Generate Html' option.

2. Move to your post, select the insertion point and Paste (Ctrl+V)

You will just see the HTML code at that point but when you click submit the code should render correctly as a sheet image. Here's a very small sample:

Excel Workbook
	A	B	C	D
17	TK	RN	Horse	PN
18	Ded	2	Caddo Native	3
19	Ded	2	Watch My Smoke	5
20	Ded	2	Man Of Strife	6
Cf within race

Excel 2010


Was the suggestion about the Conditional Formatting in my previous post any use?

 

[MitchS]

Thanks, I appreciate your help, I really do. Its just impossible to explain correctly what I'm trying to do without posting the complete sheet. I may need to go somewhere else..

My sheet goes 29 columns across and over 140 columns down. There are 10 races on the sheet going down separated by a header on top of each race which explains the column name.

As an example. I would want to highlight the top 3 highest numbers in EACH RACE SEPARATELY on column "SR-L9" as an example. EACH RACE IS A SEPARATE ENTITY.. If I could understand how to do that then I could apply that principle to other columns that I want to do the same thing to... I want to do that to about 15 of the columns as a visual.

Here the problem though. The column numbers going down are not constant! Every night I delete this sheet and redo for the next day. Depending on how many horses in each race determines the spacing on the races going down so again the numbers are not constant..

My question as in my first post is. Is it possible to apply a formula highlighting the top 3 numbers in a column to an XL sheet where the numbers going down are not constant from day to day?

Does anybody understand what I'm trying to say?

 

[Peter_SSs]

Does anybody understand what I'm trying to say?

I believe that I do, and I thought that I demonstrated that here.

You have not said in what way that fails your basic concept.

 

[MitchS]

Excel Workbook
	N	O	P
1	SR-L	SR-L9	SR-9A
2	87	82	82
3	86	80	79
4	83	74	81
5	81	70	70
6	61	44	44
7	46	18	18
8	79	0	76
9	67	0	29
10	68	0	58
11	87	0	72
12	0	0	0
13	81	0	74
DED1

Excel 2003



LOL, Now, I got it, lol...thanks, that's pretty neat!

 

[MitchS]

Excel Workbook
	N	O	P
1	SR-L	SR-L9	SR-9A
2	87	82	82
3	86	80	79
4	83	74	81
5	81	70	70
6	61	44	44
7	46	18	18
8	79	0	76
9	67	0	29
10	68	0	58
11	87	0	72
12	0	0	0
13	81	0	74
14
15
16	SR-L	SR-L9	SR-9A
17	108	104	101
18	102	96	107
19	97	94	105
20	101	0	102
21	80	0	103
22	98	0	94
23	98	0	105
24	96	0	115
DED1

Excel 2003

 

[MitchS]

Ok, now were getting somewhere. The above post shows two SEPARATE races. Generally there are usually 10 separate races on one XL sheet. The numbers on the left going down will change from day to day. With that in mind, is it possible to create a formula to highlight the top 3 HIGHEST numbers in column "SR-L9" in EACH RACE SEPARATELY... Keeping in mind that the numbers on the left going down will change a bit from day to day?

 

[Peter_SSs]

Still the same basic concept as my earlier post, with a few tweaks to change from SMALL to LARGE and to allow easy adaptation to multiple columns.

For this layout (note that I have hidden most of the columns, and changed you data a little)

1. Select N18:Pxx (or more columns too if you want)

2. Apply the Conditional Formatting shown. When entering the formula, change my reference to row 50 to something that is at least as far down as the bottom of your data is likely to be.

Excel Workbook
	A	B	C	N	O	P
17	TK	RN	Horse	SR-L	SR-L9	SR-9A
18	Ded	2	Caddo Native	87	82	82
19	Ded	2	Watch My Smoke	86	80	79
20	Ded	2	Man Of Strife	83	74	81
21	Ded	2	Z Native	81	70	70
22	Ded	2	Jump Jump	61	44	44
23	Ded	2	K One Four	46	18	18
24	Ded	2	Mardi Gras Man	79	0	76
25	Ded	2	Prince William B	67	0	29
26
27
28	TK	RN	Horse	SR-L	SR-L9	SR-9A
29	Ded	3	What A Tribute	108	104	101
30	Ded	3	Hit The Switch	102	96	107
31	Ded	3	Goheadmakemyday	97	94	105
32	Ded	3	Call Me Ruston	101	0	102
33	Ded	3	Toro Creek	80	0	103
34	Ded	3	Mr Big Stuff	98	0	94
35	Ded	3	Sal's Big Easy	98	0	105
36	Ded	3	Prime Time Gambler	96	0	115
37
Cf within race

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N18	1. / Formula is =AND(N18>=LARGE(IF($B$18:$B$50=$B18,N$18:N$50),3),N18<>"")	Abc

 

[Peter_SSs]

BTW, now you have the HTML Maker working, I have removed that excessively large table from post #8.

 

[MitchS]

Thank you Peter! This is great and I'm actually learning to code a little in XL, very nice thank you! The code you've shown is pretty easy to understand, I get it for the most part however I will need to make some tweaks to it. I have some questions?


With your code I'm seeing the "B" column as this lines up the race number so it starts the highest top 3 over again with each race. Great!

I also see the "N" column in your code and how it relates to the "B" column...BUT there is no mention of the "O" and "P" columns in your code but it works?


It just so happens that those 3 columns "N, O and P" are together but there are other columns that I want to highlight that are not together..

Here's an example of what I want do. Maybe you can point me in the right direction-


Numbers run from 2 to 150
Column "B" lines up the race number, same as in your example

In each grouping I may want to change the color so that the top 3 in each grouping are different colors.


Top 3 highest numbers-

Columns- "H", "K,L,M", "N,O,P", "U,V" , "Q", "X"

Above are 6 different groupings and each grouping with different colors

Top 3 Lowest numbers- (Lowest numbers are lowest with a (-) numerical rating and run highest with a (+) numerical rating. These columns need to return the lowest top 3.

Columns- "I", 'R", "Y", "AA,AB"

4 different groupings with each grouping a different color.


Any help getting starting in the right direction would be great!

I'm imagining that when I enter the code in that I'm going to the Conditional formatting tab. Use the "formula is" under condition 1. Condition 2 then would be a seperate grouping with my grouping examples with a different color etc etc... Is this correct?

Thanks again for any help getting me started in the right direction.

Mitch