Pinpointing Dup data

ntruong

Active Member
Joined
Aug 4, 2003
Messages
261
Hello Experts,

Is there a formula to extract or flag duplicate data or entry in a column without using VBA?
Thanks a lot in advance,

Nee
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
You can use Formulas and/or Conditional Formatting to identify duplicate data.

One method is to use the COUNTIF function to identify any data that apears more than once.

For example, let's say that you have data in A1:A100. If you wanted to see ihow many times the value in A1 appears in the range, use this Formula:
=COUNTIF($A$1:$A$100,A1)
 
Upvote 0
Hello Nee,
Here are a couple of ideas to choose from.

1) jm14's conditional formatting suggestion.
Highlight the entire range or column.
Conditional Format with formula being: =COUNTIF(A:A,A1)>1 and format
to suit.

2) A formula to show the row a duplicate occurs in. (Needs to go into a separate
column and copy down.)
=IF(COUNTIF(A:A,A1)>1,ROW(INDEX(A:A,MATCH(A1,A:A,0),1)),"")

3) A formula to show the number of times a duplicate is repeated. (Needs to go into a separate
column and copy down.)
=IF(COUNTIF(A:A,A1)<2,"",COUNTIF(A:A,A1))

One or the other of these ought to get you what you need.
 
Upvote 0
Hello Jim and HalfAce,

Thanks a lot for the quick tip.
Jim: Your conditional format works just fine. thx!

HalfAce: Long time no see - hope you've been doing great.
I love your #3 tip for allocating the dups. Works just great!

Thankfully,
Nee
 
Upvote 0
Hiya Nee,

Saw your name flash by and thought I'd say "howdy". Looks like Dan & Joe have you all fixed up. One other tip would be that if you need to knock out dup's
  1. Take Joe's formula and before copying down and fix just the row on the front half of the range and set for one row and then copy down; i.e.=COUNTIF($A$1:$A1,A1)
  2. Then you can quickly select the column and to a Copy + Paste Values over the top.
  3. Then sort by this column, do a FIND for 2
  4. Then Shift+EndDown and knock out the duplicate rows.
(Sorting to the bottom and deleting can be a lot quicker than deleting using autofilter if you have lots of rows and the ones you're deleting are thoroughly intermingled w/ the ones you're keeping.)

Anyway, that's an awful long "howdy", isn't it?

Take care,
 
Upvote 0
Hello Greg,

Wow - so long! How are you doing?
Thanks for the add-on tip -- works beautifully as always.

Jim14 - if you still wonder -- my earlier thanks was meant to go to you of course.

Thanks again gentlemen. Monday's almost over! Enjoy anyway.

Nee
 
Upvote 0
Hello Nee, yes I'm doing fine, thanks.
I hope you are as well.

'jim14' is actually jm14 :biggrin:

(I also assumed his name was Jim for a couple of years untill I learned otherwise.
His name is Joe but to maintain his anonymity, now we just call him 'Cedric') :LOL:
 
Upvote 0
'jim14' is actually jm14

(I also assumed his name was Jim for a couple of years untill I learned otherwise.
His name is Joe but to maintain his anonymity, now we just call him 'Cedric')
Maybe I should just make it easier for everyone and legally change my name to Jim! :rolleyes:
 
Upvote 0
Maybe I should just make it easier for everyone and legally change my name to Jim! :rolleyes:
That'd be Jim Minsky, correct? :wink: Maybe we could go with your "Pirate Name" of Pirate Garrick the Fashionably Late? (seems just a tad bit wordy, though).
 
Upvote 0

Forum statistics

Threads
1,218,522
Messages
6,142,961
Members
450,456
Latest member
Exidur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top