Searching for Duplicates Within Data Across Columns and Sheets 2

[kathildreth2003]

Hi,

I have over a thousand data records in an excel file that are 3 columns across ..
What I am needing to do is to search for the duplicates and sort them not only to show the duplicates but also the person that entered it onto the system so we are able to see where/ how and why this has been happening


Please help

 

[Fenrirshowl]

Assuming your data is in 3 consecutive columns, A-C, and in row 1 and below, you could enter a quick COUNTIF array function and AUTOFILTER the values greater than 1.

{=COUNTIF(A:C,A1:C1}

Type the function as normal and hold CONTROL and SHIFT when pressing RETURN (this gives it the {}). You can then copy it down all the rows. Use DATA Menu, Filters, Autofilter on this column and show values greater than 1.

 

[Rofeu]

Hi,

Fenrirshowl: your formula counts every entry in A:C that is equal to A1.

Kathildreth: you can use Fenrirshowl's technique, but use this instead (NOT as array formula):

=SUMPRODUCT(--(A1=A1:A2000)*--(B1=B1:B2000)*--(C1=C1:C2000))

Cheers,

Roel

 

[Fenrirshowl]

Roel

Thanks for that, I never realised before (then again, I've not used countif in that way before, preferring to concatenate all entries and compare to single strings). Worth a star in my book.

Fen

 

[Rofeu]

Cheers!

 

[firefytr]

You do not need to coerce an array when it is compared against others when using SUMPRODUCT. So you can shorten to ...

=SUMPRODUCT((A1=A1:A2000)*(B1=B1:B2000)*(C1=C1:C2000))

The only time you need to coerce - when doing a count or sum with SUMPRODUCT - is when the array is by itself.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Rofeu]

Hi,

although you do not NEED to coerce, it is better to do so. It takes Excel less time to compute the formula with the coercion in place.

Cheers,

Roel

 

[firefytr]

Do you use FastExcel to test your formula speeds?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Rofeu]

Hi,

no, I must honestly say, I've never heard of FastExcel.

I just use the formula in 50k rows and time it.

Cheers,

Roel

 

[firefytr]

I seriously doubt you'd find much - if any - difference. I would think it would be ever so slightly faster without the double unary minus signs, but I doubt the human eye would ever notice the difference. As far as it being better to use the coercion in this situation, that is just blatantly false. As far as I'm concerned it's just easier to type without typing that much. Call me lazy.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci