Checking for Duplicates

[Axim5]

Hi,

I am new to this forum, infact this is my first thread. I have been
reading various threads and replies and I must say that I am impress
with the level of knowledge available here.

Now, I would like assistance in figuring out a solution.

I have 2 sheets with following data (for example):
Sheet 1:

Cert # Amount
CA 1000345 $1,000.00

and the above continue on to upto 600 rows.

On Sheet 2: I also have the same data (not necessarily the same
numbers).

I want to do the following:

I would like to (on a new sheet) list of all the duplicate "Cert #s"
and their corresponding "Amounts".

What is the easiest way to do that?

BTW, I am using Excel 2002.

Thanaks is advance.

 

[Dave Peterson]

Chip Pearson has a bunch of techniques for dealing with duplicates at:
http://www.cpearson.com/excel/duplicat.htm

He has suggestions for worksheet formulas and VBA code there.

 

[Max]

Try the following steps, which uses Advanced Filter
in *back-up* copies of your workbook (not the original)

Note1:
(i) "Duplicate(s)" means: the 2nd, 3rd, 4th etc instances
of each Cert # in the col.
(ii) "Unique" means: the first instance of each Cert # in the col.

1. Assuming you want to extract the duplicate Cert #s
in Sheets 1 & 2 separately, i.e. you want the duplicates
present in each sheet (on its own)

(a) In Sheet1:

Sekect Cert # col
Click Data > Filter > Advanced Filter
(Click OK to the Excel prompt to use the first row as labels)

In the Advanced Filter dialog:
Check Filter the list, in-place
Check Unique values only
Click OK

Select all the "blue colored" filtered rows
(select all the "blue" row headers)

These are the "unique" rows which you do not want

Right-click > Delete rows

Click Data > Filter > Show All

This will reveal the duplicate rows in Sheet1

(b) Repeat steps for Sheet1 in Sheet2,
to get the duplicate rows in Sheet 2

(c) Copy > Paste the duplicate rows remaining
from (a) and (b) above into a new Sheet3

2. Assuming what you want is to extract the duplicates in Sheet 1 & 2
together,
i.e. you want the duplicates present when you combine the data from both
sheets

(a) In a new Sheet 3:

Copy > Paste the data from Cert # & Amount cols
in both Sheets 1 & 2 into 2 cols in Sheet3,
with say, Sheet2's data immediately below Sheet1's

[Note2: the data arrangement order, viz Sheet1's data first followed by
Sheet2's,
or the other way around, will dictate what is considered unique / duplicate
Cert #s.
Refer Note1's explanation above]

Perform same steps for 1(a) above to extract the duplicates
for the combined Sheet 1 & 2's data

hth
Max