match text combinations across multiple columns

[dinosaursandbugs]

Hello! I am trying to find matches between multiple columns of text and having a terrible time getting the formula correct.

I had 2 individuals rate items using 2 letter codes. They could give each item between 1 and 4 different two-letter codes (with 9 unique two-letter code options available --these are the same for all items). I want to know which codes (if any) are the same between raters for each item.

Here is some sample data.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]rater 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rater 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]code1[/TD]
[TD]code2[/TD]
[TD]code3[/TD]
[TD]code4[/TD]
[TD]code1b[/TD]
[TD]code2b[/TD]
[TD]code3b[/TD]
[TD]code4b[/TD]
[TD]common codes[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]cv[/TD]
[TD]le[/TD]
[TD]bw[/TD]
[TD][/TD]
[TD]le[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ot[/TD]
[TD]cv[/TD]
[TD][/TD]
[TD][/TD]
[TD]ot[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ge[/TD]
[TD]rj[/TD]
[TD]le[/TD]
[TD]cv[/TD]
[TD]ge[/TD]
[TD]le[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]ot[/TD]
[TD]bw[/TD]
[TD][/TD]
[TD][/TD]
[TD]cv[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]ot[/TD]
[TD]cv[/TD]
[TD][/TD]
[TD][/TD]
[TD]cv[/TD]
[TD]le[/TD]
[TD]ot[/TD]
[TD]bw[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I want out of this is a column listing the common codes between raters for each item (when there is a common code(s)).
For example, using the above sample data, I would like the next column J to display "le" in J3, "ot" in J4, "ge,le" in J5, etc.

The tricky part is that the items have different numbers of codes for each rater. In some cases there are no similar codes between raters (ex. row 6), in some cases 1 code is similar (ex. row 3), and sometimes more than one code is shared (ex. row 5 and row 7). The ordering of the codes does not matter, I just need to know what codes in columns B through E match with a code or codes in columns F through I.

I've been fiddling with INDEX & MATCH codes, but nothing is working yet. Thank you in advance for any advice! Otherwise I'll be doing this manually for all 11,000+ items and I don't want to think about how long that would take me!

 

[=ODIN=]

I don't think you'll be able to accomplish this without some serious array formulas, or a load of creative helper columns. Ideally you'd have set up your data in such a way that it could be processed easily later.

Someone smarter than me might have a better idea than this, but a macro seems like the faster solution.

-Do you have any idea how to use a macro if someone writes a quick an dirty one for you?
-How accurate is that sheet layout you showed? I'm toying with writing you a quick macro, but i'd just hard code in the column references and don't want to do that if they're not accurate.

 

[dinosaursandbugs]

Hi Odin,

I have limited experience with using macros but I am more than willing to fiddle around with anything you can come up with. I can easily make my actual data set fit the layout above with the same column/row references. I would love to see any macro you come up with. I'm also glad that I'm not crazy and wasn't missing a simple solution with array formulas. Thanks for your help!

 

[=ODIN=]

Assuming your data:
-is exactly as above (2 header rows and 9 columns wide starting in A1)
-you run the macro when the sheet you want it to operate on is active

this code might work:

Sub theRaters()
    Dim lR As Long, cS As String, pS As String
    Dim commA As String
    
    lR = Range("a" & Rows.Count).End(xlUp).Row
    
    
    For q = 3 To lR
        cS = ""
        pS = ""
        commA = ""
        
        For w = 2 To 5
            If Cells(q, w) <> "" Then
                cS = cS & Trim(LCase(Cells(q, w))) & "//"
            End If
        Next w
        
        For e = 6 To 9
            If Cells(q, e) <> "" Then
                If InStr(1, cS, Trim(LCase(Cells(q, e))) & "//", vbTextCompare) <> 0 Then
                    pS = pS & commA & Trim(LCase(Cells(q, e)))
                    commA = ","
                End If
            End If
        Next e
        
        Cells(q, 10).Value = pS
    Next q
    
    
End Sub

 

[dinosaursandbugs]

Odin, your macro worked beautifully. This goes to show that I'm going to have to go beyond basic formulas and start learning how to write those myself! Thank you so much for your help