Smart Detection of missing data

[RepetitiveLady]

Hi everybody,

I need to check and tally 2 different list of products.
Here is an example of it:

<table dir="ltr" border="1" bordercolor="#05ad00" cellpadding="2" cellspacing="1" height="154" width="448"><tbody><tr><td align="center" height="4" width="14%">

Fixed Database
​

</td><td height="4" width="14%">Daily List
</td></tr><tr><td style="vertical-align: top;">Button A White
</td><td style="vertical-align: top;">Button A White
</td></tr><tr><td style="vertical-align: top;">Button A Black
</td><td style="vertical-align: top;">Button B Pink
</td></tr><tr><td style="vertical-align: top;">Ribbon A White
</td><td style="vertical-align: top;">Ribbon A Black
</td></tr><tr><td style="vertical-align: top;">Ribbon A Black
</td><td style="vertical-align: top;">Thread A White
</td></tr><tr><td style="vertical-align: top;">Thread A White
</td><td style="vertical-align: top;">Cloth A Blue</td></tr><tr><td valign="top">Thread A Black
</td><td valign="top">
</td></tr></tbody></table>
As you can see from the table above, "Button B Pink" and "Cloth A Blue" is not in the Fixed Database list in column A.

Is there any way that i can make it show me which product is not in the Fixed Database? Maybe you can make highlight the product that is in column B but not in column A. Or make it extract the product into column C. Anything will do, as long as i can easily identify which product is missing.

Please do take note, that i do not want to highlight or extract anything from column A that is missing in column B. I only want to highlight or extract product from column B that is missing in column A.

Can anyone help me?

Cheers
- Cassandra -

 

[Legacy 14611]

Happy with a macro?

Sub missdat()
Dim a As Range, e As Range
Set a = Range("a1").CurrentRegion
With CreateObject("Scripting.Dictionary")
    For Each e In a.Resize(a.Rows.Count - 1, 1).Offset(1)
        .Item(e.Value) = Empty
    Next
    For Each e In a.Resize(a.Rows.Count - 1, 1).Offset(1, 1)
        If Not IsEmpty(e) And Not .exists(e.Value) Then
            e.Font.Color = vbRed
            e.Offset(, 2) = e.Value
        End If
    Next
End With
End Sub

 

[dscg]

Non macro placed in column C & copied down.

=IF(ISERROR(MATCH(B1,A:A,0)),"Missing","Listed")

 

[hiker95]

RepetitiveLady,

Excel Workbook
	A	B	C
1	Fixed Database	Daily List
2	Button A White	Button A White
3	Button A Black	Button B Pink	not found in column A
4	Ribbon A White	Ribbon A Black
5	Ribbon A Black	Thread A White
6	Thread A White	Cloth A Blue	not found in column A
7	Thread A Black
8
Sheet1

The formula in cell C2 copied down:
=IF(ISNA(MATCH($B2,A:A,0)),"not found in column A","")

 

[RepetitiveLady]

Hi,

Thanks everyone for the help.

I don't know about other people, but my personal preference will be the code provided by hiker95.

If i use, macro, i have to run it every time i want the to see the missing data. And that specific macro is affected by capital letter. Example: i have "cloth" in column A, and "CLOTH" in column B, the macro will detect "CLOTH" as missing, highlight it and extract it out.

dscg code is good too. But it show "Listed" when the product in column B is in Column A, this would be messy. It is good, but not my preferred style.

But at the end, i would like to thank everyone for helping out. Really appreciate it.

Thanks you so much.

Cheers
- Cassandra -