Pairing cells according multiple criteria.

[sergiopcrt]

Hi Everyone,

I have the following dataset were all ID's are duplicated but I want to flag all the Pairs that have a "NO_MATCH" in the field ANALYSIS_1 so I can filter them out and work on them.

A1	ID	ANALYSIS_1	ANALYSIS_2
A2	2064031	1_MATCH	FALSE
A3	2064031	1_MATCH	FALSE
A4	2064050	1_MATCH	FALSE
A5	2064050	1_MATCH	FALSE
A6	2064051	1_MATCH	FALSE
A7	2064051	1_MATCH	FALSE
A8	2064075	1_MATCH	FALSE
A9	2064075	1_MATCH	FALSE
A10	2064101	1_MATCH	PAIR
A11	2064101	NO_MATCH	PAIR
A12	2064106	1_MATCH	PAIR
A13	2064106	1_MATCH	FALSE

For the NO_MATCH found on C11 I'd like to have row 11 and row 10 marked as "PAIR" and try comparing each cell to the one immediately above and below, but I have 1 incorrect flag.

=IF(AND(OR(C11="NO_MATCH"C10="NO_MATCH",C12="NO_MATCH"),OR(B11=B10,B11=B12)),"PAIR")

Also, is it possible with IDs replicated 3 and 4 times?

can you guys help me? This doesn't seem to complicated, but somehow I can't get my head around it.

Thanks in advance!
Sergio

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D
1	A1	ID	ANALYSIS_1	ANALYSIS_2
2	A2	2064031	1_MATCH
3	A3	2064031	1_MATCH
4	A4	2064050	1_MATCH
5	A5	2064050	1_MATCH
6	A6	2064051	1_MATCH	Pair
7	A7	2064051	1_MATCH	Pair
8	A8	2064051	NO_MATCH	Pair
9	A9	2064075	1_MATCH
10	A10	2064101	1_MATCH	Pair
11	A11	2064101	NO_MATCH	Pair
12	A12	2064106	1_MATCH
13	A13	2064106	1_MATCH
Master

Cell Formulas
Range	Formula
D2:D13	D2	=IF(COUNTIFS(B:B,B2,C:C,"NO_MATCH")>0,"Pair","")

 

[sergiopcrt]

Cell FormulasRangeFormulaD2:D13D2=IF(COUNTIFS(B:B,B2,C:C,"NO_MATCH")>0,"Pair","")

omg, It's so simple, it's poetry! thank you so much @Fluff this was exactly what I needed. I hadn't considered the countifs route!

 

[Fluff]

You're welcome & thanks for the feedback.