Query to compare string combination

[Erick]

I need a query to compare combination of concatenated string so that "A, B, C" compared to B, A, C will return True, whilst "A, B, C" compared to "B, A, A" will return false. Effectively if I compare 2 string records, it will look at the combination (comma delimited) and will return true if the comma delimited strings are the same, regardless of the position.

Can this be done without VBA?

 

[shg]

Not in any reasonable, extensible way.

 

[NiMip]

Is it always going to be three values, separated by a comma and a space? If you have any variation in the number of variables, it's going to be messy. If it's consistent, I think I could come up with a solution specific to that format.

EDIT: Also, no promises that it will be one formula. Separating the delimited values and using them in one formula is more painful than you might imagine.

 

[Erick]

I'm afraid the number of comma-separated elements will be variable. However, if I manage to get the maximum number of elements across all records and pad out all records with a nil assigned character so that they all records will end up having the same number of comma-separated elements, would this be doable?

Is it easier if the elements are represented as separate record?
ID | Category | Output
01 | CAT1 | 'A'
02 | CAT1 | 'B'
03 | CAT1 | 'C'
04 | CAT2 | 'B'
05 | CAT2 | 'A'
06 | CAT2 | 'C'

So in comparing CAT1 vs CAT2 above, it will return TRUE as both have the same combination of Outputs, just in different order.

 

[james_lankford]

maybe this is a starting point

Option Explicit


Sub start_here()
    
    Dim return_value As String
    Dim s As String
    s = "A, Q, B, C"
    
    return_value = build_where_clause(s)
    Debug.Print return_value


End Sub


Function build_where_clause(s As String) As String
    
    Dim sql As String
    sql = "( " & vbCrLf
    
    Dim v As Variant
    v = Split(s, ",")


    Dim i As Integer
    Dim last_index As Integer
    
    last_index = UBound(v)
    Debug.Print last_index
    
    For i = 0 To last_index
        Debug.Print Trim(v(i))
        sql = sql & "  your_column like '*" & Trim(v(i)) & "*' " & vbCrLf
        If i <> last_index Then
            sql = sql & "  and  " & vbCrLf
        End If
    Next
    
    sql = sql & ") " & vbCrLf
    build_where_clause = sql
    
End Function

 

[Erick]

Thanks James. I was hoping for a non-vba solution but I'll give this one a try.

 

[NiMip]

Before I look at the separate record idea, I made up my own little scenario for the sake of testing an idea. Do you think a version of this will be applicable to your database? To answer your question: the maximum number of elements with padded nils would totally work. Just depending on the maximum, the number of columns will get pretty large... might not be an issue for you.

For the "Match?" formula in I7, you need to hit CONTROL+SHIFT+ENTER as it's an array formula.

[TABLE="class: grid, width: 1106"]
<tbody>[TR]
[TD][/TD]
[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]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]First position[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Second position[/TD]
[TD="align: right"]=SEARCH(",",$A$7)+2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Third position[/TD]
[TD="align: right"]=SEARCH(",",$A$7,B3)+2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]String 1[/TD]
[TD]String 2[/TD]
[TD]String 1.1[/TD]
[TD]String 1.2[/TD]
[TD]String 1.3[/TD]
[TD]String 2.1[/TD]
[TD]String 2.2[/TD]
[TD]String 2.3[/TD]
[TD]Match?[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]a, b, c[/TD]
[TD]b, c, a[/TD]
[TD]=MID($A7,$B$2,1)[/TD]
[TD]=MID($A7,$B$3,1)[/TD]
[TD]=MID($A7,$B$4,1)[/TD]
[TD]=MID($B7,$B$2,1)[/TD]
[TD]=MID($B7,$B$3,1)[/TD]
[TD]=MID($B7,$B$4,1)[/TD]
[TD]=AND(COUNTIF(C7:E7,C7:E7)=COUNTIF(F7:H7,C7:E7))[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]a, b, b[/TD]
[TD]a, b, c[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]a, c, b[/TD]
[TD]a, b, c[/TD]
[TD]a[/TD]
[TD]c[/TD]
[TD]b[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]b, a, a[/TD]
[TD]a, b, a[/TD]
[TD]b[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]a[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]a, b, a[/TD]
[TD]a, a, a[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]c, a, a[/TD]
[TD]a, c, a[/TD]
[TD]c[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]c[/TD]
[TD]a[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

 

[xenou]

Your original post only mentions the letters (a,b,c).
Your subsequent has IDs and Categories also.

Can you clarify your problem. Are you trying to compare two records with a known Category to see if they are the same? Or are you just looking for things that are the same without knowing Categories or IDs?

 

[Joe4]

Erick,

Some of these solutions are Excel solutions, but you have posted this in the Access forum.
Can you confirm where you are trying to do this, Excel or Access? It isn't entirely clear from your original post, and people sometimes accidentally post to the wrong forum.

 

[Erick]

My original request was for an Access solution, and I am still exploring how to do this in Access. However I'll take an Excel alternative for now. I will post here if I ever find an Access solution.