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

=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.

"Steve" wrote:

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

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.


"Steve" wrote:

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

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

On Oct 3, 9:50*pm, Steve wrote:
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

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

"Thomas [PBD]" wrote:

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.


"Steve" wrote:

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