How do you identify duplicates?

[Akuso]

I have 2 spreadsheets called "recovery rate" and "august". I have 5 digit account numbers in column B on the recovery rate sheet and 5 digit account numbers in column B on the august spreadsheet. I just need to identify what duplicate numbers there are between any of the numbers in the B columns between both spreadsheets. If there is a duplicate I just need the duplicate number somewhere I can see it. There shouldn't be many. I have looked this up extensively so I need a step by step method on how to do this. The B columns are not in any order. Please help. Thanks.

[NBVC]

You can create a formula in another column like so in the recovery rate sheet:

=Isnumber(Match(B2,'august'!B:B,0))

copied down. TRUE means a match exists...

you can do the same from the other sheet

=Isnumber(match(B2,'Recovery rate'!B:B,0))

[K m]

You could use the MATCH function

If used with CONDITIONAL FORMAT, MATCH could "highlight" the cells which are duplicates

[Akuso]

The formula may have worked. I get some "true" matches but I need to know what account number the true is for?

[Akuso]

That sounds perfect. What would the entire formula for that be?

[K m]

You could highlight the matches with Conditional Formatting.

The way you do that is go to the first cell you want to test.
Click Format/Conditional Formatting/Condition 1/Formula is Enter =Isnumber(Match(B2,'august'!B:B,0))
Change the conditional format of that cell to something like red text
Click OK

Copy the cell
Highlight the other cells in that column
Right Mouse click and PASTE SPECIAL/FORMAT Enter

[Akuso]

ok. I got it. I had to change your formula just a little. =Isnumber(Match(B2,'august'!B:B,0))..
changed to =Isnumber(Match(B5:B106,'august'!B:B,0))

so before i guess it was just trying to match cell B2 instead of all cells in column B like I needed....I think ; )

[K m]

No, Conditiional Formatting works one cell at a time, therefore if you wanted conditional formatting, your formula won't work.

Use the formula in my post from NBCV. Copy the conditional format down. The way to do that is to:
- Right Click on the cell you want to copy and select COPY
- Highlight the cells you want to have the same conditional format
- Right click PASTE SPECIAL/FORMAT Enter/Ok
=========================================

I am not sure the first statement is correct but the text in blue should work