Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare data in two columns to find duplicates. | Excel Discussion (Misc queries) | |||
2 workbooks - how do I find duplicate entries by comparing the two | Excel Discussion (Misc queries) | |||
Find largest alphanumeric value matching alpha criteria in databas | Excel Worksheet Functions | |||
Find and replace of word causes change of font formatting | New Users to Excel | |||
Find duplicates | Excel Discussion (Misc queries) |