finding duplicates and triplicates between two lists

For array enter this in C1 and fill across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Change criteria to =3 for triplicates.

	A	B	C	D
1	1	2	13	14
2	2	1
3	13	14
4	13	14
5	9	10

Bit of a messy description.
What do you expect the output to look like?
Here's a guess though I doubt its what you want.

in C1
=COUNTIF(A1:A12,A1)+COUNTIF(A1:A12,A1)
in D1
=COUNTIF(A1:A12,B1)+COUNTIF(B1:B12,B1)

This will list the occurrences of the numbers in A1 and B1

Or are you wanting a list of all numbers that occur more than once? In one column or two?

The onus is on you to describe the output EXACTLY as you want it.

Hi, Special-K

Below formula can find duplicates for example:

"=IFERROR(INDEX(A$1:A$6,SMALL(IF(FREQUENCY($A$1:$A$6*$B$1:$B$6/($A$1:$A$6+$B$1:$B$6),$A$1:$A$6*$B$1:$B$6/($A$1:$A$6+$B$1:$B$6))>2,ROW($A$1:$A$6)-MIN(ROW($A$1:$A$6))+1),ROWS($1:1))),"")"


A1 B1
1 2
2 1
3 6
3 6
By using above formula it projects 1 at C1 AND 3 at C2 ALSO 2 AT D1 AND 6 AT D2 but what iam looking for is a formula which only projects 3 at C1 and 6 at D1 excluding "1, 2" because 3 and 6 are at same row not like 1,2 and 2,1.

The below formula only projecets triple occurances for example:

A1 B1
1 2
2 1
1 2
3 6
3 6
3 6

"=IFERROR(INDEX(A$1:A$5,SMALL(IF(FREQUENCY($A$1:$A$5*$B$1:$B$5*$C$1:$C$5/

($A$1:$A$5+$B$1:$B$5+$C$1:$C$5),

$A$1:$A$5*$B$1:$B$5*$C$1:$C$5/($A$1:$A$5+$B$1:$B$5+$C$1:$C$5))>1,ROW($A$1:$A$5)-

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

By using above formula it projects both 1,2 and 3, 6 at C1,C2 and D1, D2 simultaneously , but my desired projection is only 3 at C1 and 6 at D1 excluding 1 and 2 because 3 and 6 are at same row.

Desperately waiting for appropriate solution from knowledgeable respected contributors.
Thanks

I still don't understand this, probably because you are separating duplicates (occurs twice) and triplicates (occurs three times) into two lists.

Let's start again.

Here is a ONE list of singles, duplicates (occurs 2 times), triplicates (occurs 3 times) and quadruplets (occurs 4 times).
NOTE: You only asked for duplicates and triplicates NOT quadruplets - I want to know how we should deal with qudaruplets (or more) if they ever occur.

A B
1 2
2 13
3 4
3 13
13 7
14 16
15 14
16 14
16 16

Occurs twice: 2 and 3
Occurs three times: 13 and 14
Occurs four times:16

Now what ouptut would you expect from the above data and in what columns?

Originally Posted by LAVA2

Hi, FlameRetired

That is the desired result as stated above as 13 is in C1 AND 14 is in D1 but while using the formula "=MIN(IF(COUNTIFS($A$1:$A$5,$A$1:$A$5,$B$1:$B$5,$B$1:$B$5)=2,A$1:A$5))" it not only displays 13 in c1 and 14 in d1 but it fills 13 & 14 all the way to c5 and d5 continuously , i think that formula needs some adjustment , please advise.

thanks

That is because the ranges are "locked" or absolute references. That formula only applies to the first set of numbers posted in #1.

LAVA2 is your profile current?

This would be much easier with new versions of Excel.

No. The formula I have that works with multiple duplicates and triplicates requires MODE.MULT. It isn't available until Excel 2010.

Still working on it.

For duplicates:
Try array entering this in C1 fill down and across column D until you get blanks. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

For triplicates array entered in C1 again.

Formula:

Please Login or Register  to view this content.