Find Duplicate Values Across Sheets Using Formula

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

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

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

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))); "")

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

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

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.

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

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

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

Thank you Mr. Fox
But I want to convert this formula also
This is the most important
=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))); "")

I am very sorry
Formulas do not work
Look at this

Here's the workbook, and the formulas are working absolutely fine.

Cross posted here

Hi mahmoud-lee,

You have been warned for several times for not providing the link while posting the same questions on different forums. I'm locking this thread for now. Please acknowledge what Peter posted at mrexcel and PM either me or Excel Fox.

Give me this value, and nothing
{=#VALUE!}