Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need help with finding duplicate entries
Hello,
I have a column that has over 2,500 entries and want to simply highlight it with ANY duplicate entries found. I have tried this formula =IF(COUNTIF($D$2:$D$2566, $D$2)1, TRUE, FALSE which I found on Chip Pearson's web site (http://www.cpearson.com/excel/duplicat.htm) and it only highlights the first cell. I KNOW there are more than one duplicates. The data range begins on column D, row 2, under the col. heading. Might there a "setting" under my "options" that I need to check on, or something? What am I doing wrong? TIA for your replies. Phil. may have numerous en |
#2
|
|||
|
|||
Need help with finding duplicate entries
If you are using the formula for conditional formatting, the dollar signs may
have to be removed from part of the formula to allow it to adjust as you copy it down the column. -- Gary''s Student "Phil" wrote: Hello, I have a column that has over 2,500 entries and want to simply highlight it with ANY duplicate entries found. I have tried this formula =IF(COUNTIF($D$2:$D$2566, $D$2)1, TRUE, FALSE which I found on Chip Pearson's web site (http://www.cpearson.com/excel/duplicat.htm) and it only highlights the first cell. I KNOW there are more than one duplicates. The data range begins on column D, row 2, under the col. heading. Might there a "setting" under my "options" that I need to check on, or something? What am I doing wrong? TIA for your replies. Phil. may have numerous en |
#3
|
|||
|
|||
Need help with finding duplicate entries
Hi,
I don't know what you mean by "as you copy down the column". I am using the "Conditional Formatting" tool/dialog, which is covered on the web page. Am I supposed to "copy down the column" instead? "Phil" wrote: Hello, I have a column that has over 2,500 entries and want to simply highlight it with ANY duplicate entries found. I have tried this formula =IF(COUNTIF($D$2:$D$2566, $D$2)1, TRUE, FALSE which I found on Chip Pearson's web site (http://www.cpearson.com/excel/duplicat.htm) and it only highlights the first cell. I KNOW there are more than one duplicates. The data range begins on column D, row 2, under the col. heading. Might there a "setting" under my "options" that I need to check on, or something? What am I doing wrong? TIA for your replies. Phil. may have numerous en |
#4
|
|||
|
|||
Need help with finding duplicate entries
The easiest way to do this I think is to use 'Advanced Filter', go for
'Unique Records Only' and put the filtered result in another column. This will do what it says and remove all duplicates in the new column so you can compare the two. Have a look here on Debra Dalgliesh's web site, she is the expert on this subject and gives clear tutorials on how to use it, http://www.contextures.com/xladvfilter01.html Regards, Alan. "Phil" wrote in message ... Hello, I have a column that has over 2,500 entries and want to simply highlight it with ANY duplicate entries found. I have tried this formula =IF(COUNTIF($D$2:$D$2566, $D$2)1, TRUE, FALSE which I found on Chip Pearson's web site (http://www.cpearson.com/excel/duplicat.htm) and it only highlights the first cell. I KNOW there are more than one duplicates. The data range begins on column D, row 2, under the col. heading. Might there a "setting" under my "options" that I need to check on, or something? What am I doing wrong? TIA for your replies. Phil. may have numerous en |
#5
|
|||
|
|||
Need help with finding duplicate entries
Hi Alan,
Unfortunately, I NEED to be able to see ALL of the duplicates in their native column, because I need to change the values in the next column. So I don't think that would be the way to go. BTW, I DID get it to work, but strangely enough, it is not consistent. IOW, in one instance, the col. has 5 KNOWN duplicates, but it only highlighted the last 4, or similarily, it will highlight the first 4. And sometimes it shows an item highlighted, but there are NO duplicates. (Fortunately, that doesn't happen alot, or I'd go crazy!) AND, whether I used this formula =COUNTIF($D$2:$D$2566,D2333)1 or this one =IF(COUNTIF($D$2:$D$2566,D2333)1, TRUE, FALSE) didn't make ANY difference. What do YOU think? "Alan" wrote: The easiest way to do this I think is to use 'Advanced Filter', go for 'Unique Records Only' and put the filtered result in another column. This will do what it says and remove all duplicates in the new column so you can compare the two. Have a look here on Debra Dalgliesh's web site, she is the expert on this subject and gives clear tutorials on how to use it, http://www.contextures.com/xladvfilter01.html Regards, Alan. "Phil" wrote in message ... Hello, I have a column that has over 2,500 entries and want to simply highlight it with ANY duplicate entries found. I have tried this formula =IF(COUNTIF($D$2:$D$2566, $D$2)1, TRUE, FALSE which I found on Chip Pearson's web site (http://www.cpearson.com/excel/duplicat.htm) and it only highlights the first cell. I KNOW there are more than one duplicates. The data range begins on column D, row 2, under the col. heading. Might there a "setting" under my "options" that I need to check on, or something? What am I doing wrong? TIA for your replies. Phil. may have numerous en |
#6
|
|||
|
|||
Need help with finding duplicate entries
Well, it is working correctly now. I made SURE that my cursor was on the
first cell (which in my case is D2), then I made SURE that ALL of the range was highlighted. THEN I did the conditional formatting. Now it works great. Thanks to all who replied. One MORE question, though. What would be the best way to just show the duplicates found? IOW, let's say I run the cond. form. and it finds 1200 out of 3000 records, and highlights them accordingly. Then, how could I just isolate those 1200 records? TIA again. "Alan" wrote: The easiest way to do this I think is to use 'Advanced Filter', go for 'Unique Records Only' and put the filtered result in another column. This will do what it says and remove all duplicates in the new column so you can compare the two. Have a look here on Debra Dalgliesh's web site, she is the expert on this subject and gives clear tutorials on how to use it, http://www.contextures.com/xladvfilter01.html Regards, Alan. "Phil" wrote in message ... Hello, I have a column that has over 2,500 entries and want to simply highlight it with ANY duplicate entries found. I have tried this formula =IF(COUNTIF($D$2:$D$2566, $D$2)1, TRUE, FALSE which I found on Chip Pearson's web site (http://www.cpearson.com/excel/duplicat.htm) and it only highlights the first cell. I KNOW there are more than one duplicates. The data range begins on column D, row 2, under the col. heading. Might there a "setting" under my "options" that I need to check on, or something? What am I doing wrong? TIA for your replies. Phil. may have numerous en |
#7
|
|||
|
|||
Need help with finding duplicate entries
"Phil" wrote:
.. One MORE question, though .. What would be the best way to just show the duplicates found? IOW, let's say I run the cond. form. and it finds 1200 out of 3000 records, and highlights them accordingly. Then, how could I just isolate those 1200 records? You could just try putting essentially the same* CF formula in an empty col to the right of your data and copy down, *slightly corrected version from what you posted originally, that is For example put in say, X2, fill down to last row of data: =IF(COUNTIF($D$2:D2,D2)1,TRUE,FALSE) Then just put a label into X1 and do a: Data Filter Autofilter on col X and filter out TRUE which would return only the duplicates. Select all the filtered rows (select the blue-colored row headers) and do a copy paste into a new sheet -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding duplicate cell entries in a column of data | Excel Discussion (Misc queries) | |||
problem tagging duplicate entries | Excel Worksheet Functions | |||
count duplicate (or, inversely, unique) entries, but based on a condition | Excel Worksheet Functions | |||
Preventing Duplicate Entries within a column | Excel Discussion (Misc queries) | |||
Add numbers for duplicate entries then delete | Excel Worksheet Functions |