Macro: Lookup Max of comma separated values

[MaxPower33]

I wondering what the best approach might be to lookup multiple comma separated values to find the max value of each string.

I can do this by first splitting out the comma delimted values and using multiple lookup formula to retrieve a value for each and then max of the results.... but i'm wondering might a macro be better since data volume can be 100k+ lines.

The comma separated values (keywords) are reported on a sheet called 'Keywords' for 3 different values
Values are reported in col C, D, E as per below example

Sheet = "Keywords" -->

A B C D E 1 ID Geo Seg func occ 2 270784189 United States 180, 190, 200, 160, 300, 250 abc, ffff, ggg, aaa, bbb, hhh 400, 3000, 200, 10, 20 3 270783575 United States 101, 102, 103, 104, 105 iii, abcd, efgh, ijk 20, 10, 60, 800, 1000 4 270783576 EMEA 300, 250 iii, abcd, 20, 10, 60, 5 270783580 NAMER 101, 102 iii, abcd 800, 1000 <colgroup><col style="width: 21pt; mso-width-source: userset; mso-width-alt: 1024;" width="28"> <col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;" width="81"> <col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;" width="98"> <col style="width: 150pt; mso-width-source: userset; mso-width-alt: 7314;" width="200"> <col style="width: 132pt; mso-width-source: userset; mso-width-alt: 6436;" width="176"> <col style="width: 142pt; mso-width-source: userset; mso-width-alt: 6912;" width="189"> <tbody> </tbody>

<tbody>
</tbody>

Values are reported on a 2nd sheet (Lookup_Values) with a value against each combination of Geo+seg/func/occ

The lookup needs to lookup a concatination of Geo+seg/func/occ to find the corresponding value in Col D/I/N on the Lookup_Values sheet.

Sheet = "Lookup_Values" -->

