Finding duplicates between two lists

Are you sure about the cell addresses? They are all the same column. It also appears (by cell row numbers) you want the output to overwrite the source data.

What am I missing?

@ sandy

That was my first thought, too. OP's previous posts seem to indicate otherwise. I'm stumped.

If you mean like this

	A	B	C	D
1	2	6	2	6
2	5	4	5	4
3	7	8
4	6	2
5	5	4

Then try this formula in C2 fill down and across until you get blanks. A simpler way has not occurred to me.

Formula:

Please Login or Register  to view this content.

Also try array entering this one in C2 fill down and 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.

This is simpler. Also array entered.

Formula:

Please Login or Register  to view this content.

You're welcome. Thanks for the feedback.

Just include the additional range in column C, array enter in D1, fill down and across until you get blanks.

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F
1	2	9	15	2	9	15
2	3	6	7
3	2	2	4
4	5	5	6
5	2	9	15

Originally Posted by LAVA2

Hi, Dave what if there are more than 100 rows, it not working for even for 15 rows atleast

Extend the range in FlameRetired's formula.

PHP Code: 

=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))),"") 


Hi, Indi_Ra thank you for the answer, i have still got couple of questions still unsolved.

1. What if there are 300 rows for 3 coloumns A,B,C? FOR EXAMPLE:

Please Login or Register  to view this content.

the outcome should be like this

Please Login or Register  to view this content.

2. What if there are 300 rows for only 2 coloumns A,B?

Cheers

Are you array entering the formulas ... Ctrl + Shift + Enter?

Please attach your workbook.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

I hope you are not seriously expecting anyone to pick through this file and work out what you are trying to do. Please would you provide a much simplified version with your expected results clearly marked out? Thanks.

@LAVA2

You don't say you have many blank rows (cells).
Remove that empty row and all formulas work well.

You have this formula:

=IFERROR(INDEX(C$1:C$236,SMCLL(IF(FREQUENCY($C$1:$C$236*$D$1:$D$236/($C$1:$C$236+$D$1:$D$236),$C$1:$C$236*$D$1:$D$236/($C$1:$C$236+$D$1:$D$236))>1,ROW($C$1:$C$236)-MIN(ROW($C$1:$C$236))+1),ROWS($1:1))),"")

Of course that formula don't work!!!! That SMCLL should be SMALL

Next time copy, carefully, formulas from this site, don't write yourself.