Matching based on rows

[dhegs]

Hi everyone,

I am looking to create a formula (I am assuming using Index & Match & IF??) that will provide me with a text output (for later use in Pivot Tables) to indicate this data should be removed.

As you can see in the picture, I've already got a column (column S) that refers to many different criteria (in hidden columns) to indicate this data should not be included. As you can see in column B, I have multiple data for each client and column T indicates the "first" and "last" row of this data.

What I am wanting, is to have a formula in column U that will indicate next to "first" and "last" for that client if it should be removed. For example for client 5502 the "first" data indicates it should be removed - I then want it to indicate in column U next to both "first" AND "last" that it should be removed. This will allow me to remove first and last data for this client in the Pivot Table as I can filter for the remove text in column U.

I am hoping someone might be able to help?!

 

[mrshl9898]

Like this?

=IF(OR(COUNTIF(B$2:B2,B2)=COUNTIF(B:B,B2),COUNTIF(B$2:B2,B2)=1),"REMOVE","")

 

[Habtest]

Hi, try this:

Book1
	B																	S	T	U
1
2	5502																	REMOVE	first	REMOVE
3	5502
4	5502
5	5502
6	5502
7	5502
8	5502
9	5502
10	5502																	REMOVE	last	REMOVE
11	5530																		first
12	5530
13	5530																		last
14	1000																		first
15	1000																		last
16	2000																	REMOVE	first	REMOVE
17	2000
18	2000
19	2000
20	2000																		last	REMOVE
21	3000																		first
22	3000
23	3000
24	3000
25	3000																		last
Sheet1

Cell Formulas
Range	Formula
U2:U25	U2	=IF(ISBLANK(T2),"",IF(ISBLANK(VLOOKUP(B2,B:S,18,0)),"",VLOOKUP(B2,B:S,18,0)))

 

[dhegs]

Like this?

=IF(OR(COUNTIF(B$2:B2,B2)=COUNTIF(B:B,B2),COUNTIF(B$2:B2,B2)=1),"REMOVE","")

Thanks so much for your response, but no this doesn't work. This results in rows below (e.g. for client 556 in rows 13 & 14) saying that they should be removed.

 

[mrshl9898]

So remove first and last UNLESS this removes all?

 

[mrshl9898]

=IF(AND(COUNTIF(B:B,B2)=2,COUNTIF(B$2:B2,B2)=1),"",IF(OR(COUNTIF(B$2:B2,B2)=COUNTIF(B:B,B2),COUNTIF(B$2:B2,B2)=1),"REMOVE",""))

 

[dhegs]

Hi, try this:

Book1
	B																	S	T	U
1
2	5502																	REMOVE	first	REMOVE
3	5502
4	5502
5	5502
6	5502
7	5502
8	5502
9	5502
10	5502																	REMOVE	last	REMOVE
11	5530																		first
12	5530
13	5530																		last
14	1000																		first
15	1000																		last
16	2000																	REMOVE	first	REMOVE
17	2000
18	2000
19	2000
20	2000																		last	REMOVE
21	3000																		first
22	3000
23	3000
24	3000
25	3000																		last
Sheet1

Cell Formulas
Range	Formula
U2:U25	U2	=IF(ISBLANK(T2),"",IF(ISBLANK(VLOOKUP(B2,B:S,18,0)),"",VLOOKUP(B2,B:S,18,0)))

Wow. Thanks so much. That works perfectly.

 

[Habtest]

Glad it works, thanks for the feedback

 

[dhegs]

Hi, try this:

Book1
	B																	S	T	U
1
2	5502																	REMOVE	first	REMOVE
3	5502
4	5502
5	5502
6	5502
7	5502
8	5502
9	5502
10	5502																	REMOVE	last	REMOVE
11	5530																		first
12	5530
13	5530																		last
14	1000																		first
15	1000																		last
16	2000																	REMOVE	first	REMOVE
17	2000
18	2000
19	2000
20	2000																		last	REMOVE
21	3000																		first
22	3000
23	3000
24	3000
25	3000																		last
Sheet1

Cell Formulas
Range	Formula
U2:U25	U2	=IF(ISBLANK(T2),"",IF(ISBLANK(VLOOKUP(B2,B:S,18,0)),"",VLOOKUP(B2,B:S,18,0)))

Actually, sorry, I just noticed, this only works when the "first" has a corresponding "REMOVE". How can this be done if the "last" does but the first doesn't? I think it is so close!?

 

[Habtest]

Actually, sorry, I just noticed, this only works when the "first" has a corresponding "REMOVE". How can this be done if the "last" does but the first doesn't? I think it is so close!?

Oh, actually it changed everything , but this should do:

Book1
	B																	S	T	U
2	5502																	REMOVE	first	REMOVE
3	5502
4	5502
5	5502
6	5502																		last	REMOVE
7	5530																		first	REMOVE
8	5530
9	5530																	REMOVE	last	REMOVE
10	1000																		first
11	1000																		last
12	2000																	REMOVE	first	REMOVE
13	2000
14	2000
15	2000
16	2000																		last	REMOVE
17	3000																		first
18	3000
19	3000
20	3000
21	3000																		last
Sheet1

Cell Formulas
Range	Formula
U2:U21	U2	=IF(AND(LEN(T2)>1,ISNUMBER(AGGREGATE(14,6,ROW($B$2:$B$21)/($B$2:$B$21=B2)/NOT(ISBLANK($S$2:$S$21)),1))),"REMOVE","")