Highlighting data.

[Moggsy85]

Hi, I am looking for a formula that will highlight the 3 lowest BSP (column C) from the specific date and time (column A&B), and then from those 3 find the lowest RBD rating (column D)?

MANY THANKS

Date	Date and Time	BSP	RBD Rating
01/01/2023​	12:10:00​	2.26​	165​
01/01/2023​	12:10:00​	5.24​	144​
01/01/2023​	12:10:00​	8.58​	13​
01/01/2023​	12:10:00​	9​	138​
01/01/2023​	12:10:00​	24.49​	143​
01/01/2023​	12:10:00​	34.39​	123​
01/01/2023​	12:10:00​	47.6​	132​
01/01/2023​	12:10:00​	75.12​	131​
01/01/2023​	12:10:00​	110​	116​
01/01/2023​	12:10:00​	132.35​	107​
01/01/2023​	12:10:00​	173.97​	7​
01/01/2023​	12:10:00​	200​	7​
01/01/2023​	12:10:00​	710​	108​
01/01/2023​	12:15:00​	2.29​	172​
01/01/2023​	12:15:00​	2.39​	156​
01/01/2023​	12:15:00​	12.83​	144​
01/01/2023​	12:15:00​	14.21​	141​
01/01/2023​	12:15:00​	81.07​	126​
01/01/2023​	12:20:00​	4.17​	134​
01/01/2023​	12:20:00​	4.63​	25​
01/01/2023​	12:20:00​	6.02​	136​
01/01/2023​	12:20:00​	6.76​	14​
01/01/2023​	12:20:00​	10.96​	140​
01/01/2023​	12:20:00​	11​	133​
01/01/2023​	12:20:00​	18.5​	128​
01/01/2023​	12:20:00​	164.86​	103​
01/01/2023​	12:25:00​	2.68​	151​
01/01/2023​	12:25:00​	3.87​	27​
01/01/2023​	12:25:00​	5.4​	121​
01/01/2023​	12:25:00​	9.71​	126​
01/01/2023​	12:25:00​	16.91​	7​
01/01/2023​	12:25:00​	26.72​	10​
01/01/2023​	12:25:00​	347.94​	7​
01/01/2023​	12:25:00​	549.45​	7​

 

[duggie33]

Hi Moggsy85,

I do not think there is not a formula that can highlight other cells. With Conditional Formatting it might be possible, but beyond my level of understanding.

Below are some formulas that return the three lowest for the chosen date and time. The results are conditionally formatted to highlight the lowest RBD Rating.

Book1.xlsx
	A	B	C	D	E	F	G
1	Date	Date and Time	BSP	RBD Rating		Return Criteria
2	1/1/2023	12:10:00	290.85	45		Date	Time
3	1/1/2023	12:10:00	535.65	84		1/1/2023	12:20:00
4	1/1/2023	12:10:00	56.92	197
5	1/1/2023	12:10:00	354.11	132
6	1/1/2023	12:10:00	666.62	64		BSP	RBD Rating
7	1/1/2023	12:10:00	426.21	74		16.31	3
8	1/1/2023	12:10:00	16.30	52		35.99	124
9	1/1/2023	12:10:00	243.00	1		54.56	87
10	1/1/2023	12:10:00	567.97	50
11	1/1/2023	12:10:00	272.56	111
12	1/1/2023	12:10:00	383.61	16
13	1/1/2023	12:10:00	431.78	90
14	1/1/2023	12:10:00	106.17	180
15	1/1/2023	12:15:00	217.51	158
16	1/1/2023	12:15:00	342.98	177
17	1/1/2023	12:15:00	410.21	9
18	1/1/2023	12:15:00	55.80	47
19	1/1/2023	12:15:00	306.74	9
20	1/1/2023	12:20:00	651.36	69
21	1/1/2023	12:20:00	35.99	124
22	1/1/2023	12:20:00	199.77	148
23	1/1/2023	12:20:00	181.35	101
24	1/1/2023	12:20:00	16.31	3
25	1/1/2023	12:20:00	179.09	92
26	1/1/2023	12:20:00	367.29	184
27	1/1/2023	12:20:00	54.56	87
28	1/1/2023	12:25:00	519.37	15
29	1/1/2023	12:25:00	215.04	88
30	1/1/2023	12:25:00	111.33	50
31	1/1/2023	12:25:00	449.86	80
32	1/1/2023	12:25:00	661.71	19
33	1/1/2023	12:25:00	11.92	99
34	1/1/2023	12:25:00	613.31	43
35	1/1/2023	12:25:00	467.20	73
Sheet7

