Duplicate values across two other worksheets

[kristell]

Good morning. I have a spreadsheet I have to update daily to find if any 'new names' appear in an existing spreadsheet.

The names are listed on two tabs:

Cleansing: Names are in col B

NHS Names are in col B

I copy names from the new report (downloaded from a CMS system) into a separate tab - All names for checking exist

I then want All names for checking exist to show if the name already exists in Cleansing: Names tab or NHS tab

I have tried a few variations e.g. two separate Conditional Formats:

=COUNTIF(Cleansing!$B$2:$B$511,B2)>0

=COUNTIF('NHS DUPES'!$B$2:$B$304,B2)>0 (not sure why I have added ' to the sheet name here!!)

With this - the bottom value doesn't appear highlighted unless I add another value (I added Ignore to one of the sheets and Ignore to the sheet with the duplicate formatting to get the last value highlighted.

It also sometimes misses some of the 'duplicates'

I am sure there must be an easier way - any ideas?

 

[etaf]

extend the range - maybe to B1000 for both
BUT - when it misses duplicates is that because of spaces ?
Often extractions from system can add a leading or triailing space

So instead of "name" you get "name " or " name"

that would be worth checking
haveing the 2 rules in conditional formatting - with different colours , would show where the duplicate was found - BUT of course if BOTH sheets have the item then it will not sure
you could just highlight different columns

=COUNTIF(Cleansing!$B$2:$B$511,$B2)>0 maybe apply to column C
=COUNTIF('NHS DUPES'!$B$2:$B$304,$B2)>0, maybe to column D

why NHS DUPES - sheet name ???

 

[kristell]

why NHS DUPES - sheet name ??? Yes sorry - Missed the DUPES bit out of the bit above

Not really bothered about the colours - that's just the way I set it up to start with. Will probably make both the dame colour!

I thought I had checked for spaces - but will try and again.

 

[kristell]

I increased the search range but can't work out why the bottom cell will only highlight if I add the word 'ignore' - (which I have also added to one of the other tabs) it is ignoring the last value in the list - this is from the All names for checking exist tab

Actual formula is:
=COUNTIF('NHS DUPES'!$B$2:$B$1000,$B2)>0
=COUNTIF(Cleansing!$B$2:$B$1002,$B2)>0

 

[etaf]

its the apply to range - you are choosing all the column B - so B1 to B????
BUT your formula is checking B2
and so if B2 is true it highlights B1 and NOT B2
either change the applied to range to
B2:B1000 or even B10000
Or change formula to
=COUNTIF('NHS DUPES'!$B$1:$B$1000,$B1)>0
=COUNTIF(Cleansing!$B$1:$B$1000,$B1)>0

Personally I very rarely use full column references as it may slow the spreadsheet down
with 365 version , as excel has 1,048,576 rows and so it will TEST ALL those 1million+ rows

 

[kristell]

Perfect - no need for 'ignore' any longer! Thank you so much!

 

[etaf]

you are welcome