ExcelBanter - Find and display duplicate data

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Find and display duplicate data (https://www.excelbanter.com/excel-discussion-misc-queries/249363-find-display-duplicate-data.html)

Find and display duplicate data

Hi,

I have the following data:

Sheet1 Sheet2

53446 Query+, 00053353446
53447 Query+, 00055353447
53448 Query+, 00053353448

The last 5 digits from each column on sheet2 are the same numbers as the
colums on sheet1. Is there any possible way that a formula or an add-on be
applied to this to display these duplicate results????

Please help

Thanks

Find and display duplicate data

Suppose you have the numbers in Sheet2 ColA..
With the number in Sheet1 cell A1 try the below formula which will retrun
"Duplicate" if present in sheet2

=IF(SUMPRODUCT(--(RIGHT(Sheet2!$A$1:$A$100,5)=""&A1)),"Duplicate")

If this post helps click Yes
---------------
Jacob Skaria

"Plebless" wrote:

Hi,

I have the following data:

Sheet1 Sheet2

53446 Query+, 00053353446
53447 Query+, 00055353447
53448 Query+, 00053353448

The last 5 digits from each column on sheet2 are the same numbers as the
colums on sheet1. Is there any possible way that a formula or an add-on be
applied to this to display these duplicate results????

Please help

Thanks