Excel - Finding duplicate values from different sheets

[BYTEr]

Hi all,

Hoping someone can help.

I have 2 worksheets 'Sheet1' & 'Sheet2' in the same workbook.

In both sheets in column A I have rows of numbers, eg; ' 233572-1'.

What I want to know is if there are any identical values in column A from both sheets and highlight them, or return a value so I know the identical values.

I did initially use the following code:

=COUNTIF(Sheet2!$A:$A, A1)

In Sheet1 which returned a value which worked when I tested it by adding the same random numbers to both sheets but don't seem to be working on any of the real values I have?

I am using Excel 2010.

Thanks in advance.

 

[BYTEr]

Ok, so nothing wrong with the formula but the formatting of the cells, as when I type in the number the formula works, I've tried TRIM but this didn't fix it, I've tried formatting the cells the same etc but unless I manually retype the number it doesn't work, anyone have any ideas?

 

[BYTEr]

So apparently you can use

=TRIM(CLEAN(text))

Which seems to of worked.

 

[Namsnik]

Probably your issue is that some of your numbers are numbers and some are text. Unless you need to do maths on them then you should convert them all to text, especially if you have leading zeros in some of them.

To convert to text select a single column then Data>>Text to Columns>> Delimited>>Tab (tabs won't exist in your single column)>>Next>>Text
Or use a formula like clean to do the same thing
=match(trim(a1),D1:5000,0)

If you want to treat them as numbers then multiply them all by 1 to co-erce them to numbers:
type 1 in a spare cell. Copy this cell. select your data, Right-Click, Pastespecial, multiply
Or in a formula you can use a double negative:
=match(--A1,D1:5000,0)

If using Match(...) or a lookup function then whilst you can coerce the value you are matching to be a number or text the range you are matching to should all be converted to text or numbers in one of the ways I described above.

 

[Namsnik]

I just checked. Whilst my advice was good for functions like MATCH, VLOOKUP and HLOOKUP which need to match data type (number or text), COUNTIF/SUMIF make no distinction so your problem is most likely extra spaces or special characters at the beginning or end of the "number". For spaces, assuming that you would expect none you could use search and replace to clean both sets of data. Otherwise your solution is probably as good as any.