# Finding only triple occurances of duplicate items

1. ## Finding only triple occurances of duplicate items

Hi, there's rows A and B as follows:

A B
1 9
6 7
21 30
1 9
9 1
21 30
``Please Login or Register  to view this content.``
I am trying to display
``Please Login or Register  to view this content.``
C D
1 9
``Please Login or Register  to view this content.``
as the resolution .

By using the formula
"=IFERROR(INDEX(A\$1:A\$5,SMALL(IF(FREQUENCY(\$A\$1:\$A\$7*\$B\$1:\$B\$7/

(\$A\$1:\$A\$7+\$B\$1:\$B\$7),

\$A\$1:\$A\$7*\$B\$1:\$B\$7/(\$A\$1:\$A\$7+\$B\$1:\$B\$7))>1,ROW(\$A\$1:\$A\$7)-

MIN(ROW(\$A\$1:\$A\$7))+1),ROWS(\$1:1))),"") "

the resolution is
``Please Login or Register  to view this content.``
C D
1 9
21 30
``Please Login or Register  to view this content.``
As there are duplicates in upper example however desired answer is supposed to be only 1 and 9 in Rows C and D excluding 21 and 30 because they have occurred only twice not thrice.

Is there a way to achieve the desired resolution by editing or entering an entirely new formula or way?

thanks

2. ## Re: Finding only triple occurances of duplicate items

hmmm
use the formula button so you dont paste a formula over 4 lines....

not sure how your example came up to A7 when there are 6 lines of data

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Finding only triple occurances of duplicate items

Perhaps you need helper column, and the array formula is

put in D2 and entered as array and copied down

=IFERROR(INDEX(\$F\$2:\$F\$7,MATCH(0,COUNTIF(D1:\$D\$1,\$F\$2:\$F\$7)+IF(COUNTIF(\$F\$2:\$F\$7,\$F\$2:\$F\$7)>1,0,1),0)),"")

4. ## Re: Finding only triple occurances of duplicate items

There are currently 1 users browsing this thread. (0 members and 1 guests)