VLOOKUP HELP ...

[Orana_Jetcity]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)

Hi ppl.I use office 2008
My problem is that i need to create a formula in excel so i can compare clients
Lets say i have 1000 clients in one .xls and
200 in one other.
I need a formula that i can copy - paste in some area lets say the 200 names and the formula to show me witch of the 200 are contained in the 1000 so i can clean them and lets say remain 170 unique of the 200.
I tried but i think i do something whrong
If anyone can help i would be glad
Thanks

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)

Hi ppl.I use office 2008
My problem is that i need to create a formula in excel so i can compare
clients
Lets say i have 1000 clients in one .xls and
200 in one other.
I need a formula that i can copy - paste in some area lets say the 200 names
and the formula to show me witch of the 200 are contained in the 1000 so i
can clean them and lets say remain 170 unique of the 200.
I tried but i think i do something whrong

There are many ways to do this:

If you're going to combine the lists anyway, the easiest would be to
paste the 200 names directly in/under the same list as the 1000, then
use Data/Filter/Advanced Filter to copy the list to another location,
with "Unique records only" checkbox checked.

Alternatively:

Assume:

A ... D
1 Name1 Name2
2 Name2 Name1001
3 Name3 Name7
....
200 Name200 Name1170
....
1000 Name1000

Then,

By Formula:

E1: =IF(COUNT($A$1:$A$1000,D1),"Duplicate","")

copy down. You could then autofilter D:E on E = ""


By Conditional Formatting (if you just want to see the dups):

Select List 2. Assuming cell D1 is active, choose Format/Conditional
Formatting, and enter

CF1: Formula is =COUNT($A$1:$A$1000,D1)
Format1: <pattern>/<color>

will highlight the duplicate cells with your chosen color.

 

[Orana_Jetcity]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)

Hi ppl.I use office 2008
My problem is that i need to create a formula in excel so i can compare
clients
Lets say i have 1000 clients in one .xls and
200 in one other.
I need a formula that i can copy - paste in some area lets say the 200 names
and the formula to show me witch of the 200 are contained in the 1000 so i
can clean them and lets say remain 170 unique of the 200.
I tried but i think i do something whrong

There are many ways to do this:

If you're going to combine the lists anyway, the easiest would be to
paste the 200 names directly in/under the same list as the 1000, then
use Data/Filter/Advanced Filter to copy the list to another location,
with "Unique records only" checkbox checked.

Alternatively:

Assume:

A ... D
1 Name1 Name2
2 Name2 Name1001
3 Name3 Name7
...
200 Name200 Name1170
...
1000 Name1000

Then,

By Formula:

E1: =IF(COUNT($A$1:$A$1000,D1),"Duplicate","")

copy down. You could then autofilter D:E on E = ""


By Conditional Formatting (if you just want to see the dups):

Select List 2. Assuming cell D1 is active, choose Format/Conditional
Formatting, and enter

CF1: Formula is =COUNT($A$1:$A$1000,D1)
Format1: /

will highlight the duplicate cells with your chosen color.
[/QUOTE]

sounds nice but...i didnt manage to make it
Is it possible that someone can create a blank form and so i can paste tha names?
Like it sounds its quite easy for you have major problems even to follow the steps you say