Find duplicate data

[Jayne]

Hi, This should be simple. I have data (eg PPL0106AU) in columns A (Rows 2 to 104) & C (Rows 2 to 303) and need to identify which entries are in BOTH columns. I've entered in column B: =IF(MATCH(A2,C$2:C$303),A2,"No")
This forumula is resulting in a duplicate of column A in column B. Help with the formula please.

[FDibbins]

I would use =vlookup(A2,$C$2:$C$303,1,0)

You could perhaps use Conditional Formatting for this? Use that in a New Rule, and set the color, it will highlight the matches

[Jayne]

Thanks, I entered the suggested formula in column B and dragged it down. In every case it is returning an error value #N/A. Suggestions why?

[playaller]

Paste the below code in B2 and copy down. This will display the number of duplicates. If none will be blank. FDibbins code is showing the NA# because of the ones that do not have duplicates so it just needs to be adjusted to show blank if no duplicate's.

Displays duplicate count.

Please Login or Register  to view this content.

[ThirtyTwo]

Please Login or Register  to view this content.

[FDibbins]

Thanks, I entered the suggested formula in column B and dragged it down. In every case it is returning an error value #N/A. Suggestions why?

This is indicating that there are no matches

[Jayne]

Thanks FDibbins - I tested again and yes, when there is a match it does work.
And thank you ThirtyTwo as well, that one also works well.
Cheers all.

[FDibbins]

Great, maybe adjust to this, to eliminate the error messages...
=iferror(vlookup(A2,$C$2:$C$303,1,0),"")

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)