i need to find duplicates! ASAP

[mj]

Hello guys!

I'm really new in using formulas in excel. I badly needed help in
finding duplicates in my workbook.

Here's the scenario: I have one workbook containing 5 worksheets. Each
worksheet has 10-15 columns of data with 600-800 records (row). With
this, I'm having a hard time tracking down the duplicates that
occurred in the whole worksheet. If I'll do it manually it will take
me ages to finish it and I don't have the time to do it. Is there an
easier way to find or highlight duplicates in the whole worksheet and
compare it to the remaining worksheets so I could only have unique
records?

I tried using the countif, IS and MATCH functions (conditional
formatting) in 2-column data but I don't know if the functions would
work in such amount of records. I'm not familiar with macros
either...:-(

If someone could help me, I would deeply appreciate it.

Thanks in advance!

[rsenn]

You can try something like this. You'll have to modify it for different tabs, and depending on your data you may have to first concatonate two or more columns to get a unique key.

I'm going to use a simple example.


Assume you have a column of names in column A.

In cell B5 type the formula =VLOOKUP(A5,A$1:A4,1,false), and copy it up and down column B alongside the names in column A.

The first instance of each name will have a #N/A next to it. These you apparently want to keep, and delete the others.

[Bob Phillips]

Conditional Formatting will do what you want.

Starting in A1, select all cells to test, lets say A1:M800

Goto Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =COUNTIF($A$1:$M$800,A1)>1
Click Format
Select the pattern tab
Choose a good highlighting colour
exit out

All duplicates will now show in that colour.

Change the range to suit in the formula.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mj" <[email protected]> wrote in message
news:[email protected]...
> Hello guys!
>
> I'm really new in using formulas in excel. I badly needed help in
> finding duplicates in my workbook.
>
> Here's the scenario: I have one workbook containing 5 worksheets. Each
> worksheet has 10-15 columns of data with 600-800 records (row). With
> this, I'm having a hard time tracking down the duplicates that
> occurred in the whole worksheet. If I'll do it manually it will take
> me ages to finish it and I don't have the time to do it. Is there an
> easier way to find or highlight duplicates in the whole worksheet and
> compare it to the remaining worksheets so I could only have unique
> records?
>
> I tried using the countif, IS and MATCH functions (conditional
> formatting) in 2-column data but I don't know if the functions would
> work in such amount of records. I'm not familiar with macros
> either...:-(
>
> If someone could help me, I would deeply appreciate it.
>
> Thanks in advance!
>

[mj]

Thanks for the replies.

Mr./Ms. Rsenn, honestly I haven't tried the vlookup function since
I'm a newbie when it comes to excel formulas. From what I understood,
you're suggesting that I must create a primary key for my data
through concatenation of two or more columns but what if I can't do
that since my columns belong to the same category? In my case, these
records are record nos. of my collection in different subfolders.

For your information my collection/file is arranged as follows:

1. Worksheets are named according to folder (i.e. main folder 1, main
folder 2....main folder 5)
2. Columns are subdivided into subfolder per main folder (i.e.
subfolder 1, subfolder 2...subfolder 10)
3. Columns contain record nos. of my collection

As for the COUNTIF function, as Mr. Bob suggested, I tried but I'm
having problems with it. I don't know if it's my trusty PC (hahaha)
or because of the amount of data I'm working with. The problem is
whenever I scroll up and checks for the duplicates it's so slow or
lagging, with this I have to restart my PC then when I open the file
again it stop responding (sigh).

I'm working on very large amount of data and I really want to know
the occurrences of duplicated record nos. in all the subfolders and
main folders.

Please enlighten me...

[wjohnson]

Assume the following - Column A is what you want to find the dups in.
In 1st sheet insert a column next to the Column A. In Column B insert the following and copy down - Sheet 1 (or whatever you call it). Then copy and paste Columns A and B into a blank Sheet (sheet 6)
Do the same for Sheets 2 thru 5 pasteing the information into columns A and B of sheet 6.
Sort Column A and insert a "Blank Row" at row 1.
Then in Cell C2 enter the following formula:
=IF(A2=A1,"Duplicate","Not Duplicate") and then copy the formula down.
You will now be able to see where the dups are and what sheet they are located on.
The cells have to be "exactly" the same or you will get a Not Dup return (even extra spaces) will affect the dup or not dup entry.