Cell Formulas
Range	Formula
F7:F9	F7	=AGGREGATE(15,6,C$2:C$35/(($A$2:$A$35=$F$3)*($B$2:$B$35=$G$3)),ROW(A1))
G7:G9	G7	=INDEX($D$2:$D$35,MATCH($F7&$F$3&$G$3,$C$2:$C$35&$A$2:$A$35&$B$2:$B$35,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F7:G9	Expression	=$G7=SMALL($G$7:$G$9,1)	text	NO

Note that I replaced all your number values in the last two columns because copying and pasting made them text and I could not figure out how to convert them to numbers. Sounds trivial, but I couldn't figure it out. Tried changing format, trim, clean, value, multiplying, and combinations of those functions with no luck. That could be completely different post. In the future, you should post your data with XL2BB.

Hope that helps,

Doug

 

[Peter_SSs]

copying and pasting made them text and I could not figure out how to convert them to numbers.

Try a macro including .Replace What:=ChrW(8203), Replacement:="", LookAt:=xlPart

In the future, you should post your data with XL2BB.

This OP will not be able to do that. Refer to version information at the top of this page: XL2BB - Excel Range to BBCode

@Moggsy85
Is this what you are trying to do? (I don't have a Mac to test though)

23 09 11.xlsm
	A	B	C	D
1	Date	Time	BSP	RBD Rating
2	1/01/2023	12:10:00	2.26	165
3	1/01/2023	12:10:00	5.24	144
4	1/01/2023	12:10:00	8.58	13
5	1/01/2023	12:10:00	9	138
6	1/01/2023	12:10:00	24.49	143
7	1/01/2023	12:10:00	34.39	123
8	1/01/2023	12:10:00	47.6	132
9	1/01/2023	12:10:00	75.12	131
10	1/01/2023	12:10:00	110	116
11	1/01/2023	12:10:00	132.35	107
12	1/01/2023	12:10:00	173.97	7
13	1/01/2023	12:10:00	200	7
14	1/01/2023	12:10:00	710	108
15	1/01/2023	12:15:00	2.29	172
16	1/01/2023	12:15:00	2.39	156
17	1/01/2023	12:15:00	12.83	144
18	1/01/2023	12:15:00	14.21	141
19	1/01/2023	12:15:00	81.07	126
20	1/01/2023	12:20:00	4.17	134
21	1/01/2023	12:20:00	4.63	25
22	1/01/2023	12:20:00	6.02	136
23	1/01/2023	12:20:00	6.76	14
24	1/01/2023	12:20:00	10.96	140
25	1/01/2023	12:20:00	11	133
26	1/01/2023	12:20:00	18.5	128
27	1/01/2023	12:20:00	164.86	103
28	1/01/2023	12:25:00	2.68	151
29	1/01/2023	12:25:00	3.87	27
30	1/01/2023	12:25:00	5.4	121
31	1/01/2023	12:25:00	9.71	126
32	1/01/2023	12:25:00	16.91	7
33	1/01/2023	12:25:00	26.72	10
34	1/01/2023	12:25:00	347.94	7
35	1/01/2023	12:25:00	549.45	7
Moggsy85

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D35	Expression	=D2=MIN(IF(C$2:C$35<=SMALL(IF(A$2:A$35=A2,IF(B$2:B$35=B2,C$2:C$35)),3),IF(A$2:A$35=A2,IF(B$2:B$35=B2,D$2:D$35))))	text	NO
C2:C35	Expression	=C2<=SMALL(IF(A$2:A$35=A2,IF(B$2:B$35=B2,C$2:C$35)),3)	text	NO

 

[Moggsy85]

Thank you so so much

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.