Find repeating values that occur only X times

[paocoep]

How can I specify the frequency / occurrence of repeating values that need to be found in a column or across different columns?
For example, if a column had 1,2,3,3,4,4,4, I want Excel to only find values that repeat 3 times, and the answer should be 4.

Someone gave a very good tip on how to count duplicate values, but it's lacking the 'specify frequency' part, as the 'Mode' includes both 3 & 2 occurrences.

	A	B	C

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: center"]Values[/TD]

[TD="align: center"]Mode[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]15[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: center"]14[/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: center"]14[/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: center"]14[/TD]

[TD="align: center"]18[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]15[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: center"]15[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: center"]15[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="align: center"]11[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="align: center"]11[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]
[TD="align: center"]13[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD="align: center"]13[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]
[TD="align: center"]18[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]
[TD="align: center"]18[/TD]

[TD="bgcolor: #CACACA, align: center"]17[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: #CACACA, align: center"]18[/TD]
[TD="align: center"]16[/TD]

[TD="bgcolor: #CACACA, align: center"]19[/TD]
[TD="align: center"]19[/TD]

[TD="bgcolor: #CACACA, align: center"]20[/TD]
[TD="align: center"]20[/TD]

</tbody>

"I sorted column A just so it'd be easier to see whcih values should be returned.

Enter this formula in C2:

=IFERROR(MODE(A2:A20),"")

Enter this array formua** in C3 and copy down as needed:

=IFERROR(MODE(IF(COUNTIF(C$2:C2,A$2:A$20)=0,A$2:A$20)),"")

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER."



I hope I'm making sense! Help appreciated!

 

[paocoep]

Thanks!!!
By the way, do you know how I can apply these codes to counting alphabets instead of numbers?
I tried them on a column of alphabets as values but didn't work
And if that's possible, can the alphabets be all in one cell instead of across multiple cells in a column like the number values in the above chart?
For example, if a cell contained "ABCDEEEAB", can a code count how many times each alphabet appears in that cell?
Or would I have to at least separate them with space or something, like: "A B C D E E E A B"?

 

[Aladin Akyurek]

Thanks!!!
By the way, do you know how I can apply these codes to counting alphabets instead of numbers?
I tried them on a column of alphabets as values but didn't work {/quote]

[TABLE="width: 144"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Values
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]a
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Count
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]a
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]a
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Values
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]b
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]a
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]b
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]b
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]b
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]c
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]c
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]c
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]c
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]d
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]d
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]q
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]q
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]q
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]q
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]p
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]q
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]g
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]g
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

Letters, strings, number, or a mixture of them should not better. The formulas stay the same.

And if that's possible, can the alphabets be all in one cell instead of across multiple cells in a column like the number values in the above chart?
For example, if a cell contained "ABCDEEEAB", can a code count how many times each alphabet appears in that cell?
Or would I have to at least separate them with space or something, like: "A B C D E E E A B"?

Not sure what you mean; probably this:


[TABLE="width: 161"]
<TBODY>[TR]
[TD="class: xl65, width: 87, bgcolor: transparent"]Values
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]ABCDEEEABB
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Count
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Values
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]B
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]E
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

C1: 3

C3, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(MATCH(MID($A$2,ROW(INDIRECT("1:"&LEN($A$2))),1),MID($A$2,ROW(INDIRECT("1:"&LEN($A$2))),1),0),
ROW(INDIRECT("1:"&LEN($A$2))))=C$1,1))

C5, control+shift+enter and copy down:

=IF(ROWS($C$5:C5)<=C$3,
INDEX(MID($A$2,ROW(INDIRECT("1:"&LEN($A$2))),1),SMALL(IF(FREQUENCY(
MATCH(MID($A$2,ROW(INDIRECT("1:"&LEN($A$2))),1),MID($A$2,ROW(INDIRECT("1:"&LEN($A$2))),1),0),ROW(INDIRECT("1:"&LEN($A$2))))=C$1,
ROW(INDIRECT("1:"&LEN($A$2)))),ROWS($C$5:C5))),"")

 

[paocoep]

Thank you again!!
I got the first code (C3) to work in Excel, but the C5 code gave me an error.
I can't figure out how to attach a screen shot, so I'll explain:
The error highlighted "INDIRECT" in the C5 code after I pressed "control+shift+enter," and under the entire code there is a "ROW([reference])" notification... I hope this makes sense Do you know what these are?

Also, do you happen to know why neither of C3 & C5 codes for alphabet-counting works on Google Spreadsheet?
I get "#REF! Error: circular dependency detected."
The ones for number-counting work fine there, though. Thanks!!

 

[Aladin Akyurek]

Thank you again!!
I got the first code (C3) to work in Excel, but the C5 code gave me an error.
I can't figure out how to attach a screen shot, so I'll explain:
The error highlighted "INDIRECT" in the C5 code after I pressed "control+shift+enter," and under the entire code there is a "ROW([reference])" notification... I hope this makes sense Do you know what these are?

Perhaps a copy-and-paste error:

https://dl.dropboxusercontent.com/u/65698317/paocoep.xlsx

Also, do you happen to know why neither of C3 & C5 codes for alphabet-counting works on Google Spreadsheet?
I get "#REF! Error: circular dependency detected."
The ones for number-counting work fine there, though. Thanks!!

Not sure, but try to open the workbook on Google Spreadsheet.

 

[paocoep]

Thanks for the file! I'm gonna try to work with Sheet 2 with one alphabet per cell as Sheet 3's C5 code still doesn't seem to be compatible with Excel 2004 or Google Spreadsheet...
You have helped me so much! Is there like a 'vote' function or something with which I can 'up' your reputation?

 

[Aladin Akyurek]

Thanks for the file!

You are welcome.

I'm gonna try to work with Sheet 2 with one alphabet per cell as Sheet 3's C5 code still doesn't seem to be compatible with Excel 2004 or Google Spreadsheet...[/quote]

The 2nd formula with INDIRECT is probably too long for the Mac version...

[/quote]You have helped me so much! Is there like a 'vote' function or something with which I can 'up' your reputation?[/QUOTE]

Glad to be of help. And no, there is no vote function here.

 

[paocoep]

Aww too bad there's no vote function!

The xls file you gave me is working perfectly on Google Spreadsheet, but when I change the range for C5 on Sheet 2 (the one with one alphabet per cell), I get "#N/A error: Argument out of range:"

All I did was replace 20 in all of "$A$20" in your original C3 & C5 codes to 200 as I have to count 200 alphabets... (I did this of course after I put all those alphabets in the A column)

Am I doing something wrong?

 

[Aladin Akyurek]

Aww too bad there's no vote function!

The xls file you gave me is working perfectly on Google Spreadsheet, but when I change the range for C5 on Sheet 2 (the one with one alphabet per cell), I get "#N/A error: Argument out of range:"

All I did was replace 20 in all of "$A$20" in your original C3 & C5 codes to 200 as I have to count 200 alphabets... (I did this of course after I put all those alphabets in the A column)

Am I doing something wrong?

After an edit (e.g., changing the range size), you also need to confirm these formula again with control+shift+enter.