Pinpointing Dup data

[ntruong]

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

 

[Joe4]

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)

 

[HalfAce]

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.

 

[ntruong]

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

 

[Joe4]

jim???

 

[Greg Truby]

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,

 

[ntruong]

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

 

[HalfAce]

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

'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')

 

[Joe4]

'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!

 

[Greg Truby]

Maybe I should just make it easier for everyone and legally change my name to Jim!

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