0

First question on StackOverflow, please tell me how to improve. Also new to programming so problem may be simple, took me multiple hours to solve

I have two columns contained in a table, column X and column Y. Column X contains keys, column Y contains items. The columns are related by row. The item on row Z in Y should be mapped to the key on the same row in X. There can be more than one unique item per key, but only one unique key per item. All values in both columns occur an unknown amount of times. The task is to come up with an efficient mapping that provides easy access to all unique items corresponding to a specific key.

Please see my solution below. The function is called teamgroup and is part of a larger program, so there are inputs from other functions. What has not been included in my code is that the table is sorted by column X and that unique keys from column X are stored in a separate array, keys. IsInArray is also a separate function.

'Set keys
For i = 1 To UBound(keys)

    With dict

        .Add keys(i, 1), tmp

    End With

Next i

'Group values by key
For Each kee In dict.keys

    For i = 2 To lastrow

        If kee = tbl.Range(i, keycol).Value Then

            If IsInArray(tbl.Range(i, itemcol).Value, dict.Item(kee)) Then

            Else
                ReDim Preserve tmp(1 To UBound(tmp) + 1)
                tmp(UBound(tmp)) = tbl.Range(i, itemcol).Value
                dict.Remove (kee)
                dict.Add (kee), tmp
            End If

        Else

        End If

    Next i

    ReDim tmp(0)

Next

Set teamgroup = dict

End Function

The data is as follows:

 X     Y
 56     6070
 56     6070
 55     6021
 55     6024
 56     6054
 0      6050

There is no required form of the output but it should allow easy access to the items mapped to a specific key. I believe the dictionary to be a good tool for that. In this instance, an input of "56" should output an array, or similar, containing 6070 and 6054.

The end result of my code is a dictionary with unique keys mapping to items containing arrays with unique values from column Y.

My feeling is that this is an inefficient and convoluted way to solve the problem, so I would like to receive input on how it could be done better.

Théodore Montel
  • 13
  • 3
  • Why not just use a `Dictionary` holding a key - `Dictionary` pair? – Comintern Feb 11 '19 at 18:56
  • 2
    A dictionary whose values are collections would also be natural. It does seem convoluted to constantly delete and then re-add keys. It would be helpful if you gave a sample of data in columns X,Y together with what the final output should look like. – John Coleman Feb 11 '19 at 19:10
  • @JohnColeman I have added a small sample of data that should give you a feeling for what it looks like. – Théodore Montel Feb 11 '19 at 19:40
  • @Comintern I would be very happy if you could show me an implementation of that :) – Théodore Montel Feb 11 '19 at 19:40
  • Are there any rows where the entire row is a duplicate? Keys might be duplicated -- but are values ever duplicate? – John Coleman Feb 11 '19 at 19:43
  • @JohnColeman Yes as a matter of fact I just added such an instance. Sorry about that – Théodore Montel Feb 11 '19 at 19:45
  • In that case, the idea of @Comintern of having a dictionary of dictionaries is better than mine of a dictionary of collections (since dictionaries are the closest thing VBA has to sets). But -- dictionaries might not preserve the order. Is the order of the values for a given key important? – John Coleman Feb 11 '19 at 19:47
  • @JohnColeman has a good basic example of how to use nested dictionaries [on this question](https://stackoverflow.com/q/32317527/4088852). – Comintern Feb 11 '19 at 19:51
  • @JohnColeman The order of the values is not important. – Théodore Montel Feb 11 '19 at 19:59
  • @Comintern Thank you for that. It does help a bit, however, I do not quite understand how to apply it in my case. I do not have any keys to input in the inner dictionary, nor do I have any need for two keys as far as I can see. – Théodore Montel Feb 11 '19 at 20:03
  • Just use the keys (kind of like a hashset). You can stick a `vbNullString` in the `Item` if you're not going to be using it. – Comintern Feb 11 '19 at 20:11
  • @Comintern Alright. I believe I understand what you mean. Am I right if I interpret your improvement to benefit me through 1)allowing for easy checking of duplicates in the nested dictionaries and 2)letting me add items to the nested dictionary without deleting the whole item and re-adding it? – Théodore Montel Feb 11 '19 at 20:27
  • Yep, you've got it. ;-) – Comintern Feb 11 '19 at 20:34
  • 1
    @Comintern Alright I understand it then, thanks! – Théodore Montel Feb 11 '19 at 20:41

0 Answers0