Extract data from Range which appear N times

[ionelz]

I have a Range B5:B100, values are General, text and/or numbers
A1=1 to 95 ...because in B5:B100 can be max 95 values
I want to know in C5:C100, values from B5:B100 which appear A1 times !

For example, If A1=5, list all which appear 5 times...

[hrlngrv]

Picky: B5:B100 spans 96 rows, not 95.

C5: =IFERROR(INDEX(B5:B100,MATCH($A$1,COUNTIF(B5:B100,B5:B100),0)),"")
C6: =IFERROR(INDEX(B$5:B$100,MATCH($A$1,INDEX(COUNTIF(B$5:B$100,B$5:B$100)*(COUNTIF(C$5:C5,B$5:B$100)=0),0),0)),"")

Fill C6 down into C7:C100. This is rather inefficient, but OK for one-off tasks or for small data sets like this. For repeated use and/or much larger data sets, it's more efficient to use supporting cells, specifically,

X5: 1
X6: =X5+1

Fill X6 down into X7:X100. Select Y5:Y101, type =FREQUENCY(INDEX(MATCH(B5:B100,B5:B100,0),0),X5:X100), hold down [Ctrl] and [Shift] keys and press [Enter]. That should enter this formula into Y5:Y101 as an array formula.

Z5: =IF(Y5=$A$1,1)
Z6: =IF(Y6=$A$1,MAX(Z$5:Z5)+1)

Fill Z6 down into Z7:Z100.

C5: =IF(ROWS(C$5:C5)<=COUNT(Z$5:Z$100),INDEX(B$5:B$100,INDEX(X$5:X$100,MATCH(ROWS(C$5:C5),Z$5:Z$100,0))),"")

Fill C5 down into C6:C100.