If ID # on one sheet isn't found on another...

[Steve]

I have two differnt source files for employees on two different tabs. Both
tabs have their ID #:
On sheet A it's in the B column.
B C
123 Sam

On sheet B it's on the G columm.

G H
(Sam's ID & name are not on Sheet B)

The row locations are different, meaning one week Sam's ID may be in A16, and
the next week in A20. Same with sheet B.

What formula could I use on sheet A to identify if an employee is on A but
not B, and on sheet B, if an employee is on B but not A ? Like if found on A
but not on B, produce the name (Sam) which is in the a column next to the
ID# ( column C in sheet A).

I hope this makes sense.

Much thanks,

Steve

 

[Sean Timmons]

=IF(ISERROR(VLOOKUP(B2,SheetB!G:H,2,0)),C2,VLOOKUP(B2,SheetB!G:H,2,0))

Will look for the B2 value from Sheet A in Sheet B, column G. If it can't
find the exact value, it will return value from C2 of Sheet A. If it DOES
fidn the value, it returns the value from column H of sheet B.

=IF(ISERROR(VLOOKUP(G2,SheetA!A:B,2,0)),H2,VLOOKUP(G2,SheetA!A:B,2,0))

Same idea, but to be placed on Sheet B.

 

[Thomas [PBD]]

Steve,

In D2 on Sheet A, I placed:
=IF(COUNTIF(B!G:G,A!B2)=0,C2,"")
which returns the persons name if they are not found on Sheet B, if it is
there, it returns nothing.

Same with Sheet B in I2, I placed:
=IF(COUNTIF(A!B:B,B!G2)=0,H2,"")

 

[Pete_UK]

Use this on SheetA:

=IF(ISNA(MATCH(B2,SheetB!G:G,0)),"not present on B","")

and copy down, then use this on SheetB:

=IF(ISNA(MATCH(G2,SheetA!B:B,0)),"not present on A","")

and copy this down.

In each sheet you know the names that are on that sheet, so you just
want to know which of them are not on the other sheet.

I've assumed that your data starts in row 2 on both sheets - adjust B2
and G2 if necessary.

Hope this helps.

Pete

 

[Steve]

Thanks guys.
I'm always amazed at how many different ways Excel can produce the desired
results. So far I tried the countif solution, because it initially seemed
like the least complicated, and it worked fine. I'll be trying the others to
try to broaden my Excelibility.

Thanks again to all

Steve

Steve,

In D2 on Sheet A, I placed:
=IF(COUNTIF(B!G:G,A!B2)=0,C2,"")
which returns the persons name if they are not found on Sheet B, if it is
there, it returns nothing.

Same with Sheet B in I2, I placed:
=IF(COUNTIF(A!B:B,B!G2)=0,H2,"")

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.

I have two differnt source files for employees on two different tabs. Both
tabs have their ID #:
On sheet A it's in the B column.
B C
123 Sam

On sheet B it's on the G columm.

G H
(Sam's ID & name are not on Sheet B)

The row locations are different, meaning one week Sam's ID may be in A16, and
the next week in A20. Same with sheet B.

What formula could I use on sheet A to identify if an employee is on A but
not B, and on sheet B, if an employee is on B but not A ? Like if found on A
but not on B, produce the name (Sam) which is in the a column next to the
ID# ( column C in sheet A).

I hope this makes sense.

Much thanks,

Steve