Query to compare string combination

Erick

Active Member
Joined
Feb 26, 2003
Messages
362
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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Not in any reasonable, extensible way.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
maybe this is a starting point

Code:
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
 
Upvote 0
Thanks James. I was hoping for a non-vba solution but I'll give this one a try.
 
Upvote 0
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.

ABCDEFGHI
1
2First position1
3Second position=SEARCH(",",$A$7)+2
4Third position=SEARCH(",",$A$7,B3)+2
5
6String 1String 2String 1.1String 1.2String 1.3String 2.1String 2.2String 2.3Match?
7a, b, cb, c, a=MID($A7,$B$2,1)=MID($A7,$B$3,1)=MID($A7,$B$4,1)=MID($B7,$B$2,1)=MID($B7,$B$3,1)=MID($B7,$B$4,1)=AND(COUNTIF(C7:E7,C7:E7)=COUNTIF(F7:H7,C7:E7))
8a, b, ba, b, cabbabcFALSE
9a, c, ba, b, cacbabcTRUE
10b, a, aa, b, abaaabaTRUE
11a, b, aa, a, aabaaaaFALSE
12c, a, aa, c, acaaacaTRUE

<tbody>
</tbody>
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top