How do I Compare worksheet columns

[Guest]

I have two worksheets; One with the names (last, first) of last years
employees,
The second with the current employee list (last, first). I need to compare
the two lists and display a list of employees NOT on the current list. "who's
not with us anymore". Can anyone help? Thanks.

 

[JulieD]

Hi

Check out Chip Pearson's page on working with duplicates at
http://www.cpearson.com/excel/duplicat.htm

the article you're after is right down the bottom of the page, but you also
might find some other useful things along the way.

 

[Max]

Here's one crack at this ..

Assume data below is in sheet: Current,
cols A to B, data from row2 down

Fname Lname
Durell Joe
Lowre Peter
Factor Max
Martin Peters

and this data below is in sheet: Last,
cols A to B, data from row2 down

Fname Lname
Martin Peters
Marsh Betty
Durell Joe
Polar Rich
Factor Max

Using an empty col to the right, say col C?

Put in C2's formula bar and array-enter (Press CTRL+SHIFT+ENTER) :
=IF(TRIM(A2&"_"&B2)="","",IF(ISNUMBER(MATCH(A2&"_"&B2,Current!$A$2:$A$100&"_
"&Current!$B$2:$B$100,0)),"",ROW()))

Copy C2 down as many rows as there is data in cols A and B

In a new sheet: Results
----------------------
Copy > Paste the same col headers into A1:E1

Put in A2:
=IF(ISERROR(SMALL(Last!$C:$C,ROWS($A$1:A1))),"",INDEX(Last!A:A,MATCH(SMALL(L
ast!$C:$C,ROWS($A$1:A1)),Last!$C:$C,0)))

Copy across to B2, fill down by as many rows as was done in "Last"

"Result" will return all the rows from "Last"
whose data do not match those in "Current"
(returned rows will be bunched at the top)

For the sample data above, you'll get:

Fname Lname
Marsh Betty
Polar Rich
(rest are "blank" rows)

Adapt to suit ..

A sample file for the above is available at:
http://flypicture.com/p.cfm?id=41449
(right-click on link "Download File" at the top)
File: dannix54_wksht.xls

 

[Max]

Correction to array formula in C2 in "Last"
Use instead in C2:
=IF(ISNUMBER(MATCH(A2&"_"&B2,Current!$A$2:$A$100&"_"&Current!$B$2:$B$100,0))
,"",ROW())

Corrected sample at: http://flypicture.com/p.cfm?id=41454
(right-click on link "Download File" at the top)
File: dannix54_wksht1.xls

 

[Max]

Re-corrected sample at:
http://flypicture.com/p.cfm?id=41496
(right-click on link "Download File" at the top)
File: dannix54_wksht11.xls

(Formulas in "Results" need not be array-entered,
just normal ENTER will do)

 

[Guest]

Hello Max,
The results in my worksheet are yeilding your sample results, not the
results from the collumn compare. Do I need to change the formula in any way?
Thank you,
Dan

 

[Max]

Of course you would need to adapt to suit what you actually have <g>

Maybe email over a copy of your file, I'll take a look

You can email to either:
demechanik <at>yahoo<dot>com, or
xdemechanik <at>yahoo<dot>com

 

[Max]

File with implemented suggestion returned to OP:

Range in formula in col C in "Last" was adapted to suit the extent of the
data that was in "Current", i.e. used:

Current!$A$2:$A$2042&"_"&Current!$B$2:$B$2042
(as the last row of data in Current was row 2042)

instead of:
Current!$A$2:$A$100&"_"&Current!$B$2:$B$100

 

[Guest]

Hi,

Suppose you have thelist of previous years employees in range A1:A2 and a
list of the current years employees in range B1:B2. In C1, array enter
(Ctrl+Shift+Enter) the following formula

=IF(OR(A1=$B$1:$B$2),A1,"Not in list")

Regards,

Ashish Mathur