Vlookup: can it do this?

[enigmahack]

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!

 

[poorwallace]

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?

 

[Barrie Davidson]

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?

 

[nbrcrunch]

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.

 

[enigmahack]

ADVERTISEMENT

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.

 

[Barrie Davidson]

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?

 

[enigmahack]

ADVERTISEMENT

haven't even thought of that! Thanks! I'll try it!

 

[enigmahack]

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?

 

[Barrie Davidson]

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?

 

[Norie]

Eh, what's 'attrits'?