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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,078
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

Active Member
Joined
Aug 4, 2003
Messages
261
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,078
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

jim??? :rolleyes:
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019
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

Active Member
Joined
Aug 4, 2003
Messages
261

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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:
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,078
Office Version
  1. 365
Platform
  1. Windows
'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:
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019
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).
 

Forum statistics

Threads
1,141,628
Messages
5,707,503
Members
421,511
Latest member
mgroah1

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
Top