Matching based on rows

dhegs

New Member
Joined
May 6, 2021
Messages
6
Platform
  1. MacOS
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?!
 

Attachments

  • Screen Shot 2021-05-07 at 11.45.52 am.png
    Screen Shot 2021-05-07 at 11.45.52 am.png
    43.9 KB · Views: 11

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Like this?

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

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
193
Office Version
  1. 365
Platform
  1. Windows
Hi, try this:
Book1
BSTU
1
25502REMOVEfirstREMOVE
35502 
45502 
55502 
65502 
75502 
85502 
95502 
105502REMOVElastREMOVE
115530first 
125530 
135530last 
141000first 
151000last 
162000REMOVEfirstREMOVE
172000 
182000 
192000 
202000lastREMOVE
213000first 
223000 
233000 
243000 
253000last 
Sheet1
Cell Formulas
RangeFormula
U2:U25U2=IF(ISBLANK(T2),"",IF(ISBLANK(VLOOKUP(B2,B:S,18,0)),"",VLOOKUP(B2,B:S,18,0)))
 

dhegs

New Member
Joined
May 6, 2021
Messages
6
Platform
  1. MacOS
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

Well-known Member
Joined
Feb 6, 2012
Messages
1,951

ADVERTISEMENT

So remove first and last UNLESS this removes all?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
=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",""))

1620355501125.png
 

dhegs

New Member
Joined
May 6, 2021
Messages
6
Platform
  1. MacOS

ADVERTISEMENT

Hi, try this:
Book1
BSTU
1
25502REMOVEfirstREMOVE
35502 
45502 
55502 
65502 
75502 
85502 
95502 
105502REMOVElastREMOVE
115530first 
125530 
135530last 
141000first 
151000last 
162000REMOVEfirstREMOVE
172000 
182000 
192000 
202000lastREMOVE
213000first 
223000 
233000 
243000 
253000last 
Sheet1
Cell Formulas
RangeFormula
U2:U25U2=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

Board Regular
Joined
Jul 30, 2020
Messages
193
Office Version
  1. 365
Platform
  1. Windows
Glad it works, thanks for the feedback :)
 

dhegs

New Member
Joined
May 6, 2021
Messages
6
Platform
  1. MacOS
Hi, try this:
Book1
BSTU
1
25502REMOVEfirstREMOVE
35502 
45502 
55502 
65502 
75502 
85502 
95502 
105502REMOVElastREMOVE
115530first 
125530 
135530last 
141000first 
151000last 
162000REMOVEfirstREMOVE
172000 
182000 
192000 
202000lastREMOVE
213000first 
223000 
233000 
243000 
253000last 
Sheet1
Cell Formulas
RangeFormula
U2:U25U2=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

Board Regular
Joined
Jul 30, 2020
Messages
193
Office Version
  1. 365
Platform
  1. Windows
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 :eek:, but this should do:
Book1
BSTU
25502REMOVEfirstREMOVE
35502 
45502 
55502 
65502lastREMOVE
75530firstREMOVE
85530 
95530REMOVElastREMOVE
101000first 
111000last 
122000REMOVEfirstREMOVE
132000 
142000 
152000 
162000lastREMOVE
173000first 
183000 
193000 
203000 
213000last 
Sheet1
Cell Formulas
RangeFormula
U2:U21U2=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","")
 

Forum statistics

Threads
1,141,062
Messages
5,704,057
Members
421,325
Latest member
tapete86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top