0

I have a case where I have a pre-defined set of values/codes what in a combination make some message. I also have an incoming file that I have to analyze on the codes, compare the sets against predefined values. The problem is that the incoming file doesn't exactly match my library:

enter image description here

In my library I have set of 4 columns and each row has it's unique meaning. I want to get that meaning as a string to further proceed with calculations. The problem is that the incoming file is not consistent. It doesn't follow exact sequence as in pre-defined file.

I need to compare them regardless of sequence. After being matched I want to grab corresponding Meaning from Library and proceed working with Case in my file.

Any ideas how to implement it in VBA?

Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
mrGott
  • 1,066
  • 3
  • 18
  • 53
  • If I understand correctly, you want to match on distinct sets. In other words, `SOME CASE 3` should match to `SOME MEANING 6`, because they share the same unique set of values (`ERTY`, `FGHJ`, `GFOP`). Is this correct? – Zev Spitz Nov 10 '19 at 13:30
  • @ZevSpitz That's exactly what I'm trying to do :) The thing is that GFOP can be in the third column or in the fourth as for ex. Case 2, or can be exactly the same as Meaning 6 (which would be simple). – mrGott Nov 10 '19 at 13:35
  • If you have Excel 2016 or later, with the TEXTJOIN function, you can do this with worksheet functions. Or you could use these worksheet functions in a vba function to do the same thing. – Ron Rosenfeld Nov 11 '19 at 02:12

1 Answers1

1

Offhand, you could create a VBA function that would return a string representing the unique values.

If there is some character that will never be in the values, you could delimit the values with that character (such as _):

Function GetUniqueValuesString(rng As Range) As String
    Dim rngValues() As Variant
    rngValues = rng.Value

    ' The following line requires a reference to Microsoft Scripting Runtime
    ' (via Tools -> References...)
    Dim dict As New Scripting.Dictionary

    'The function only parses the first row of the range
    Dim i As Integer
    For i = 1 To UBound(rngValues, 2)
        dict(rngValues(1, i)) = 1 ' 1 here is a dummy value
    Next

    GetUniqueValuesString = Join(dict.Keys, "_")
End Function

(If every value is always the same number of characters, you could simply join them, without any delimiter.)

Using this function against a horizontal cell range:

=GetUniqeValuesString(A4:D4)

should return a string with only the unique values:

BGAA_TGHJ_WETY

If you apply this function to both the library rows and the file rows, you should be able to match on the function's returned value.

Note that the function has a few limitations that might need to be resolved:

  • It assumes the order of the values will be the same. In other words, BGAA, TGHJ, WETY will resolve to a different string than BGAA, WETY, TGHJ.
  • The function only joins the first row; other rows are ignored.
Zev Spitz
  • 13,950
  • 6
  • 64
  • 136