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

dhegs

New Member
Joined
May 6, 2021
Messages
6
Platform
  1. MacOS
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","")
[/RANGE
Cell Formulas
RangeFormula
Thank you so much again - your help is so appreciated. Okay so this works... IF column C is just written text, but this is a formula! So I am getting it responding to remove when there is no "REMOVE" because the formula is returning "". I have tried but am unsure how to fix that part of your formula: NOT(ISBLANK($C$2:$C$2325) ?!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
193
Office Version
  1. 365
Platform
  1. Windows
Please see the revision:

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)/($S$2:$S$21="REMOVE"),1))),"REMOVE","")


I'm still using the table above, including reference to column S instead of C, for consistency purpose. You may of course tweak where needs be.
 
Solution

dhegs

New Member
Joined
May 6, 2021
Messages
6
Platform
  1. MacOS
Please see the revision:

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)/($S$2:$S$21="REMOVE"),1))),"REMOVE","")


I'm still using the table above, including reference to column S instead of C, for consistency purpose. You may of course tweak where needs be.
You are a genius! That is great - all tested and all working. Thanks so much again! Apologies for any confusion with using different column letters!
 

Habtest

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

Forum statistics

Threads
1,141,062
Messages
5,704,062
Members
421,326
Latest member
pfaustino

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