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



"Bob Bridges" wrote:

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" wrote:
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.

--- "Bob Bridges" wrote:
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" wrote:
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: One of the sheets shows days off
as follows: NNNNYYN, Ys 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"" ?