If cell does not contain specific text, then return a that missing text

[Aharris90]

Hello,
I am hoping someone can help. I am trying to run a formula that will search a column for text and if that text does not match text within another column to then return the missing text to the bottom of the column the text is missing from.

So lets say Column A has 100 records and Column B has 75 records, I would like row 76 in column B to return the first missing text from Column A.

I hope that makes sense.

Thanks

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[RobP]

... and just for completeness in case you wanted to run a macro instead of using formulas each time...

Sub Main()

Dim lastqueryrow, lastcustomerrow As Long
Dim a, b As Long
Dim Found As Boolean

a = 2

lastqueryrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' find the last row of data in the datafile
lastcustomerrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row '

Do While a < lastqueryrow + 1  'loop through each customer in Col A
   
    Found = False

    For b = 2 To lastcustomerrow  'compare each with full list inCol B
   
        If ActiveSheet.Range(Cells(b, 2), Cells(b, 2)) = ActiveSheet.Range(Cells(a, 1), Cells(a, 1)).Text Then
            Found = True
            Exit For
        End If
   
    Next b

    If Found = False Then 'store value at bottom of the customer list
        ActiveSheet.Range(Cells(lastcustomerrow + 1, 2), Cells(lastcustomerrow + 1, 2)) = ActiveSheet.Range(Cells(a, 1), Cells(a, 1))
        lastcustomerrow = lastcustomerrow + 1
    End If
   
    a = a + 1
Loop

End Sub

 

[Aharris90]

... and just for completeness in case you wanted to run a macro instead of using formulas each time...

Sub Main()

Dim lastqueryrow, lastcustomerrow As Long
Dim a, b As Long
Dim Found As Boolean

a = 2

lastqueryrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' find the last row of data in the datafile
lastcustomerrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row '

Do While a < lastqueryrow + 1  'loop through each customer in Col A
  
    Found = False

    For b = 2 To lastcustomerrow  'compare each with full list inCol B
  
        If ActiveSheet.Range(Cells(b, 2), Cells(b, 2)) = ActiveSheet.Range(Cells(a, 1), Cells(a, 1)).Text Then
            Found = True
            Exit For
        End If
  
    Next b

    If Found = False Then 'store value at bottom of the customer list
        ActiveSheet.Range(Cells(lastcustomerrow + 1, 2), Cells(lastcustomerrow + 1, 2)) = ActiveSheet.Range(Cells(a, 1), Cells(a, 1))
        lastcustomerrow = lastcustomerrow + 1
    End If
  
    a = a + 1
Loop

End Sub

Thank you for your help. Might play around with this to learn as well. thanks again