Comparing data from different spreadsheets????

[kittymis]

Hi I have some a small problem with comparing data.

I have data on to different sheet:

Sheet A

names account no.
fred 12
Marie 54
An so on....

Sheet B also includes names and account no. but not identical to the ones in
sheet A.

Since the list is endless....more or less is is most convinient to keep the
two sheets.

The problem:

I wish to find out which names and accounts are repeated in both A and B. If
possible as an output in a blanc sheet C.

Hopefully someone can assist me. I have tried to use countif and if but have
not succeded.

[pinmaster]

Hi
One way would be to use conditional formatting. But because conditional formatting cannot reference other worksheets you will have to use range names. Start by selecting your data in sheet B, once you have selected, type a name in the name box left of the formula bar.....say "list", then select your data in sheet A, goto FORMAT/CONDITIONAL FORMATTING select FORMULA IS and type:
=AND(COUNTIF(list,$A1)>0,COUNTIF(list,$B1)>0)
choose your format....say red background

A1 and B1 represents the first column and row of your data in sheet A, ajust accordingly

HTH
JG