A B C D E F G H I J K L M N 1 Geo Seg ID Lookup_Val Value Geo func Lookup_Val Value Geo occ Lookup_Val Value 2 United States 101 United States101 0.2 United States abc United Statesabc 0.1 United States 10 United States10 0.1 3 United States 102 United States102 0.5 United States ffff United Statesffff 0.22 United States 20 United States20 0.22 4 United States 103 United States103 0.3 United States ggg United Statesggg 0.33 United States 60 United States60 0.33 5 United States 104 United States104 0.2 United States aaa United Statesaaa 0.1 United States 200 United States200 0.1 6 United States 105 United States105 0.6 United States bbb United Statesbbb 0.15 United States 400 United States400 0.15 7 United States 106 United States106 0.7 United States hhh United Stateshhh 0.22 United States 800 United States800 0.22 8 United States 107 United States107 0.75 United States iii United Statesiii 0.25 United States 1000 United States1000 0.25 9 United States 108 United States108 0.85 United States abcd United Statesabcd 0.4 United States 3000 United States3000 0.4 10 United States 109 United States109 0.1 United States efgh United Statesefgh 0.5 EMEA 10 EMEA10 0.25 11 United States 110 United States110 0.15 United States ijk United Statesijk 0.36 EMEA 20 EMEA20 0.4 12 United States 160 United States160 0.25 EMEA abc EMEAabc 0.1 EMEA 60 EMEA60 0.25 13 United States 180 United States180 0.35 EMEA ffff EMEAffff 0.15 EMEA 200 EMEA200 0.4 14 United States 190 United States190 0.45 EMEA ggg EMEAggg 0.22 EMEA 400 EMEA400 0.25 15 United States 200 United States200 0.55 EMEA aaa EMEAaaa 0.25 EMEA 800 EMEA800 0.4 16 United States 250 United States250 0.65 EMEA bbb EMEAbbb 0.15 EMEA 1000 EMEA1000 0.25 17 United States 300 United States300 0.7 EMEA hhh EMEAhhh 0.22 EMEA 3000 EMEA3000 0.4 18 EMEA 101 EMEA101 0.2 EMEA iii EMEAiii 0.25 NAMER 10 NAMER10 0.5 19 EMEA 102 EMEA102 0.5 EMEA abcd EMEAabcd 0.4 NAMER 20 NAMER20 0.2 20 EMEA 103 EMEA103 0.45 EMEA efgh EMEAefgh 0.5 NAMER 60 NAMER60 0.1 21 EMEA 104 EMEA104 0.55 EMEA ijk EMEAijk 0.36 NAMER 200 NAMER200 0.2 22 EMEA 105 EMEA105 0.65 NAMER abc NAMERabc 0.15 NAMER 400 NAMER400 0.1 23 EMEA 106 EMEA106 0.7 NAMER ffff NAMERffff 0.22 NAMER 800 NAMER800 0.3 24 EMEA 107 EMEA107 0.45 NAMER ggg NAMERggg 0.25 NAMER 1000 NAMER1000 0.4 25 EMEA 108 EMEA108 0.55 NAMER aaa NAMERaaa 0.4 NAMER 3000 NAMER3000 0.5 26 EMEA 109 EMEA109 0.65 NAMER bbb NAMERbbb 0.15 27 EMEA 110 EMEA110 0.15 NAMER hhh NAMERhhh 0.22 28 EMEA 160 EMEA160 0.25 NAMER iii NAMERiii 0.25 29 EMEA 180 EMEA180 0.35 NAMER abcd NAMERabcd 0.4 30 EMEA 190 EMEA190 0.45 NAMER efgh NAMERefgh 0.5 31 EMEA 200 EMEA200 0.55 NAMER ijk NAMERijk 0.36 32 EMEA 250 EMEA250 0.65 33 EMEA 300 EMEA300 0.7 34 NAMER 101 NAMER101 0.2 35 NAMER 102 NAMER102 0.5 36 NAMER 103 NAMER103 0.45 37 NAMER 104 NAMER104 0.55 38 NAMER 105 NAMER105 0.65 39 NAMER 106 NAMER106 0.7 40 NAMER 107 NAMER107 0.45 41 NAMER 108 NAMER108 0.55 42 NAMER 109 NAMER109 0.65 43 NAMER 110 NAMER110 0.15 44 NAMER 160 NAMER160 0.25 45 NAMER 180 NAMER180 0.35 46 NAMER 190 NAMER190 0.45 47 NAMER 200 NAMER200 0.55 48 NAMER 250 NAMER250 0.65 49 NAMER 300 NAMER300 0.7 <colgroup><col style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;" width="35"> <col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;" width="90"> <col style="width: 48pt;" width="64"> <col style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;" width="112"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;" width="99"> <col style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" width="61"> <col style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;" width="112"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;" width="90"> <col style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;" width="55"> <col style="width: 89pt; mso-width-source: userset; mso-width-alt: 4352;" width="119"> <col style="width: 48pt;" width="64"> <tbody> </tbody>

<tbody>
</tbody>

So for example. When you lookup the values for the first ID (270784189), the results might report as this:

F G H Seg func occ 0.35, 0.45, 0.55, 0.7, 0.65 0.1, 0.22, 0.33, 0.1, 0.15, 0.22 0.15, 0.4, 0.1, 0.1, 0.22 <colgroup><col style="width: 117pt; mso-width-source: userset; mso-width-alt: 5705;" width="156"> <col style="width: 137pt; mso-width-source: userset; mso-width-alt: 6656;" width="182"> <col style="width: 106pt; mso-width-source: userset; mso-width-alt: 5156;" width="141"> <tbody> </tbody>

<tbody>
</tbody>

So for example, the final Max result for the first ID (270784189) would be:

I J K Max_Seg Max_func Max_occ 0.7 0.33 0.4 <colgroup><col style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;" width="62"> <col style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;" width="67"> <col style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;" width="60"> <tbody> </tbody>

<tbody>
</tbody>

These results don't necessarily have to report as comma delimted resutls as per example. Alternatively, they could be split as text to colums across 3 separate worksheets (1 for seg/occ/func). This may lend itself better to concatenting values with geo.

The max number of comma delimted values per seg/occ/func is 100.

Any suggestions much appreciated!
Thanks!!!

 

[MickG]

Results in columns "F to K" of Sheet "Keywords"
If results are correct, where do you want the resultd placed ???

