CODEWITHSUNDEEP
vbaexcel
Paulo Roberto
Paulo Roberto

Reputation: 1548

Compare data in 2 columns with VBA or formula

I have 2 sheets with IDs, first and last name, extracted from two different databases.

I need to send the client a third sheet where it shows that ID 32721 of sheet 1, is equivalent to ID 32761 of sheet 2.

Make an DE x FOR all of this data.

How can I do this in excel? I have tried with formulas and I have not been able, is there any way to do this with VBA? If so, help me because I do not understand anything about VBA.

image

Result

I need to compare name + surname in both sheets and in a third sheet show the relationships between the IDs.

enter image description here

Upvotes: 2

Views: 183

Answers (2)

EEM
EEM

Reputation: 6659

This solution extracts to Sheet3 the following:

  1. Matching records and their relationship
  2. Records in Sheet1 not in Sheet2
  3. Records in Sheet2 not in Sheet1

Assumptions: There is a different number of records in the sheets, and the records are not sorted in both sheets. Records in Sheet1 are located at A1:C36 and records in Sheet2 are located at A1:C33

Formulas:

  1. Matching records and their relationship (located at A1:D33)

To extract the records that appear in both sheets use these formulas:

Sheet1 ID (FormulaArray in column A):

    =IFERROR( INDEX( ID.1,
    AGGREGATE( 15, 6,
    MATCH( Name.2 & LastName.2, Name.1 & LastName.1, 0 ),
    ROW() - ROW($1:$1) )), "")

Name (Formula in column B): 

    =IFERROR( INDEX( Name.1, MATCH( $A2, ID.1, 0 )), "")

Last Name (Formula in column C): 

    =IFERROR( INDEX( LastName.1, MATCH( $A2, ID.1, 0 )), "")

Sheet2 ID (FormulaArray in column D):

    =IFERROR( INDEX( ID.2, MATCH(
    INDEX( Name.1, MATCH( $A2, ID.1, 0 )) &
    INDEX( LastName.1, MATCH( $A2, ID.1, 0 )),
    Name.2 & LastName.2, 0 )), "")
  1. Records in Sheet1 not in Sheet2 (located at F1:H33)

To extract the records that only appear in Sheet1 use these formulas:

Sheet1 ID (FormulaArray in column F):

    =IFERROR(AGGREGATE(15,6,
    IF(ISERROR(MATCH( Name.1 & LastName.1, Name.2 & LastName.2, 0 )), ID.1, ""),
    ROW() - ROW($1:$1)),"")

Name (Formula in column G): 

    =IFERROR( INDEX( Name.1, MATCH( $F2, ID.1, 0 )), "")

Last Name (Formula in column H): 

    =IFERROR( INDEX( LastName.1, MATCH( $F2, ID.1, 0 )), "")
  1. Records in Sheet2 not in Sheet1 (located at J1:L33) To extract the records that only appear in Sheet2 use these formulas:

Sheet2 ID (FormulaArray in column J):

    =IFERROR(AGGREGATE(15,6,
    IF(ISERROR(MATCH( Name.2 & LastName.2, Name.1 & LastName.1, 0 )), ID.1, ""),
    ROW() - ROW($1:$1)),"")

Name (Formula in column K): 

    =IFERROR( INDEX( Name.2, MATCH( $J2, ID.2, 0 )), "")

Last Name (Formula in column L): 

    =IFERROR( INDEX( LastName.2, MATCH( $J2, ID.2, 0 )), "")

Where:

ID.1 = Sheet1!$A$2:$A$36

Name.1 = Sheet1!$B$2:$B$36

LastName.1 = Sheet1!$C$2:$C$36

ID.2 = Sheet2!$A$2:$A$33

Name.2 = Sheet2!$B$2:$B$33

LastName.2 = Sheet2!$C$2:$C$33

FormulaArrays are entered pressing CTRL+SHIFT+ENTER simultaneously, you shall see { and } around the formula if entered correctly

See AGGREGATE function, INDEX function, MATCH function.

Upvotes: 1

SteveES
SteveES

Reputation: 554

You should be able to use an INDEX MATCH array formula (hit Ctrl + Shift + Enter, rather than just Enter) in Sheet 1 to generate a list of IDs from Sheet 2. This link provides an excellent explanation of using INDEX MATCH on multiple criteria. (It will essentially do the same as VLOOKUP, but is more powerful). Your formula would look something like this:

{=INDEX('Sheet2'!$A$2:$C$13, MATCH(1, (B2='Sheet2'!$B$2:$B$13) * (C2='Sheet2'!$C$2:$C$13) ,0), 1)}

Where the above finds the row MATCH of B2 and C2 from Sheet1 in the ranges B2:B13 and C2:C13 respectively in Sheet2, then uses INDEX for that row and column 1 from Sheet2.

Upvotes: 3

Related Questions