Find value based on multiple criteria

[edgarsrom]

Hello,

I have got stuck completely with the following situation:

In sheet 1 I have information about 1) AN code(full part code), 2)part code 3) year part fits(from-to)

In sheet 2 I have only information for 2) part code and 3) year part fits(exact year)

question is: How do I find AN code in sheet 2 based on information I have(part code and exact year) from sheet 1? Also same AN code can fit for two same part codes therefore I would need to look by year(I think)

Example:

Sheet 1 data:
COL A: B: C: D: E: F: G: H:

AN CODE:	Part code	Table with years(from - to):
AN11001	11001	2000	2001	2002	2003	2004
AN11001	11001	2004	2005
AN11002	11002	1999	2000	2001	2002	2003	2004
AN11002	11002	2001	2002	2003	2004
AN1003	11003	2001	2002
AN1003	11003	2002

<tbody>
</tbody>

Sheet 2 data:
COL A: B: C:

Year:	Part code:	AN code:
2004	11001
2005	11001
2002	11002
2002	11003

<tbody>
</tbody>

Any ideas please?

Thanks in advance!

 

[MickG]

Try this for results in sheet2 column "C".

[COLOR="Navy"]Sub[/COLOR] MG25Sep19
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
    Ray = Sheets("Sheet1").Cells(1).CurrentRegion
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]For[/COLOR] Ac = 3 To UBound(Ray, 2)
        [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(n, Ac)) And Not .exists(Ray(n, Ac) & Ray(n, 2)) [COLOR="Navy"]Then[/COLOR]
            .Add Ray(n, Ac) & Ray(n, 2), Ray(n, 1)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] .exists(Dn.Value & Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
        Dn.Offset(, 2) = .Item(Dn.Value & Dn.Offset(, 1).Value)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[edgarsrom]

Thank you so much for this code!!! It works perfect!!! One last question when you get a minute, could you please describe in couple words how this code is working?

Really appreciate solution provided!