Similiar data on two different sheets - how to ID what is differen

[Steve]

I have some employee data from two different sources (on two separate tabs).

On 3rd tab, I'd like to show if the data is on sheet A , but not on sheet
B, and vice-versa. E.g.

Sheet A
Employee ID Name
12345 John
78910 Mary
45678 Joe

Sheet B
Employee ID Name
12345 John
45678 Joe
77777 Jill


So I'd like to have in Sheet C something to the effect of:
Mary is in sheet A, but not B &
Jill is in sheet B, but not A.

Another thing I'd like to be able to do is:

One of the sheets shows days off as follows
NNNNYYN Y's meaning they're off Wed & Thurs
the other sheet shows WedThu as their days off
Can I also get someting to indicate that for that particular employee ( ID)
if NNNNYYN meaning off Wec & Thu on one sheet, but shows FriSat on the other,
to produce an Alert such as "day offs don't match"" ?

Much thanks,

Steve

 

[Bob Bridges]

There's an easy solution to the first part of this one, Steve, but a
surprising number of Excel users seem to be unfamiliar with it so you're in
plentiful company. I'll just tell you the bare bones; you can probably gussy
it up pretty without further help. In some column in sheet A to the right of
column A (where the emp numbers are), on row 3 let's say, type this formula:

=MATCH(A3,SheetB!A3:A253,0)

This looks in A3:A253 (or whatever your search range should be) on SheetB
for the emp number in column A of this row. If it finds that exact value, it
displays the row number in this cell; otherwise it displays #N/A. If it
finds the value you can use the row number with INDIRECT to compare the
days-off values; if it didn't, the ISERROR test can be used to display the
aviso that it's not on the list at all.

Do the same in SheetB to match the other way.

 

[Steve]

Bob,

Thanks so much. Very nice. I additionally used is number in a column, and in
another column if false, to produce the actual name. It's working perfect.
A couple things, if you don't mind.
Each sheet has a header row, and that's throwing the row #'s off by 1. I
assume there's a simple fix for that in the formula.
Also, I don't understand the indirect part for the days off values.
I'm also thinking I may have to create maybe a table to show:
NNNNNYYN = THUFRI
NNNNYYNN = WEDTHU
NNNYYNNN = TUEWED
NNYYYYYY = SATSUN
etc, to start with.
Then to compare/match the alphabetical days off, and if no match, "days off
don't match" .
Basically if John 12345 on sheet A is showing NNYYYYY ( which means off Sat
& Sun), but on sheet B he's showing TUEWED, I need that to tell me that his
days off don't match.
I hope I'm explaining this right. Thanks for your patience.

Steve

 

[Bob Bridges]

The header row is easy: Since MATCH shows you a row number that's relative
to the table, and you need a number that is one greater than that, you just
use MATCH(...)+1 for the row number. Actually I'd put the MATCH in a helper
column and refer to it from other places.

INDIRECT, if you've never used it before, allows you to construct a text
address and have Excel intepret it as a real one. For example, in Sheet1!A1
I can put this formula:

=Sheet2!C5

....and Excel will display in Sheet1!A1 whatever value is in Sheet2!C5 - pi,
let's say, to 5 decimal places. But if I used quotes:

="Sheet2!C5"

....then instead of 3.14159, all you'll ever see in A1 is "Sheet2!C5". But
if you use the INDIRECT function:

=INDIRECT("Sheet2!C5")

....it can intepret "Sheet2!C5" as an address, and what you'll get back is
3.14159.

The nice thing about this is that you can take pieces of an address - like a
row number - and do arithmetic and concatenation to it to pull in a value
from somewhere else. So let's say you're looking on both sheets your
days-off column is E, and in column N you have that MATCH formula from below:

=MATCH(A3,SheetB!A3:A253,0)

As a result, SheetA!N3 shows, say, "13" - meaning the emp that is in row 3
in SheetA is on row 14 (not 13) in SheetB. Now you know that the employee's
days-off value is in E14 (not E13) in SheetB, so you can point to it with
this formula:

=INDIRECT("SheetB!E"&N3+1)

N3 is 13, remember, so N3+1 is 14 and "SheetB!E"&14 yields "SheetB!E14",
which gives you the proper address for ... well, you get it, I'm sure.

