Conditional Formatting with multiple criteria

[Katherine1]

The document I have is several thousand rows long and by about 130 columns. I need to determine the latest load date for each Item number.

Based on the item # in Column B, I would like to highlight the current Item #, (Column C; Y = Yes) with the latest Load date in Column D. In several cases, an item # may have multiple load dates that appear to be current. Items have varying numbers of rows so multiple rows will need to be highlighted for each item.

Thank you in advance for your help!

Katherine

 

[JGordon11]

Date and time field.xlsx
	A	B	C	D
1	Submission Order	Item Number	Current Y/N	Load Date
2	1	1	Y	9/30/2021
3	2	1	Y	10/2/2021
4	3	4	Y	10/2/2021
5	4	5	Y	10/2/2021
6	5	3	N	10/1/2021
7	6	3	N	10/2/2021
8	7	4	N	10/4/2021
9	8	2	N	10/4/2021
10	9	3	Y	9/30/2021
11	10	3	Y	10/1/2021
12	11	5	N	10/3/2021
13	12	2	Y	10/1/2021
14	13	3	Y	10/1/2021
15	14	2	N	10/3/2021
16	15	4	N	10/2/2021
17	16	5	Y	9/30/2021
18	17	2	N	10/2/2021
19	18	3	N	10/4/2021
20	19	5	Y	10/2/2021
21	20	4	N	10/1/2021
22	21	4	Y	10/4/2021
23	22	2	Y	9/30/2021
24	23	3	Y	10/2/2021
25	24	1	N	10/1/2021
26	25	2	N	10/1/2021
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D26	Expression	=AND($D2=MAX(($B$2:$B$26=$B2)*($C$2:$C$26="Y")*($D$2:$D$26)), $C2="Y")	text	NO

 

[cmowla]

This maybe?

Blank - Copy.xlsx
	A	B	C	D
1	Data example
2	Submission Order	Item Number	Current Y/N	Load Date
3	1	MMS3	Y	9/28/21 15:32
4	1	MMS3	Y	9/28/21 15:32
5	1	MMS3	Y	9/28/21 15:32
6	1	MMS3	Y	9/28/21 15:32
7	1	MMS3	Y	9/28/21 15:32
8	2	P653	Y	9/28/21 16:01
9	2	P653	Y	9/28/21 16:01
10	2	P653	Y	9/28/21 16:01
11	2	P653	Y	9/28/21 16:01
12	2	P653	N	9/28/21 16:01
13	2	P653	N	9/28/21 16:01
14	2	P653	N	9/28/21 16:01
15	3	P656	Y	9/28/21 16:01
16	3	P656	Y	9/28/21 16:01
17	3	P656	Y	9/28/21 16:01
18	3	P656	Y	9/28/21 16:01
19	3	P656	Y	9/28/21 16:01
20	3	P656	Y	9/28/21 16:01
21	4	MMS3	Y	10/1/21 14:37
22	4	MMS3	Y	10/1/21 14:37
23	4	MMS3	Y	10/1/21 14:37
24	4	MMS3	Y	10/1/21 14:37
25	4	MMS3	Y	10/1/21 14:37
26	5	P653	N	9/28/21 16:21
27	5	P653	N	9/28/21 16:21
28	5	P653	N	9/28/21 16:21
29	5	P653	N	9/28/21 16:21
30	6	P653	Y	10/1/21 14:37
31	6	P653	Y	10/1/21 14:37
32	6	P653	Y	10/1/21 14:37
33	6	P653	Y	10/1/21 14:37
34	7	P656	Y	10/1/21 14:37
35	7	P656	Y	10/1/21 14:37
36	7	P656	Y	10/1/21 14:37
37	7	P656	Y	10/1/21 14:37
Sheet1 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:D37	Expression	=AND($D3=MAX($D$3:$D$37),$C3="Y")	text	NO

 

[cmowla]

This maybe?

