Comparing 2 lists of data that needs exact matches and duplicates only act as 1

Question

Comparing 2 lists of data that needs exact matches and duplicates only act as 1 duplicate not many

This is my table not what i am wanting to do is quite difficult to explain so please bare with me.

the first column which is column A is a list of expected numbers that I need. Column B is what I have I need to compare these 2 list for missing numbers because this list may contain hundreds of numbers and some may be duplicates of both columns which makes this difficult.

What I need is a formula or several to compare each list A with list B and find out what I am missing but every formula I use if i enter the number 137574 in to column B in a condition format or a vlookup formlua shows that i have all of the numbers from column B but as you can see from the table I only have 2 not the 3 that are required.

So basically or not so basically what I need from the formula is using the table above either output to column A or adding another column C and find out that I am missing number one of 137574 and 754628 in any way possible whether it be highlighted red in column A or adding the missing numbers to colum C.

Any help would be appreciated thank you in advance

117465 117465 137574 137574 137574 137574 137574 145637 145637 147846 147846 175485 175485 475632 475632 748659 748659 754628

Explanation / Answer

Try this macro:

Sub CompareLists()
    Dim r1 As Long
    Dim m1 As Long
    Dim r2 As Long
    Dim m2 As Long
    m1 = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:A" & m1).Interior.ColorIndex = xlColorIndexNone
    r1 = 1
    r2 = 1
    Do While Range("A" & r1).Value <> ""
        If Range("B" & r2).Value = Range("A" & r1).Value Then
            r2 = r2 + 1
        Else
            Range("A" & r1).Interior.Color = vbYellow
        End If
        r1 = r1 + 1
    Loop
End Sub

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.