It isn't clear to me why it would make any difference whether you compared
"NNYYNNN" with "NYNYNNN" or "WETH" with "TUTH", or whatever; they're still
not going to match, so you're still going to have the right criteria for your
"days off don't match" message.

 

[Steve]

Ok, thanks. I'll work on that Match & Indirect solution.

Regarding the NNNNNYY stuff, I don't think I explained it clearly.
One source shows the days off of Sat & Sun in this format ->YYNNNNN
The other source shows it in this format -> SATSUN.
Most of the time, both sheets/formats will show the same days off, though
sometimes they don't. I need to indentify when they don't match, so I figured
I have to first convert the YYNNNNN format to the SATSUN format, and then use
the formula alert as 'days off don't match".

Thanks,

Steve

 

[Steve]

OK, I'm confused on the indirect match.

I did do a vlookup with a table to change the , e.g. YYNNNNN to SATSUN, the
NNYYNNN to MONTUE, etc. so that at least the YYNNNNN format stuff will be in
the SATSUN format.

Now,
Sheet A
ID # Days Off
12345 SATSUN,

but on sheet B, if the same ID shows different days off

Sheet B
ID # Days Off
12345 MONTUE "'Alert"

That's when I want that alert to show, such as
if(SATSUN=SATSUN), then "","ALERT"
Also remember that I have many of those Employee ID's, and when those ID's
are on both of those sheets, I'd like it to check the days off as above.

Thanks in advance for all your patience.

Steve

 

[Bob Bridges]

Right you are, I missed what you were (I see now) saying very plainly. Yeah,
I agree you need a conversion from NNYNYNN format to TUETHU format. You have
one now, I gather, so that part's taken care of, right? What comes after
that? I mean, how much have you already figured out and what are you asking
about next?

I don't want to sound insulting by telling you something that was already
obvious to you, but I'll tell you how I'd go about it, just off-hand:

1) In both sheets: EmpNbrs in col A, other stuff (name etc) in cols B-D,
days off in col E. I'll pretend that on SheetA the days-off format is MONTUE
and on SheetB it's NYYNNNN.

2) In both sheets, =MATCH in column N.

3) In SheetB col O, a conversion of col E's NYYNNNN days-off value to MONTUE
format, to match SheetA.

4) In SheetA col F, a check to see whether this employee's days off match
the same schedule on SheetB, using IF functions and INDIRECT, maybe like this:

=IF(ISERROR(N3),"Emp not on SheetB",IF(INDIRECT("SheetB!O"&N3+1)=E3,"Emp
matches","Alert: DaysOff nomatch!"))

Of course you'd choose different messages, probably blank ones in at least
one of those cases.

That formula has a lot of pieces in it, so take your time. N3+1 contains
the row the same employee is in on SheetB, so INDIRECT("SheetB!O"&N3+1) gives
you that row's converted days-off code so you can compare it with col E on
this sheet, which is the point here. Did I do any better?

 

[Steve]

No, I'm never insulted when it comes to Excel, and I appreciate all the help
I could get. I'm still having trouble with it. Perhaps if I ID the pertinent
data on each sheet, and their columns, you'll see exactly what I need.

Sheet A
B K S (my Vlookups results)
U
12345 NNNNNYY THUFRI =If "sheeta!"S2="sheetB!"
L2,"","Alert"


Sheet B L
B
12345 TUEWED

So U2 would show the ALERT because the sheet A THUFRI did not match the
Sheet B TUEWED
But it won't be that simple for my if/alert formula, because remember the
same employees are in different rows on each sheet, and some are on A & not
on B, and vice-versa. That's why I think the indirect match is needed, which
I can't grasp.

Thanks again,

Steve

 

[Bob Bridges]

I think you're right that it's time to switch to your actual worksheets
instead of my coming up with hypothetical columns N and O and what-not. But
looking at the below I'm not sure I'm reading it right. Want to switch to
email? Send me your .xls - use the email from my profile, after properly
modifying it - and I'll show you a couple examples of INDIRECT using your own
workbook. That and a few more back-and-forths oughta knock in the head
whatever confusion we're having.