Expanding Conditional Formatting down multiple rows

[Walkon]

Good evening,

I sort application data. A "complete" application has three components - one application document, and two references. I aggregate the data from the two sources (applications and references) and sort by the Last Name of the applicant (ALast). I then use a formula to determine a formula to determine if all three of the components for a particular application is present. [ =IF(AND(A2=A3,A3=A4,A1<>A2),"Complete","") ]. I then use Conditional Formatting to colour the rows that are "Complete". See "Completes - MrExcel.jpg image below.

As I need to be able to pull all three rows of each "Complete" Application from the column of data. I was thinking that if all three of the rows of each "Complete" application could have the "Complete" label in the "Complete?" column, (F in the image below) then I could sort the data based on either cell colour or by column F. See "Desired result - MrExcel.jpg image below.

I have been unable to develop a formula that will identify each row in a "complete" grouping.

I would be very grateful for ideas.

Thanks,

 

[Peter_SSs]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
You will generally get faster/better responses
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Is this what you are after?

25 02 09.xlsm
	A	B	C	D	E	F
1	ID	Alast				Complete?
2	1	A				Complete
3	1	A
4	1	A
5	2	B
6	3	C
7	3	C
8	4	D
9	4	D
10	5	E				Complete
11	5	E
12	5	E
13	6	F				Complete
14	6	F
15	6	F
Complete

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F15	Expression	=INDEX($F$1:$F2,MATCH($A2&"|"&$B2,$A$1:$A2&"|"&$B$1:$B2,0))="Complete"	text	NO

Or another option

25 02 09.xlsm
	A	B	C	D	E	F
1	ID	Alast				Complete?
2	1	A				Complete
3	1	A
4	1	A
5	2	B
6	3	C
7	3	C
8	4	D
9	4	D
10	5	E				Complete
11	5	E
12	5	E
13	6	F				Complete
14	6	F
15	6	F
Complete (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F15	Expression	=TAKE(FILTER($F$2:$F2,($A$2:$A2=$A2)*($B$2:$B2=$B2)),1)="Complete"	text	NO

 

[Cubist]

Another option:

Book3
	A	B	C	D	E	F
1	ID	Alast				Complete?
2	1	A				Complete
3	1	A
4	1	A
5	2	B
6	3	C
7	3	C
8	4	D
9	4	D
10	5	E				Complete
11	5	E
12	5	E
13	6	F				Complete
14	6	F
15	6	F
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F15	Expression	=COUNTIFS($B:$B,$B2,$F:$F,"Complete")	text	NO

 

[Peter_SSs]

Another option:

I think a check of column A would also be needed as I imagine a person could have more than one application & I think column A is the application ID not the person's ID (& the OP's formula check in column F includes a check on column A being the same so I assume it could be different)
Also, the check only need to be upwards so if the data was very large it may be best (it certainly shouldn't hurt) to restrict the check that way.

25 02 09.xlsm
	A	B	C	D	E	F
1	ID	Alast				Complete?
2	1	A				Complete
3	1	A
4	1	A
5	2	A				Complete
6	2	A
7	2	A
8	3	A
9	4	D
10	4	D
11	5	E				Complete
12	5	E
13	5	E
14	6	F				Complete
15	6	F
16	6	F
Complete (3)

Cell Formulas
Range	Formula
F2:F16	F2	=IF(AND(A2=A3,A3=A4,A1<>A2),"Complete","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F16	Expression	=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$F$2:$F2,"Complete")	text	NO

.. or maybe all that means the check only needs to be on cols a & F and not B at all?

25 02 09.xlsm
	A	B	C	D	E	F
1	ID	Alast				Complete?
2	1	A				Complete
3	1	A
4	1	A
5	2	A				Complete
6	2	A
7	2	A
8	3	A
9	4	D
10	4	D
11	5	E				Complete
12	5	E
13	5	E
14	6	F				Complete
15	6	F
16	6	F
Complete (4)

Cell Formulas
Range	Formula
F2:F16	F2	=IF(AND(A2=A3,A3=A4,A1<>A2),"Complete","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F16	Expression	=COUNTIFS($A$2:$A2,$A2,$F$2:$F2,"Complete")	text	NO

 

[Walkon]

I think a check of column A would also be needed as I imagine a person could have more than one application & I think column A is the application ID not the person's ID (& the OP's formula check in column F includes a check on column A being the same so I assume it could be different)
Also, the check only need to be upwards so if the data was very large it may be best (it certainly shouldn't hurt) to restrict the check that way.

25 02 09.xlsm
	A	B	C	D	E	F
1	ID	Alast				Complete?
2	1	A				Complete
3	1	A
4	1	A
5	2	A				Complete
6	2	A
7	2	A
8	3	A
9	4	D
10	4	D
11	5	E				Complete
12	5	E
13	5	E
14	6	F				Complete
15	6	F
16	6	F
Complete (3)

Cell Formulas
Range	Formula
F2:F16	F2	=IF(AND(A2=A3,A3=A4,A1<>A2),"Complete","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F16	Expression	=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$F$2:$F2,"Complete")	text	NO

.. or maybe all that means the check only needs to be on cols a & F and not B at all?

25 02 09.xlsm
	A	B	C	D	E	F
1	ID	Alast				Complete?
2	1	A				Complete
3	1	A
4	1	A
5	2	A				Complete
6	2	A
7	2	A
8	3	A
9	4	D
10	4	D
11	5	E				Complete
12	5	E
13	5	E
14	6	F				Complete
15	6	F
16	6	F
Complete (4)

Cell Formulas
Range	Formula
F2:F16	F2	=IF(AND(A2=A3,A3=A4,A1<>A2),"Complete","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F16	Expression	=COUNTIFS($A$2:$A2,$A2,$F$2:$F2,"Complete")	text	NO

Good morning,
Thanks very much for the solutions and the info regarding XL2BB. My apologies for making things more difficult than needed. I have donwloaded XL2BB and will use it in future asks.

Thnaks again,

BEst,

Rob

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.