agwalsh
asked on
Using CountIf for duplicates
Hi Folks
Just wanted to check if I'm on track with this solution. I have a file with 6 columns and I'm not sure if I have duplicates or not. My workaround has been to create a new field, which concatenates all the elements of each transaction and then uses conditional formatting to flag those which are duplicates. It seems to work :-) but should I also use a Count if with this to check and if so, how would I structure it?
Thanks
Just wanted to check if I'm on track with this solution. I have a file with 6 columns and I'm not sure if I have duplicates or not. My workaround has been to create a new field, which concatenates all the elements of each transaction and then uses conditional formatting to flag those which are duplicates. It seems to work :-) but should I also use a Count if with this to check and if so, how would I structure it?
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@RobHenson - will try that. Yes, I'm using the Show duplicates option :-) Thank you!
A
A
Please check the attachment for your query
The ID Filed Enter your suspicious duplicate data, then Click on Run then you can find how many records are duplicates, for example more than 2 time all are duplicates
Run.xls
The ID Filed Enter your suspicious duplicate data, then Click on Run then you can find how many records are duplicates, for example more than 2 time all are duplicates
Run.xls
ASKER
Thank you all very much!
to use countif to count the number of times a particiular value repeats in column A (for example) is to put the following in column B and copy down:-
=Countif($a$1:$a1, $a1)
Not the 'missing' dollar sign on the second part of the range.
If you have sorted column A you will find that column b now has a 1 for non-identical (first match) and then 2,3,4 etc for subsequent matches.
Hope this helps
Reg