Finding duplicates in 2 worksheets

[darkwood]

I have 2 worksheets of data for a mailing list I'm doing. The first worksheet has criteria pulled from one set of software and the other is from a separate system. What I want to do is find which account numbers from Sheet 2 appear on Sheet 1. Those that match get deleted from both Sheet 1 and 2 put into a new worksheeet (Sheet 3), leaving me with no duplicates in Sheet 1, and whatever is left in Sheet 2 is discarded. I'll then use Sheet 1 for one mailing, and Sheet 3 for the other.

Any ideas?

[Duke Carey]

Put both datasets in MS Access. Let's call one Table1 and the other Table2.
Assuming the account# column is named AcctNum, use a query like this to get
duplicates:

Select T1.*
FROM Table1 T1
INNER JOIN Table2 T2
on T1.AcctNum = T2.AcctNum

These are all your duplicates

To get the remaining records from T1, the ones that are not duplicates, use
this query

Select T1.*
FROM Table1 T1
LEFT JOIN Table2 T2
on T1.AcctNum = T2.AcctNum
WHERE T2.AcctNum IS NULL






"darkwood" wrote:

>
> I have 2 worksheets of data for a mailing list I'm doing. The first
> worksheet has criteria pulled from one set of software and the other is
> from a separate system. What I want to do is find which account numbers
> from Sheet 2 appear on Sheet 1. Those that match get deleted from both
> Sheet 1 and 2 put into a new worksheeet (Sheet 3), leaving me with no
> duplicates in Sheet 1, and whatever is left in Sheet 2 is discarded.
> I'll then use Sheet 1 for one mailing, and Sheet 3 for the other.
>
> Any ideas?
>
>
> --
> darkwood
> ------------------------------------------------------------------------
> darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948
> View this thread: http://www.excelforum.com/showthread...hreadid=541624
>
>

[Ronald Dodge]

This is something that I have setup an index column on both sheets with a
distinctive character or pattern of characters in between each cell for the
different columns that's getting compared.

Example:

Columns A, B, C and D are getting compared for the rows of 5:3000 on
"Sheet1" and "Sheet2"

In an empty column, let's say you have to go all the way out to column AA,
you would type in the following formula in Cell AA5:

=A5 & "|" & B5 & "|" & C5 & "|" & D5

I'm also assuming that both sheets are setup in the same format, but if not,
adjust accordingly. This formula would be put on both sheets.

Now, for this second part, you will be using the MATCH function, which the
more records there are, the more costly it gets to be time wise as such a
function can cause the system spend an extra amount of time calculating. It
isn't noticeable if you have just a few of these, but with a lot of them,
it's very noticeable. Therefore, before you continue, you may want to set
your calculation mode to "Manual" on a temporary basis, so as you aren't
waiting for it each and every time you make a change in Excel. Some might
say the background calculation that been put into Excel starting with the
2000 version alleviate this issue. However, in my experience that I have
found, sometimes, it works like it suppose to, but other times, it doesn't
work like it suppose to. Like it seems to work properly most of the time on
both computers that I work on at work using Excel 2002, but for the system
at home using Excel 2003, it rarely works properly.

Anyhow, here's the formula you will put in Sheet1!AB5 (or adjust accordingly
like you did with the first formula depending on where you put the first
formula):

=IF(ISERROR(MATCH(AA5,Sheet2!AA:AA,0)),0,1)

After you have put in your formula, go to the very bottom of the list, move
the active cell to column AA of that last row on "Sheet1".

Hold down the Shift key, and press the right arrow key.

While still holding down the Shift key, also hold down the Ctrl key, and
press the up arrow key.

Now, press and hold the Ctrl key, and then press the letter D for Fill Down.

Make sure you have the first formula on Sheet2 as well.

If calculation mode has been set to Manual, press and hold down the Shift
key, and press F9 to calculate that worksheet (Sheet2 first, then Sheet1 in
this case). This may take minutes depending on how many records you have of
both sheets as well as what your system resources are and how much of those
resources are being used. The reason you calculate Sheet2 first is cause
none of the formulas on Sheet2 is dependent on Sheet1, while some of the
formulas on Sheet1 is dependent on the calculated values of Sheet2.


Now, you can convert the formulas to values by doing a copy (Ctrl-C), and
then paste special as values (Alt, E, S, V, Enter).

Sort the data by the AB column, then what other order you want after that.
Note, all of the rows with a 1 in the AB column are the ones that are found
on both, Sheet1, and Sheet2. The ones with a 0 in the AB column, are not,
and it sorted so as the duplicates are at the bottom of the list, so you can
select those records on Sheet1 and copy them (Ctrl-C), paste to Sheet3
(Ctrl-V), go back to Sheet1 and delete those records (Either Delete or press
and hold the Ctrl key while pressing the numpad minus key, then down arrow
one time may be needed to move rows up). All that's left to do at this
point, delete Sheet2, and if you want, delete columns AA:AB on Sheet1 as
those 2 columns aren't needed anymore (or which ever 2 columns you use for
these formulas on Sheet1).

It's not the most efficient way of doing it, but it's the quick and dirty
way of doing it. Unless you plan on doing something like this on a more
regular basis, this would probably serve you best, as the more efficient way
of doing it has more of a setup time done via macros.

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

"darkwood" <[email protected]> wrote in
message news:[email protected]...
>
> I have 2 worksheets of data for a mailing list I'm doing. The first
> worksheet has criteria pulled from one set of software and the other is
> from a separate system. What I want to do is find which account numbers
> from Sheet 2 appear on Sheet 1. Those that match get deleted from both
> Sheet 1 and 2 put into a new worksheeet (Sheet 3), leaving me with no
> duplicates in Sheet 1, and whatever is left in Sheet 2 is discarded.
> I'll then use Sheet 1 for one mailing, and Sheet 3 for the other.
>
> Any ideas?
>
>
> --
> darkwood
> ------------------------------------------------------------------------
> darkwood's Profile:
http://www.excelforum.com/member.php...o&userid=29948
> View this thread: http://www.excelforum.com/showthread...hreadid=541624
>