Separating employees that are in database A and database B from employees who are only in database A

Copper Contributor

My company has two databases where PTO is recorded, and I’ve been asked to do an audit to find discrepancies between the two. One big issue is that not all employees from Database B are in Database A, so I’d like a way to differentiate between Database B employees who are in Database A, and Database B employees who are not. My solution is =IF(VLOOKUP(D2,$A:$C,3,FALSE)="#NA","no match","match"). That way, I’ll be able to sort the range to separate matches from non-matches, which I’ll need to deal with separately.

 

I also want to be able to sort the matches by name, so the employees in Database A line up exactly with Database B. Once that’s accomplished, I’m planning to use  conditional coloring to highlight the employees with PTO discrepancies, so I can go in and figure out what exactly is causing the discrepancy in the database itself.

 

Also, If you can think of another way to set up my data so I can easily see which employees have PTO discrepancies between the two databases, please let me know!

 

Thanks:smiling_face_with_smiling_eyes:

 

Database A Employee #Database A Employee NameDatabase A PTO UsedDatabase A Employee #Database A Employee NameDatabase B PTO Used
1Knope, Leslie248.001Knope, Leslie248.00
2Swanson, Ron200.008Trager, Chris80.00
3Beavers, Ethyl201.083Beavers, Ethyl201.08
4Haverford, Tom748.134Haverford, Tom760.00
5Ludgate, April896.3210Wyatt, Ben40.00
5 Replies

@Caitlin_Hanley_1994 

The #NA for which you are testing is actually not text; it is an indicator for a particular type of Excel processing error. To test for that error, you can use the ISNA function, as in:

=IF( ISNA(VLOOKUP(D2,$A:$C,3,FALSE)), "not found", "found" )


(The term "found" is used instead of "match", which is better reserved for amount comparisons.)

Hi @Caitlin_Hanley_1994 

 

Assuming you run Excel > 2013/Windows or 365/Mac, a possible Power Query option is attached:

 

#1 a way to differentiate between Database B employees who are in Database A, and Database B employees who are not

Sample1.png

 

#2 employees in Database A line up exactly with Database B...use conditional coloring to highlight the employees with PTO discrepancies

Sample2.png

 

#3 easily see which employees have PTO discrepancies between the two databases

Sample3.png

 

Any question please let me know

You're welcome @Caitlin_Hanley_1994 

And next time someone will have a similar challenge we'll re-invent the wheel due to no feedback from your end (Thank you)

@Lorenzo Seems like there's been a lot of "ghosting" going on lately. That, plus the flooding of A.I. (Anti-Intelligence) generated responses, has caused my enthusiasm for helping out here to dwindle. :(

@djclements I'm with you. People who come here (or other forums) to get what they need and disappear don't mind a sec. what the possible implications are & sooner or later they run the risk there'll be less and less... contributors on communities to try helping them

I sometimes do this kind of follow-up and if no feedback ==> the OP goes on my black list