Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mj
 
Posts: n/a
Default i need to find duplicates! ASAP

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!

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
rsenn
 
Posts: n/a
Default i need to find duplicates! ASAP


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.


--
rsenn
------------------------------------------------------------------------
rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050
View this thread: http://www.excelforum.com/showthread...hreadid=515598

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default i need to find duplicates! ASAP

Conditional Formatting will do what you want.

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

Goto FormatConditional 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" wrote in message
ups.com...
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!



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mj
 
Posts: n/a
Default i need to find duplicates! ASAP

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...

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
wjohnson
 
Posts: n/a
Default i need to find duplicates! ASAP


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.


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=515598

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare data in two columns to find duplicates. Dan Excel Discussion (Misc queries) 2 October 5th 05 04:42 AM
2 workbooks - how do I find duplicate entries by comparing the two queen on Excel Discussion (Misc queries) 2 September 20th 05 11:12 AM
Find largest alphanumeric value matching alpha criteria in databas Alison Excel Worksheet Functions 7 August 4th 05 06:59 PM
Find and replace of word causes change of font formatting jwa90010 New Users to Excel 4 July 22nd 05 08:10 PM
Find duplicates R. Choate Excel Discussion (Misc queries) 5 November 28th 04 11:14 PM


All times are GMT +1. The time now is 03:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"