CODEWITHSUNDEEP
excel
fresh
fresh

Reputation: 140

Comparing two data sets for matches and nonmatches

I have data in two different sheets. Sheet1.A will contain an alphanumeric entry "ABC123" and Sheet2.A will contain a similar entry "ABC123 some text" or "some text ABC123"

Additionally Sheet1 will always have less entries than Sheet2, therefore there will be nonmatches.

In Sheet3 I want to be able to display all the entries for Sheet1.A with its corresponding match from Sheet2.A and then for all non matches, I would like them to be displayed at the bottom of the list.

Example of ideal output:

Sheet3.A  Sheet3.B
ABC123    ABC123
ABC222    ABC222
ABC333    ABC333
          ABC444
          ABC555
          ABC666

Currently I am using an index match (with a LEFT function) formula for Sheet3.B but does not produce an ideal output:

Sheet3.A  Sheet3.B
ABC123    ABC123
ABC222    ABC222
ABC333    ABC333
          ABC444
          ABC444
          ABC444

Also because I am using a LEFT function and the data in Sheet2.A may not be arranged similar to Sheet1.A, some entries are not being found, thus producing #N/A

I would also like to add that Sheet2.A may contain more than 256 characters which is causing problems for the index match function. This issue is not a top priority but if it can be resolved as well, that would be great.

Edit:

Question and Accepted answer now properly reflect one another

Upvotes: 1

Views: 1176

Answers (1)

David Zemens
David Zemens

Reputation: 53663

You could probably use the .Find method, searching for partial matches.

Sub FindPartialString()

Dim wsList As Worksheet
Dim wsSearch As Worksheet
Dim wsOutput As Worksheet
Dim lastRow As Long
Dim rngList As Range
Dim rngMatch As Range
Dim cl As Range
Dim arrNonMatches() As Variant
Dim nonMatchCount As Long


Set wsList = Sheets(1) '## Modify as needed
Set wsSearch = Sheets(2) '## Modify as needed
Set wsOutput = Sheets(3) '## Modify as needed
Set rngList = wsList.Range("A2:A5") '## Modify as needed

For Each cl In rngList
    Set rngMatch = Nothing 'clear the container before each query
    'look for a partial match:
    Set rngMatch = wsSearch.Cells.Find(What:=cl.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

    'Store the matches and non matches in separate arrays:
    If Not rngMatch Is Nothing Then
        lastRow = 1 + Application.WorksheetFunction.CountA(wsOutput.Range("A:A"))
        'put the searched value in column A:
        wsOutput.Cells(lastRow, 1) = cl.Value
        'Put the found value in column B:
        wsOutput.Cells(lastRow, 2) = rngMatch.Value
    Else:
        'store non-matches in an array
        ReDim Preserve arrNonMatches(nonMatchCount)
        arrNonMatches(nonMatchCount) = cl.Value
        nonMatchCount = nonMatchCount + 1
    End If
Next

'Print out the non-matches
lastRow = lastRow + 1
wsOutput.Cells(lastRow, 1).Resize(UBound(arrNonMatches) + 1, 1).Value = Application.Transpose(arrNonMatches)
End Sub

Upvotes: 1

Related Questions