If Information Matches Other Spreadsheet

[richmcgill]

I have two spreadsheets..
Spreadsheet 1 Take Loan number column A and Social Security Number Column D.
Spreadsheet 2 Match to the loan number from spreadsheet 1 and search the row for a match to the Social Security Number on Spreadsheet 1. This goes beyond a VLOOKUP.
On spreadsheet 1 (Yes or No works) based on a match of loan number and the social security number (there are nine social security numbers on the other spreadsheet) from spreadsheet 2.

Report Number 1
Loan Number	First Name	Last Name	Social Security Number
88888	John	Jones	111111111
55555	Mary	Webb	222222222
66666	Tom	Luck	333333333
77777	Harry	Smith	444444444
11111	Jill	Maxwell	555555555
22222	Kyle	Comings	666666666
33333	Frank	Kilgore	777777777

If you notice there are nine cells with Social Security Numbers so I do not think a VLOOKUP would work here.

Report Number 1

Loan Number

First Name

Last Name

Social Security Number

Social Security Number

Social Security Number

Social Security Number

Social Security Number

Social Security Number

Social Security Number

Social Security Number

Social Security Number

Social Security Number

88888

John

Jones

55555

Mary

Webb

66666

Tom

Luck

77777

Harry

Smith

11111

Jill

Maxwell

22222

Kyle

Cummings

33333

Frank

Kilgore

 

[johnnyL]

Can you provide an example of what the desired output would be?

 

[richmcgill]

Something like this. I do not need to know what column it is in just if it is present or not by a Yes or No or a Match or No Match.

Report Number 1
Loan Number	First Name	Last Name	Social Security Number	Match?
88888	John	Jones	111111111	Match	8th Column over
55555	Mary	Webb	222222222	Match	12th Column Over
66666	Tom	Luck	333333333	Match	5th Column Over
77777	Harry	Smith	444444444	Match	4th Column Over
11111	Jill	Maxwell	555555555	No Match	Social Security Number is not present
22222	Kyle	Comings	666666666	No Match	Social Security Number is not present
33333	Frank	Kilgore	777777777	Match	7th Column Over

Loan Number	First Name	Last Name	Social Security Number	Social Security Number	Social Security Number	Social Security Number	Social Security Number	Social Security Number	Social Security Number	Social Security Number	Social Security Number	Social Security Number
88888	John	Jones	123456789	4654646841	15481616	4784646946	111111111	564464576	4654498646	486468464	4684648	546844
55555	Mary	Webb	334168486	16887646	49849813	688646846	164864116	66616469	5486946	1516461	222222222	46894864
66666	Tom	Luck	684864788	333333333	687681613	16348798	1234658764	53687463	343473684	35436413	484648984	45468466
77777	Harry	Smith	444444444	673734487	706696654	1345493687	821710957	723463310	662333787	658363302	879069063	703741705
11111	Jill	Maxwell	1341711629	990180174	1344528454	673195639	1891505605	710534304	1000320525	692283254	1141495825	702315307
22222	Kyle	Cummings	1101291285	1330581328	1363543495	2002340528	1478557798	1380310151	1319180628	1315210143	1535915904	1360588546
33333	Frank	Kilgore	1998558470	1647027015	2001375295	777777777	2548352446	1367381145	1657167366	1349130095	1798342666	1359162148

 

[johnnyL]

You mentioned 9 cells with SS#s, you are showing 10 cells?

Is vba code an option?

 

[richmcgill]

It should be 9.

VBA would work fine.

 

[johnnyL]

@richmcgill How about this:

Sub ArrayTestV2()
'
'   Variable Names
    Dim CheckForMatchLastRow                            As Long
    Dim CheckForMatchLoanNumberSearchCounter            As Long
    Dim SourceLoanNumberSearchCounter                   As Long
    Dim SourceLastRow                                   As Long
    Dim SourceRowOffset                                 As Long
    Dim TotalColumnsInCheckForMatchSocialNumberArray    As Long
    Dim TotalRowsInCheckForMatchSocialNumberArray       As Long
    Dim CheckForMatchSocialNumberSearchCounter          As Long
