Find repeating values that occur only X times

P

paocoep

New Member
Joined
Jun 27, 2013
Messages
10
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.

ABC

<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! :nya: Help appreciated!
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
C1: 3 (Frequency spec)

C3, control+shift+enter, not just enter:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF($A$2:$A$20<>"",
  MATCH($A$2:$A$20,$A$2:$A$20,0)),
  ROW($A$2:$A$20)-ROW($A$2)+1)=C$1,1))
 
Upvote 0
Thank you for reply Aladin :rolleyes:
Ahh I didn't know I can't insert non-URL images here to explain what's going on...

So I have to explain in words unless someone knows how to insert screen shots here :confused:

I put your new code in C3, and I put "=IFERROR(MODE(A2:A20),"")" in C2, but I got '#NAME?' for C2 and 0 for C3... I did control+shift+enter for C3 and just enter for C2, so I don't know what i did wrong?

I'm using 2004 Mac version, but it shouldn't matter, right...?

I tried this on Google Spreadsheet, too, but didn't work :stickouttounge:
 
Upvote 0
paocoep said:
Thank you for reply Aladin :rolleyes:
Ahh I didn't know I can't insert non-URL images here to explain what's going on...

So I have to explain in words unless someone knows how to insert screen shots here :confused:

I put your new code in C3, and I put "=IFERROR(MODE(A2:A20),"")" in C2, but I got '#NAME?' for C2 and 0 for C3... I did control+shift+enter for C3 and just enter for C2, so I don't know what i did wrong?

I'm using 2004 Mac version, but it shouldn't matter, right...?

I tried this on Google Spreadsheet, too, but didn't work :stickouttounge:
Click to expand...

I cannot make sense of this. The array formula I posted gives you a count of items with occurrence frequency equaling 3. Is that not what you were asking for in you original post?
 
Upvote 0
Aladin Akyurek said:
I cannot make sense of this. The array formula I posted gives you a count of items with occurrence frequency equaling 3. Is that not what you were asking for in you original post?
Click to expand...
If I had to guess, I would say that the 2004 Mac version of Excel, like the 2003 Windows version, does not have the IFERROR function available; hence, the #NAME! error.
 
Upvote 0
Rick Rothstein said:
If I had to guess, I would say that the 2004 Mac version of Excel, like the 2003 Windows version, does not have the IFERROR function available; hence, the #NAME! error.
Click to expand...

Thanks Rick. I now realize what the OP is after...

[TABLE="width: 144"]
<colgroup><col style="width: 48pt;" span="3" width="64"> <tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: white"]Values[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]12[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Count[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]12[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]12[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Values[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]14[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]12[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]14[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]14[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]14[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]15[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]15[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]15[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]15[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]11[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]11[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]13[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]13[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]18[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]18[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]10[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]16[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]19[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]20[/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[/TR]
</tbody>[/TABLE]


C1: 3 (an occurrence frequency of interest)

C3, control+shift+enter, not just enter:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF($A$2:$A$20<>"",MATCH($A$2:$A$20,$A$2:$A$20,0)),
  ROW($A$2:$A$20)-ROW($A$2)+1)=C$1,1))

C5, control+shift+enter and copy down:
Rich (BB code): 
=IF(ROWS($C$5:C5)<=$C$3,
  INDEX($A$2:$A$20,SMALL(IF(FREQUENCY(IF($A$2:$A$20<>"",
  MATCH($A$2:$A$20,$A$2:$A$20,0)),ROW($A$2:$A$20)-ROW($A$2)+1)=C$1,
  ROW($A$2:$A$20)-ROW($A$2)+1),ROWS($C$5:C5))),"")

Note. The foregoing does not invoke IFERROR at all.
 
Upvote 0
Ah sorry to bring this up kinda late, but could you please tell me where in Aladin's code I change to specify another frequency to count?
I mean, if I wanted to count numbers that show up twice instead of 3 times, how do I change the code?
Thank you :biggrin:
 
Upvote 0
paocoep said:
Ah sorry to bring this up kinda late, but could you please tell me where in Aladin's code I change to specify another frequency to count?
I mean, if I wanted to count numbers that show up twice instead of 3 times, how do I change the code?
Thank you :biggrin:
Click to expand...

C$1 is used to house that spec.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
169
admvlad
A
D
  • Solved
Repeat Range of Cells N times with each item in range repeating X times
Replies
5
Views
345
Cubist
Cubist
ibmy
  • Solved
Count number of occurence of value with condition
2
Replies
10
Views
245
Alex Blakenburg
A
K
  • Solved
Populate Number According to Repeat Frequency
Replies
4
Views
361
Peter_SSs
Peter_SSs
O
  • Solved
Macro to extract repeating invoice numbers and details
Replies
4
Views
223
Omar M
O

Forum statistics

Threads
1,223,270
Messages
6,171,102
Members
452,379
Latest member
IainTru

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top