Excel duplicate cells

[darkside100]

I have a very large excel spreadsheet with 3 columns of identical type numbers.

I need to find if any of the cells in any of the 3 columns contain duplicate entries

I need to get a list of where the multiple entries are.

 

[cLFlaVA]

What do you mean "duplicate entries"?
Could you give a brief example?

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[darkside100]

cLFlaVA (Programmer)

If for example FAL23456 is in cell 1 of the first of the 3 columns, I need to find out if it appears anywhere else in the 3 columns.

I know I can do a find all on FAL23456 and it will show all instances in all worksheets

What I want is a list of any cell in the 3 columns which has a duplicate entry, in other words a list of all cells that have a duplicate somewhere in the 3 columns

 

[macropod]

Hi Darkside,

Suppose the value you want to test is in A1, and that your range spans A1:C10. In that case, you could use the formula:
=COUNTIF($A$1:$C$10,A1)
to tell you how many times the value in A1 appears in the range. Copy this formula across 3 columns and down 10 rows and you'd have a matrix showing the match count for each cell in the range.

Cheers

 

[PeterMoran]

Hi,

Checkout Chip Pearsons site for Duplicates on the Topic Index:

http://www.cpearson.com/excel/topic.htm

and then at John Walkenbach's site for a neat approach:

http://j-walk.com/ss/excel/usertips/tip073.htm

Good Luck!

Peter Moran

 

[SkipVought]

Hi,

You can use the PivotTable Wizard, drag the field heading in question to the ROW AREA, and ALSO into the DATA AREA as Count of fieldname.

Takes about 10 seconds to get the answer you want!

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]