Trying to Look Up and compare multiple columns to return TRUE

[MikeXYZ7]

Hello

I am struggling to work out the logic to determine a match between 2 dates, 2 products and one customer?

What I am trying to do is run a macro or formula to determine from the list where the SAME customer name (col A), matches with a RED & BLUE product with the same delivery date.

In the table below, I would want rows 4 & 5 to be highlighted as a match! And all other rows to be ignored.

Is anyone able to help me solve this conundrum?

Kind regards

Mike



[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CUSTOMER NAME[/TD]
[TD]PRODUCT COLOUR[/TD]
[TD]DELIVERY DATE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cust 1[/TD]
[TD]RED[/TD]
[TD]FEB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cust 1[/TD]
[TD]RED[/TD]
[TD]FEB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Cust 2[/TD]
[TD]RED[/TD]
[TD]MAR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cust 2[/TD]
[TD]BLUE[/TD]
[TD]MAR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Cust 2[/TD]
[TD]RED[/TD]
[TD]MAR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Cust 3[/TD]
[TD]BLUE[/TD]
[TD]APR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Cust 3[/TD]
[TD]RED[/TD]
[TD]MAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Cust 4[/TD]
[TD]BLUE[/TD]
[TD]AUG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Cust 4[/TD]
[TD]BLUE[/TD]
[TD]AUG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Cust 4[/TD]
[TD]BLUE[/TD]
[TD]SEP[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG11Aug55
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, P [COLOR="Navy"]As[/COLOR] Range, Col [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Txt = Dn.Value & Dn.Offset(, 2).Value
    [COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
        .Add Txt, Dn.Offset(, 1)
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Txt) = Union(.Item(Txt), Dn.Offset(, 1))
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
   [COLOR="Navy"]Set[/COLOR] nRng = Nothing
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Col [COLOR="Navy"]In[/COLOR] Array("RED", "BLUE")
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] P [COLOR="Navy"]In[/COLOR] .Item(K)
            [COLOR="Navy"]If[/COLOR] P.Value = Col [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = P Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, P)
                [COLOR="Navy"]Exit[/COLOR] For
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] P
    [COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] nRng.Count = 2 [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Interior.Color = vbYellow
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Col
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MikeXYZ7]

Hi Mick

Thank you for such a quick response. I've just tried the solution but have hit a roadblock in that I haven't come across the "With CreateObject("scripting.dictionary")" . I can see what this is doing and it seems ideal for what i am trying to do but i am unsure how to add Microsoft scripting Runtime?

I'll do some research and then try again - if you can let me know how to do this, that would be great - I'm running Win 10, Excel 2016.

Kind regards

Mike

 

[MickG]

Because "Late Binding" is used Early vs. Late Binding you do not need to do anything, but if your running a "Mac" you will not be able to use this solution.
Other than that the code should work !!!

 

[MikeXYZ7]

Hi Mick

It does work! You are a superstar! Thank you! I put the code into a module initially and hit a runtime error - but tried again by putting the code into the worksheet and it worked perfectly!

Thank you for the support!

All the best

Mike

 

[MickG]

You're welcome