Find duplicate values in multiple sheets

  • I have 2 sheets that i need to find what the same valued cells are.


    1 sheet has names and i need to find the rows where those same names are on the 2nd sheet.
    The formats are different (upper-lower case) and there is additional text as part of the cells in the 2nd sheet. (i.e prod123.web.corp.com) and i just need to find the prod123 parts.


    thanks

  • Re: Find duplicate values in multiple sheets


    "(i.e prod123.web.corp.com) and i just need to find the prod123 parts. "
    it means the part before the fist point is used?
    Where the data is in the sheets: Which columns?
    To save time why not to prepare a short sample of your data

    Triumph without peril brings no glory: Just try

  • Re: Find duplicate values in multiple sheets


    A possibility can be to add extra column in the sheet where the values to find are (column A) and use next formula
    A2 =SUMPRODUCT(((LEFT(OCPtabvHost!$A$2:$A$50,FIND(".",OCPtabvHost!$A$2:$A$50)-1))=B2)*(ROW(OCPtabvHost!$A$2:$A$50)))
    the value displayed is the row.
    It can works only if the value appears once

  • Re: Find duplicate values in multiple sheets


    In the file uploaded all the results show as 0.
    I would prefer if it resulted in a highlight since I would then be able to filter for that color.

  • Re: Find duplicate values in multiple sheets


    If you paste =IF(LEN([@Host])>14,LEFT([@Host],(LEN([@Host])-13)),[@Host]) into a column in ocptabvhost, it will give you the name, or the ip address if the name isn't listed. Then on the other (first) sheet you add =MATCH((LOWER([@[System Name]])),Table2[Column210],0)+1, and it will give you the row number where the item is on the second sheet.


    Then you apply a conditional format to the sheet to add whatever color you wanted


    Hope this is what you're looking for.

  • Re: Find duplicate values in multiple sheets


    Quote from Rowddawg;680945

    If you paste =IF(LEN([@Host])>14,LEFT([@Host],(LEN([@Host])-13)),[@Host]) into a column in ocptabvhost, it will give you the name, or the ip address if the name isn't listed. Then on the other (first) sheet you add =MATCH((LOWER([@[System Name]])),Table2[Column210],0)+1, and it will give you the row number where the item is on the second sheet.


    Then you apply a conditional format to the sheet to add whatever color you wanted


    Hope this is what you're looking for.



    Im sorry but its not working for me at all. It just shows the formula in the cells. Could you possibly do it and upload it?

  • Re: Find duplicate values in multiple sheets


    Quote from Rowddawg;680956

    https://www.dropbox.com/s/dq9m…013%20Cust-samplerev.xlsx Here's the link to the file... Turns out you have to actually type in the formulas I gave you, not just copy/paste. Hope this helps.


    Thank you i think it'll work well.
    Is it possible to then mark (highlight) the names in the second sheet that did in fact match with something from the 1st sheet.
    I also need to create a separate sheet with names that are extra on each side.

  • Re: Find duplicate values in multiple sheets


    https://www.dropbox.com/s/dq9m…013%20Cust-samplerev.xlsx


    See if this is what you want for the highlight... added a column basically doing the same thing as in the first worksheet, but in reverse, then highlighted the row based on if the result was a row number using conditional formatting. For that, I added a format for the C column on the second worksheet, then applied it to the first three cells.


    In case you're wondering about the +1 or +375 in the formulas, that just offsets the row that the tables start on.


    Let me know.



    Anyway, see if this is what you want.

  • Re: Find duplicate values in multiple sheets


    Im trying to use =IF(LEN(Table2[[#This Row],[Host]])>14,LEFT(Table2[[#This Row],[Host]],(LEN(Table2[[#This Row],[Host]])-13)),Table2[[#This Row],[Host]])
    on the rest of my tabs (they weren't ready before) and it's taking the data from tab2. When I change it Table2 to Table3, its not working.


    =IF(LEN(Table3[[#This Row],[Host]])>14,LEFT(Table3[[#This Row],[Host]],(LEN(Table3[[#This Row],[Host]])-13)),Table3[[#This Row],[Host]])
    Says there is an error.


    Along with that, =MATCH((LOWER(Table1[[#This Row],[System Name]])),Table2[Column1],0)+1
    isnt working for the other tabs when i change it to Table3 .. etc.

  • Re: Find duplicate values in multiple sheets


    Can you attach the worksheet that's adjusted? That way I can see exactly what you're talking about and better help. I'm assuming that when you're moving them it's changing the reference point and that's why you're getting different results, but with the sheet I can give you a specific answer.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!