Find Duplicate Values Across Sheets Using Formula

[mahmoud-lee]

HI!
I own 3 tables contain some duplicate values
I want to fourth in the table separating those duplicate values
Possible solution would be by the formula

[venkat1926]

obviously you do not need a macro
that emans you have to have a few stelps
file returned to you
in shee1,sheet2 and sheet3 see column C(they are formlas)
copy this column in sheet1 2 3 to sheet5 one below te other (remember you have pasespecial - values NOT JUST PASTE
see the formula in B2 in sheet 5
copy thlis formla down
you will get duplicates. still three may be more than one same data
highlight the data in column B in sheet 5 only and do advance filter for getting unique data (hope you know that )
you will get what you are getting in col. C sheeset 5
do text to column to each of the cell in this collumn c you will get same as sheet 4

if you want on one stroke RECORD a macro taking these steps and tweak the macro

[mahmoud-lee]

Unfortunately, this is not that I want
But I want the result as in the table (sheet4)
Thank you

[mahmoud-lee]

I have found a formula check that I want
But I want to change it to (office 2003)
But I want to change it as it is
=IFERROR(IFERROR(IFERROR(INDEX(List1;MATCH(0;COUNT IF(D1:$D$1;List1)+IF(COUNTIF(List1;List1)>1; 0;1);0)); INDEX(List2; MATCH(0;COUNTIF(D1:$D$1;List2)+IF((COUNTIF(List2; List2)+COUNTIF(List1;List2))>1;0;1); 0))); INDEX(List3; MATCH(0;COUNTIF(D$1:$D1;List3)+IF((COUNTIF(List3;L ist3)+COUNTIF(List1;List3)+COUNTIF(List2;List3))>1 ; 0;1);0))); "")

[Excel Fox]

This gives the result as you indicated. It is based on venkat's method.

[mahmoud-lee]

Unfortunately I use (office 2003)
Is it possible to convert the formula that you have sent to (office 2003)

[venkat1926]

I think you hve not done the last step of txt to columns.
I have done that also
sheee5 col c and d are no differnt from sheet4.

I am sure the formulas can be used in excel 2003.

[mahmoud-lee]

Yes
your formula work well
But I mean, Mister Fox formula

[Excel Fox]

mahmoud, you've been in forums for quite long, and I can't imagine why you are not able to change a 2007 excel based formula to 2003 excel version.

The formula is this

=IF(ISERROR(LEFT(INDEX($A$2:$A$31,SMALL(IF($C$2:$C $31<>"",ROW(INDIRECT("1:"&ROWS($C$2:$C$31)))),ROW( INDIRECT("1:"&ROWS($C$2:$C$31))))),FIND("|",INDEX( $A$2:$A$31,SMALL(IF($C$2:$C$31<>"",ROW(INDIRECT("1 :"&ROWS($C$2:$C$31)))),ROW(INDIRECT("1:"&ROWS($C$2 :$C$31))))))-1)),"",LEFT(INDEX($A$2:$A$31,SMALL(IF($C$2:$C$31<> "",ROW(INDIRECT("1:"&ROWS($C$2:$C$31)))),ROW(INDIR ECT("1:"&ROWS($C$2:$C$31))))),FIND("|",INDEX($A$2: $A$31,SMALL(IF($C$2:$C$31<>"",ROW(INDIRECT("1:"&RO WS($C$2:$C$31)))),ROW(INDIRECT("1:"&ROWS($C$2:$C$3 1))))))-1))

and

=IF(ISERROR(MID(INDEX($A$2:$A$31,SMALL(IF($C$2:$C$ 31<>"",ROW($C$2:$C$31)),ROW(INDIRECT("1:"&ROWS($C$ 2:$C$31))))),FIND("|",INDEX($A$2:$A$31,SMALL(IF($C $2:$C$31<>"",ROW($C$2:$C$31)),ROW(INDIRECT("1:"&RO WS($C$2:$C$31))))))+1,255)),"",MID(INDEX($A$2:$A$3 1,SMALL(IF($C$2:$C$31<>"",ROW($C$2:$C$31)),ROW(IND IRECT("1:"&ROWS($C$2:$C$31))))),FIND("|",INDEX($A$ 2:$A$31,SMALL(IF($C$2:$C$31<>"",ROW($C$2:$C$31)),R OW(INDIRECT("1:"&ROWS($C$2:$C$31))))))+1,255))

both array formulas

[mahmoud-lee]

But I have a question
How do you separate the two columns in (sheet5)