Excel formula to find matches in two columns

[JENWPS]

I have 3 columns - Column A (Vendor), Column B(PO#), Column C(Product Line). I am trying to weed out PO's that meet certain criteria. As long as all of the DROP lines for a given PO have the same VENDOR it can be deleted. So if the VENDOR is the same for ALL DROP lines on a PO, I can disregard that PO all together regardless of the MISC lines. Is there a formula to show these matches? Below is an example with the desired outcome in column D.

COLUMN A	COLUMN B	COLUMN C	COLUMN D
VENDOR	PO#	PRODUCT LINE
CONTIXO	2401242	DROP	DISCARD
CONTIXO	2401242	DROP	DISCARD
CONTIXO	2401242	DROP	DISCARD
CONTIXO	2401242	DROP	DISCARD
MATTEL	2401242	MISC	DISCARD
KIDGAL	2401242	MISC	DISCARD
CONTIXO	2401246	DROP	KEEP
WONFOLD	2401246	DROP	KEEP
STANS	2401253	DROP	KEEP
ROYALG	2401253	DROP	KEEP
STANS	2401253	MISC	KEEP

 

[DanteAmor]

Try this:

Dante Amor
	A	B	C	D
1	VENDOR	PO#	PRODUCT LINE	DISCARD/KEEP
2	CONTIXO	2401242	DROP	DISCARD
3	CONTIXO	2401242	DROP	DISCARD
4	CONTIXO	2401242	DROP	DISCARD
5	CONTIXO	2401242	DROP	DISCARD
6	MATTEL	2401242	MISC	KEEP
7	KIDGAL	2401242	MISC	KEEP
8	CONTIXO	2401246	DROP	KEEP
9	WONFOLD	2401246	DROP	KEEP
10	STANS	2401253	DROP	KEEP
11	ROYALG	2401253	DROP	KEEP
12	STANS	2401253	MISC	KEEP
dam4

Cell Formulas
Range	Formula
D2:D12	D2	=IF(COUNTIFS(A:A,A2,B:B,B2,C:C,"DROP")=COUNTIFS(B:B,B2,C:C,"DROP"),"DISCARD","KEEP")

 

[JENWPS]

Almost - I need it to take every line of the PO into consideration. For example, for PO# 2401242 (lines 2-7), since 4 of the lines are drop and all of the vendors match for those drop lines, I need to discard the whole PO including the MISC lines so ideally lines 2-7 in column D would say Discard. That I can sort the whole sheet and delete all 'discard' lines.

 

[DanteAmor]

Try in cell D2:

=LET(a,A2:A12,b,B2:B12,c,C2:C12,IFERROR(IF(MATCH(COUNTIFS(b,b,c,"DROP"), COUNTIFS(a,UNIQUE(FILTER(a,b=b)),b,b,c,"DROP"),0),"DISCARD"),"KEEP"))

 

[bebo021999]

Older Excel version (older than 2019 and 360)

Book1
	A	B	C	D
1	VENDOR	PO#	PRODUCT LINE	DISCARD/KEEP
2	CONTIXO	2401242	DROP	discard
3	CONTIXO	2401242	DROP	discard
4	CONTIXO	2401242	DROP	discard
5	CONTIXO	2401242	DROP	discard
6	MATTEL	2401242	MISC	discard
7	KIDGAL	2401242	MISC	discard
8	CONTIXO	2401246	DROP	keep
9	WONFOLD	2401246	DROP	keep
10	STANS	2401253	DROP	keep
11	ROYALG	2401253	DROP	keep
12	STANS	2401253	MISC	keep
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=IF(SUMPRODUCT(IF($C$2:$C$12="DROP",IF($B$2:$B$12=B2,1/COUNTIFS($C$2:$C$12,"DROP",$B$2:$B$12,B2,$A$2:$A$12,$A$2:$A$12))))=1,"discard","keep")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JENWPS]

Try in cell D2:

=LET(a,A2:A12,b,B2:B12,c,C2:C12,IFERROR(IF(MATCH(COUNTIFS(b,b,c,"DROP"), COUNTIFS(a,UNIQUE(FILTER(a,b=b)),b,b,c,"DROP"),0),"DISCARD"),"KEEP"))

Is there a way to look at the entire column, not stop at line 12? This sheet has a different amount of lines each day.

 

[DanteAmor]

Is there a way to look at the entire column, not stop at line 12? This sheet has a different amount of lines each day.

12 is an example.

It is not recommended, in this type of formulas, to use the entire column, it could affect the performance of the sheet, but you can enter an estimated number, for example, if you use 2,000 rows, you can enter 5000.

=LET(a,A2:A5000,b,B2:B5000,c,C2:C5000,IFERROR(IF(MATCH(COUNTIFS(b,b,c,"DROP"), COUNTIFS(a,UNIQUE(FILTER(a,b=b)),b,b,c,"DROP"),0),"DISCARD"),"KEEP"))

 

[JENWPS]

12 is an example.

It is not recommended, in this type of formulas, to use the entire column, it could affect the performance of the sheet, but you can enter an estimated number, for example, if you use 2,000 rows, you can enter 5000.

=LET(a,A2:A5000,b,B2:B5000,c,C2:C5000,IFERROR(IF(MATCH(COUNTIFS(b,b,c,"DROP"), COUNTIFS(a,UNIQUE(FILTER(a,b=b)),b,b,c,"DROP"),0),"DISCARD"),"KEEP"))

Perfect! Thank you so much for your help!!

 

[smozgur]

Perfect! Thank you so much for your help!!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.