Linked Questions

Popular Questions

How to map duplicate items to duplicate keys

Asked by At

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.

Related Questions