Nope! Sorry about that, @JGordon11 . (I guess it should have been bluntly obvious for me . . . I didn't even have column B in my formula.) Mine happened to work on the given data, but failed as soon as I intentionally broken it:

Blank - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Data example						Expected results
2	Submission Order	Item Number	Current Y/N	Load Date			Submission Order	Item Number	Current Y/N	Load Date
3	1	MMS3	Y	9/28/21 15:32			1	MMS3	Y	9/28/21 15:32
4	1	MMS3	Y	9/28/21 15:32			1	MMS3	Y	9/28/21 15:32
5	1	MMS3	Y	9/28/21 15:32			1	MMS3	Y	9/28/21 15:32
6	1	MMS3	Y	9/28/21 15:32			1	MMS3	Y	9/28/21 15:32
7	1	MMS3	Y	9/28/21 15:32			1	MMS3	Y	9/28/21 15:32
8	2	P653	Y	9/28/21 16:01			2	P653	Y	9/28/21 16:01
9	2	P653	Y	9/28/21 16:01			2	P653	Y	9/28/21 16:01
10	2	P653	Y	9/28/21 16:01			2	P653	Y	9/28/21 16:01
11	2	P653	Y	9/28/21 16:01			2	P653	Y	9/28/21 16:01
12	2	P653	N	9/28/21 16:01			2	P653	N	9/28/21 16:01
13	2	P653	N	9/28/21 16:01			2	P653	N	9/28/21 16:01
14	2	P653	N	9/28/21 16:01			2	P653	N	9/28/21 16:01
15	3	P65633	Y	9/28/21 16:01			3	P656	Y	9/28/21 16:01
16	3	P656	Y	9/28/21 16:01			3	P656	Y	9/28/21 16:01
17	3	P656	Y	9/28/21 16:01			3	P656	Y	9/28/21 16:01
18	3	P656	Y	9/28/21 16:01			3	P656	Y	9/28/21 16:01
19	3	P656	Y	9/28/21 16:01			3	P656	Y	9/28/21 16:01
20	3	P656	Y	9/28/21 16:01			3	P656	Y	9/28/21 16:01
21	4	MMS3	Y	10/1/21 14:37			4	MMS3	Y	10/1/21 14:37
22	4	MMS3	Y	10/1/21 14:37			4	MMS3	Y	10/1/21 14:37
23	4	MMS3	Y	10/1/21 14:37			4	MMS3	Y	10/1/21 14:37
24	4	MMS3	Y	10/1/21 14:37			4	MMS3	Y	10/1/21 14:37
25	4	MMS3	Y	10/1/21 14:37			4	MMS3	Y	10/1/21 14:37
26	5	P653	N	9/28/21 16:21			5	P653	N	9/28/21 16:21
27	5	P653	N	9/28/21 16:21			5	P653	N	9/28/21 16:21
28	5	P653	N	9/28/21 16:21			5	P653	N	9/28/21 16:21
29	5	P653	N	9/28/21 16:21			5	P653	N	9/28/21 16:21
30	6	P653	Y	10/1/21 14:37			6	P653	Y	10/1/21 14:37
31	6	P653	Y	10/1/21 14:37			6	P653	Y	10/1/21 14:37
32	6	P653	Y	10/1/21 14:37			6	P653	Y	10/1/21 14:37
33	6	P653	Y	10/1/21 14:37			6	P653	Y	10/1/21 14:37
34	7	P656	Y	10/1/21 14:37			7	P656	Y	10/1/21 14:37
35	7	P656	Y	10/1/21 14:37			7	P656	Y	10/1/21 14:37
36	7	P656	Y	10/1/21 14:37			7	P656	Y	10/1/21 14:37
37	7	P656	Y	10/1/21 14:37			7	P656	Y	10/1/21 14:37
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:J37	Expression	=AND($D3=MAX(($B$3:$B$37=$B3)*($C$3:$C$37="Y")*($D$3:$D$37)), $C3="Y")	text	NO
A3:D37	Expression	=AND($D3=MAX($D$3:$D$37),$C3="Y")	text	NO

How does that formula work? Can you break it down for us?

 

[JGordon11]

Inside the max function I am multiplying three arrays: 1) True or false (1 or 0) do the cells in column B equal the cell in Column B current row, 2) True or false (1 or 0) do the cells in column C equal "Y", and 3) the date values in Column D. This returns an array of zeroes and date values matching the criteria. Then Max returns a single value, the latest date in the array. $D3=Max returns true if the date in the current row matches the max. Then we also want to exclude rows where the C column is not "Y" (because there could be some rows with N in col C but the date equals the max, so use the And function. That then returns true only for rows meeting the criteria. It is important where the $ signs are applied to lock the column but not the row of the single cell references.

 

[Katherine1]

@JGordon11 - Thank you for the formula and explanation. This formula does works just like I asked. My only issue is that with 231,555 rows my Excel takes quite a bit of time to complete. I was able to divide the rows and do this in 3 stages to help with the timing.

Thanks again!

 

[JGordon11]

Conditional formatting 230k+ rows is probably not the best solution.

Another option would be to use Power Query to create a table of just the item numbers with Current = Y and the latest load date. You would need to have your data in the form of a formal excel table and could use M code similar to this (which assumes your table is called Table1):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Submission Order", Int64.Type}, {"Item Number", type text}, {"Current Y/N", type text}, {"Load Date", type datetime}}),
    FilterRows = Table.SelectRows(ChangeType, each ([#"Current Y/N"] = "Y")),
    GroupRows = Table.Group(FilterRows, {"Item Number"}, {{"Load Date", each List.Max([Load Date]), type nullable datetime}, {"Details", each _, type table }}),
    AddCustom = Table.AddColumn(GroupRows, "Submission Order", each Table.SelectRows(_[Details], (x) =>  x[Load Date] =_[Load Date])[Submission Order]{0}),
    ReorderColumns = Table.RemoveColumns(Table.ReorderColumns(AddCustom,{"Submission Order", "Item Number", "Load Date"}),{"Details"})
in
    ReorderColumns

If you load the resulting table to the workbook (preferably to its own sheet) then you can right click anywhere in the table and click refresh when the data changes. It won't be a computational performance drag except when it refreshes. This could be made to be dynamic with an event driven VBA macro but I don't recommend it.

Yet another option would be a VBA macro to either build the same table as the Power Query approach did, or to highlight the existing table. Both of these VBA approaches can be written to run very fast. Like the PQ approach they would be manually refreshed when the data changes, but could be made to be dynamic if needed (I would not recommend making it dynamic unless there's a compelling reason to).