'
'   Sheet Names
    Dim CheckForMatchSheet                              As String
    Dim SourceSheet                                     As String
'
    Dim MatchIndicatorColumn                            As String
'
'   Array Names
    Dim CheckForMatchLoanNumberArray()                  As Variant
    Dim CheckForMatchSocialNumberArray()                As Variant
    Dim SourceLoanNumberArray()                         As Variant
    Dim SourceSocialNumberArray()                       As Variant
'
    CheckForMatchSheet = "Sheet2"                                                                           ' <--- Set this to correct sheet name
    SourceSheet = "Sheet1"                                                                                  ' <--- Set this to correct sheet name
    SourceRowOffset = 2                                                                                     ' <--- Set this to correct offset to start of data
    MatchIndicatorColumn = "E"                                                                              ' <--- Set this to column for Match results
'
    CheckForMatchLastRow = Sheets(CheckForMatchSheet).Range("A" & Rows.Count).End(xlUp).Row                 ' Find Last row of CheckForMatch sheet
    SourceLastRow = Sheets(SourceSheet).Range("A" & Rows.Count).End(xlUp).Row                               ' Find Last row of Source sheet
'
    CheckForMatchLoanNumberArray = Sheets(CheckForMatchSheet).Range("A2:A" & CheckForMatchLastRow).Value2   ' Establish CheckForMatchLoanNumberArray
    CheckForMatchSocialNumberArray = Sheets(CheckForMatchSheet).Range("D2:L" & CheckForMatchLastRow).Value2 ' Establish CheckForMatchSocialNumberArray
    SourceLoanNumberArray = Sheets(SourceSheet).Range("A3:A" & SourceLastRow).Value2                        ' Establish SourceLoanNumberArray
    SourceSocialNumberArray = Sheets(SourceSheet).Range("D3:D" & SourceLastRow).Value2                      ' Establish SourceSocialNumberArray
'
    TotalRowsInCheckForMatchSocialNumberArray = UBound(CheckForMatchSocialNumberArray, 1) - LBound(CheckForMatchSocialNumberArray, 1) + 1   ' Number of rows in SS array
    TotalColumnsInCheckForMatchSocialNumberArray = UBound(CheckForMatchSocialNumberArray, 2) - LBound(CheckForMatchSocialNumberArray, 2) + 1 '  Number of columns in SS array
'
'   At this point all values needed are loaded into arrays, Sheet Names & variables
'
    For SourceLoanNumberSearchCounter = 1 To UBound(SourceLoanNumberArray)                                  ' Source Loan number loop
        For CheckForMatchLoanNumberSearchCounter = 1 To UBound(CheckForMatchLoanNumberArray)                ' Check for Match Loan number loop
            If CheckForMatchLoanNumberArray(CheckForMatchLoanNumberSearchCounter, 1) = SourceLoanNumberArray(SourceLoanNumberSearchCounter, 1) Then ' If Match found then ...
                For CheckForMatchSocialNumberSearchCounter = 1 To TotalColumnsInCheckForMatchSocialNumberArray  ' Check for Match Social number loop
                    If CheckForMatchSocialNumberArray(CheckForMatchLoanNumberSearchCounter, CheckForMatchSocialNumberSearchCounter) = _
                                SourceSocialNumberArray(SourceLoanNumberSearchCounter, 1) Then                      ' If Social number matches then ...
                        Sheets(SourceSheet).Range("E" & SourceLoanNumberSearchCounter + SourceRowOffset) = "Match"  '   Indicate Match was found
                    End If
                Next
            End If
        Next
    Next
    Sheets(SourceSheet).Range(MatchIndicatorColumn & 1 + SourceRowOffset & ":" & MatchIndicatorColumn & _
            SourceLastRow).SpecialCells(xlCellTypeBlanks).Value = "No Match"                    ' Identify any remaining blank cells in match column as "No Match"
End Sub

 

[richmcgill]

This worked flawlessly. Thank you for your help!

 

[johnnyL]

You are welcome @richmcgill.