Solved: Finding repeated values/cells

[cavemonkey]

Hi

I've been asked to write a code that can allow the user to filter out any repeated values or cells in a spreadsheet and also count the number of times that particular value or cell has been repeated. I have no idea where to start. Finding a particular cell is easy but to filter out all repeating cells and to also count the number of times they are repeated is a little challenging to me.

Please help. Thanks

Attached is a example of what I'm hoping to achieve. Its a bit messy hopefully it won't further confuse anyone.

[mikerickson]

You might want to look at COUNTIF, SUMIF and similar functions.

=COUNTIF(A1:C4,"dog") returns the number of occurances of "dog" in A1:C4.

Advanded Filter in the Data-Filter menu, has "display uniques" and "copy to other range" options that you might find useful.

[cavemonkey]

Maybe I might explain myself wrongly. What I want to create is a code that is able to take out all possible occurances of that all repeated cells that can be found in the spreadsheet and count the nunber of occurances of them. So that particular cell might not always be "dog"

I hope this cleared up some doubts

[geekgirlau]

Mike was giving you an example of a method that can be used to count how many times a value appears within a range - obviously you need to adjust it to suit your requirements.

In your sample, you are using a list to check for specific values (the examples used are "fish" and "dog"). As Mike suggested, you could use this formula:

=COUNTIF($A$1:$F$13,A19)

in cell B19 to count the number of times "fish" appears within that range. You could then copy the formula down to the next cell to count the occurrences of "dog".