If then or vlookup?

flyers2thecup22

New Member
Joined
Aug 10, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello All, been a while since i've posted. Had to make a new account.

I'm trying to figure out a way to identify a person who has "x" roles. Using the example data below, what i'm trying to do is identify an ID (column A) if they have either role3 or role4 ONLY. below i would want to somehow 'delete' or mark John Smith since he has role1, role2 and role5 in addition to role3 and role4. my final list should be those that have role3 and/or role4 ONLY. hopefully that makes some kind of sense. Is there a way to do that?

Thank you for your time!

ABCD
IDLast NameFirst NameRole
XXXXXXXSmithJohnRole1
XXXXXXXSmithJohnRole2
XXXXXXXSmithJohnRole3
XXXXXXXSmithJohnRole4
XXXXXXXSmithJohnRole5
AAAAAAADoeJaneRole3
AAAAAAADoeJaneRole4
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Why not filter the data by roles you don't want and delete those rows?
 
Upvote 0

flyers2thecup22

New Member
Joined
Aug 10, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thank you for your response.

If I filter and hide role1, role2 and role5, that will still leave john smith visible with role3 and role4. I want him totally gone since he has role3 and role4.
 
Upvote 0

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
How about a "Helper Column" in column "A" with the formula below then filter for all the non-zeros and delete those rows?

Book6
ABCDE
1Helper ColumnIDLast NameFirst NameRole
231SmithJohnRole1
331SmithJohnRole2
431SmithJohnRole3
531SmithJohnRole4
631SmithJohnRole5
702DoeJaneRole3
802DoeJaneRole4
Sheet1
Cell Formulas
RangeFormula
A2:A8A2=COUNTIFS($B$2:$B$8,"="&B2,$E$2:$E$8,"<>Role3",$E$2:$E$8,"<>Role4")
 
Upvote 0
Solution

flyers2thecup22

New Member
Joined
Aug 10, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Awesome! That worked perfectly. Thank you very much for the help! Saved me hours/days of manually going through a spreadsheet with over 125k rows. :)
 
Upvote 0

Forum statistics

Threads
1,187,142
Messages
5,961,806
Members
438,565
Latest member
jameskbw

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