[COLOR="Navy"]Sub[/COLOR] MG03Sep55
[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]
[COLOR="Navy"]Dim[/COLOR] Seg [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] func [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] occ [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] UnQ [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Keyword [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Rstr [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] oCol [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dstr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Lookup_Values")
 [COLOR="Navy"]Set[/COLOR] Seg = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
 [COLOR="Navy"]Set[/COLOR] func = .Range(.Range("G2"), .Range("G" & Rows.Count).End(xlUp))
 [COLOR="Navy"]Set[/COLOR] occ = .Range(.Range("L2"), .Range("L" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Set[/COLOR] Rng = Union(Seg, func, occ)
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        UnQ = Dn.Column & Dn.Offset(, -1) & Dn
            Dic.Item(UnQ) = Dn.Offset(, 2)
    [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Keywords")
 [COLOR="Navy"]Set[/COLOR] Keyword = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Keyword
    [COLOR="Navy"]For[/COLOR] Ac = 2 To 4
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] True
            [COLOR="Navy"]Case[/COLOR] Ac = 2: oCol = 2
            [COLOR="Navy"]Case[/COLOR] Ac = 3: oCol = 7
            [COLOR="Navy"]Case[/COLOR] Ac = 4: oCol = 12
        [COLOR="Navy"]End[/COLOR] Select
        Rstr = Split(Dn.Offset(, Ac), ",")
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Rstr)
            Dstr = oCol & Dn.Offset(, 1) & Trim(Rstr(n))
            oMax = Application.Max(oMax, Dic.Item(Dstr))
            s = s & Dic.Item(Dstr) & ","
        [COLOR="Navy"]Next[/COLOR] n
            Dn.Offset(, Ac + 3) = oMax
            Dn.Offset(, Ac + 6) = s
            oMax = 0
            s = vbNullString
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MaxPower33]

Results in columns "F to K" of Sheet "Keywords"
If results are correct, where do you want the resultd placed ???

Amazing!!
Yes, this seems to have done the job... really efficient too when i tested it with a large dataset .
I added a 4th Range to the code as per below.
I assume I could potentially add several more ranges in the same way if that was requried?...

I'm not entirely sure where best to report the results yet so in F to K (now G to J) is perfect for now.

Dim Rng As Range, Dn As Range, n As Long
Dim Seg As Range
Dim func As Range
Dim occ As Range
Dim ind As Range
Dim UnQ As String
Dim Keyword As Range
Dim Dic As Object
Dim Rstr As Variant
Dim oMax As Double
Dim oCol As Integer
Dim Ac As Integer
Dim s As String
Dim Dstr As String
With Sheets("Lookup_Values")
 Set Seg = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
 Set func = .Range(.Range("G2"), .Range("G" & Rows.Count).End(xlUp))
 Set occ = .Range(.Range("L2"), .Range("L" & Rows.Count).End(xlUp))
 Set ind = .Range(.Range("Q2"), .Range("Q" & Rows.Count).End(xlUp))
End With
Set Rng = Union(Seg, func, occ, ind)
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
    For Each Dn In Rng
        UnQ = Dn.Column & Dn.Offset(, -1) & Dn
            Dic.Item(UnQ) = Dn.Offset(, 2)
    Next
With Sheets("Keywords")
 Set Keyword = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With
For Each Dn In Keyword
    For Ac = 2 To 5
        Select Case True
            Case Ac = 2: oCol = 2
            Case Ac = 3: oCol = 7
            Case Ac = 4: oCol = 12
            Case Ac = 5: oCol = 17
        End Select
        Rstr = Split(Dn.Offset(, Ac), ",")
        For n = 0 To UBound(Rstr)
            Dstr = oCol & Dn.Offset(, 1) & Trim(Rstr(n))
            oMax = Application.Max(oMax, Dic.Item(Dstr))
            s = s & Dic.Item(Dstr) & ","
        Next n
            Dn.Offset(, Ac + 4) = oMax
            Dn.Offset(, Ac + 8) = s
            oMax = 0
            s = vbNullString
    Next Ac
Next Dn
End Sub

Thanks Mick, that's truely awesome!

 

[MickG]

I think you could add as many more ranges as you want.
Thanks for the reply
Regrds Mick