1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel - Finding duplicate values from different sheets

Discussion in 'Windows & Other Software' started by BYTEr, 21 Mar 2018.

  1. BYTEr

    Wise Guy

    Joined: 2 Oct 2004

    Posts: 1,048

    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:

    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.
     
  2. BYTEr

    Wise Guy

    Joined: 2 Oct 2004

    Posts: 1,048

    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?
     
  3. BYTEr

    Wise Guy

    Joined: 2 Oct 2004

    Posts: 1,048

    So apparently you can use

    Code:
    =TRIM(CLEAN(text))
    
    Which seems to of worked.
     
  4. Namsnik

    Gangster

    Joined: 27 Jan 2007

    Posts: 245

    Location: Bucks

    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.
     
  5. Namsnik

    Gangster

    Joined: 27 Jan 2007

    Posts: 245

    Location: Bucks

    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.