Vlookup: can it do this?

enigmahack

Board Regular
Joined
Jun 7, 2005
Messages
96
I've been messing with vlookup for a while, and I think it can, but I'm not sure entirely, so I'm working on it :-)

I have a pretty simple task here - we have a list of agents that have agent id's and there's a large number of them.

What I want to do:
have a list on one form of all agents.
have a list on another form of all attrits *fired/quit, whatever*
from the attrit list, I want to lookup the employee ID, and if there's a match on the "all agent" list, change that line somehow, in either color, etc...
There's almost 1500 agents and with so many coming in and going out all the time it's hard to look through that list every time.
Anyway, if someone has an idea, I'd be very grateful!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
so do you already have the vlookup in place? If so, it sounds like it's a matter of using conditional formating...

Edit

As far as I know, conditional formatting will only change the cell (unless you apply it to multiple cells) ...do you need to have the whole row changed?
 
Upvote 0
Need some clarification...
Are you entering the agent id on the attrib worksheet and trying to determine which attribute is on the "all agents" worksheet?
 
Upvote 0
Seems you are working harder at this than need be. Limit to one list (Your master agents list) and merely have one column each indicating start date and end date. If end date is blank, then you know they are still employed. Do away with attrition table.
 
Upvote 0
Sorry, I could have been a little clearer:

Sheet 1 - list of reps - the list basically looks like this:

John A - 111111
John B - 111112
John C - 111113

etc.

Sheet 2 - list of Attritions:
John L - 111122

What I need it to do is to basically highlight the line that John L - 111122
exists on Sheet 1 because he's fired - The date idea would work, but we don't have that information provided to us - we simply get their emp.ID letting us know that they left.
 
Upvote 0
Why not use the MATCH function in a blank column in your agent listing (matching agent id's to the attrition) table? Then delete any rows that have a match. Have you tried that?
 
Upvote 0
Wait, how do I get it to delete it if there's a match - I've got it to show me that there ARE matches, but how do I start to make it preform functions on specific lines that are matches?
 
Upvote 0
enigmahack said:
Wait, how do I get it to delete it if there's a match - I've got it to show me that there ARE matches, but how do I start to make it preform functions on specific lines that are matches?

Filter your data on records that are not a match for #N/A and then delete those rows. Have you used auto filtering?
 
Upvote 0

Forum statistics

Threads
1,224,587
Messages
6,179,735
Members
452,939
Latest member
WCrawford

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
Back
Top