Solution required VIA VBA and Formula

  • I have three Columns in sheet 1st have Dates 2nd ID's and third Names and same are in other sheet but are shuffle.


    And now i want to check with these three columns that which are similar.


    Please share solid solution to check similar Name ID's and Dates.


    attached below there are two different files please share formula to check and also VBA Code

  • You spoke of having your data in two different sheets, but provided two different workbooks. Which way would you like this to be addressed?


    If your needs allow you to combine the two workbooks into one, you can use conditional formatting to highlight matches.

    1. Move or Copy sheet from 2nd workbook into 1st workbook
    2. Select all three data columns in the first sheet
    3. Create a conditional formatting rule using the following formula:

    =NOT(ISERROR(MATCH(A1,'[Name of 2nd sheet]'!A:A,0)))


    Replace [Name of 2nd sheet] with the actual name of the 2nd sheet. Be sure to include the 'single quotes' around the sheet name if it includes any spaces. (It's not a bad idea to include them either way.)


    If you'd like still like VBA solution too, please clarify whether combining your two data sets into one workbook is acceptable.


    Cheers


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

  • ShoaibRajput1, I think we are having a communication challenge. It looks like your solution is already working. I am not completely clear about what you still need.


    If you'd like a VBA solution, you could record a macro in which you add the CONCATENATE and the VLOOKUP columns.


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

  • I need proper formula to do this activity first i am concatinating then putting Vlookup there will be any single formula which could help in this regard.

  • OK, if I understand correctly, you could use this single formula:


    =IFERROR(AND(MATCH($A2,[Book1]Sheet1!$A:$A,0)>0,$B2=INDEX([Book1]Sheet1!$B:$B,MATCH($A2,[Book1]Sheet1!$A:$A,0)),$C2=INDEX([Book1]Sheet1!$C:$C,MATCH($A2,[Book1]Sheet1!$A:$A,0))),FALSE)


    The output is TRUE if all three fields on the first sheet match the entries on a single row of the other worksheet, and FALSE if they do not.


    If you prefer, when a match is found you could output the row where the match occurs instead of the word TRUE:


    =IFERROR(IF(AND(MATCH($A2,[Book1]Sheet1!$A:$A,0)>0,$B2=INDEX([Book1]Sheet1!$B:$B,MATCH($A2,[Book1]Sheet1!$A:$A,0)),$C2=INDEX([Book1]Sheet1!$C:$C,MATCH($A2,[Book1]Sheet1!$A:$A,0))),"Row "&MATCH($A2,[Book1]Sheet1!$A:$A,0),FALSE),FALSE)


    Hope that gets you what you need.


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

Participate now!

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