Query to compare string combination

Erick

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

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,041
Office Version
  1. 365
Platform
  1. Windows
You mean an Access solution without code? I don't think so, because of the potential for input variation. Don't bother trying to put the individual elements into fields of a Crosstab query IMHO, because the number of fields would fluctuate, thus you could end up trying to refer to a field that doesn't exist next time you run it.

My approach would be to sum each element's character value (Ascii) and compare the two sums. Would not matter how many elements there are if you make them an array. HOWEVER, I admit it could be possible that the sums of x number of characters vs y could be the same if there were many low value characters vs not as many high ones. Based on your post, I'd guess the likely-hood is nil since each string contained elements from the other. Here's the code for it:
Code:
Sub compStrings()
Dim str1 As String, str2 As String
Dim lngSum1 As Long, lngSum2 As Long
Dim ary1, ary2
Dim i As Integer

str1 = "a,b,d": str2 = "b,a,c"
ary1 = Split(str1, ",")
ary2 = Split(str2, ",")
For i = 0 To UBound(ary1)
    lngSum1 = Asc(LCase(ary1(i)))
Next
For i = 0 To UBound(ary2)
    lngSum2 = Asc(LCase(ary2(i)))
Next

If lngSum1 = lngSum2 Then
    MsgBox "equal"
Else
    MsgBox "different"
End If
    
End Sub
I chose to force each element to lower case so that A is no different than a, because those Ascii values are not the same. A little less code might be achievable by using the same function pass the string and process each sum, but not much. If the above was a function, you could call it from a query, but that's about the only way I can see a query being involved.
 
Last edited:

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
Adding more to the above answers:

I came up with some ideas for sql only solutions but with limitations. Still, might work if:
1) there are a limited number of values that might match, i.e. {'A','B',...,'F'} instead of {'A','B','C', ... 'Z'}
and/or
2) there are not duplicates within the categories, i.e. CAT1 can contain A,B,C or D,E,F but never A,A,B or D,F,F

If it's relevant, you have not yet answered my question in Post 8.
 
Last edited:

Erick

Active Member
Joined
Feb 26, 2003
Messages
360
Hi Xeniou, apologies for not responding to your query in Post 8. I actually missed that one.
To answer your question in Post 8, I am basically comparing if the elements contained within categories are the same. The number of categories will be variable, as well as the number of elements within.
With regards to your query on Post12, each category will contain on average of around 8 to 12 elements, however this will be variable. However, there will be a lot of potential elements. This A, B, C record set represents a string record with up to 32 characters. To make it easier to manage, I have created a query to assign an index number to each unique record. This means that in my comparison query, I will be comparing the numbered indices, as opposed to straight text.
To your second question, given the nature of data, there shouldn't be duplicate record per category.
My original approach to solve this was to pass the raw query output into a dynamic array, then sort the array record, then concatenate and compare. I think I was just being too ambitious in trying to achieve this with sql only solution.
 

Erick

Active Member
Joined
Feb 26, 2003
Messages
360
You mean an Access solution without code? I don't think so, because of the potential for input variation. Don't bother trying to put the individual elements into fields of a Crosstab query IMHO, because the number of fields would fluctuate, thus you could end up trying to refer to a field that doesn't exist next time you run it.

My approach would be to sum each element's character value (Ascii) and compare the two sums. Would not matter how many elements there are if you make them an array. HOWEVER, I admit it could be possible that the sums of x number of characters vs y could be the same if there were many low value characters vs not as many high ones. Based on your post, I'd guess the likely-hood is nil since each string contained elements from the other. Here's the code for it:
Code:
Sub compStrings()
Dim str1 As String, str2 As String
Dim lngSum1 As Long, lngSum2 As Long
Dim ary1, ary2
Dim i As Integer

str1 = "a,b,d": str2 = "b,a,c"
ary1 = Split(str1, ",")
ary2 = Split(str2, ",")
For i = 0 To UBound(ary1)
    lngSum1 = Asc(LCase(ary1(i)))
Next
For i = 0 To UBound(ary2)
    lngSum2 = Asc(LCase(ary2(i)))
Next

If lngSum1 = lngSum2 Then
    MsgBox "equal"
Else
    MsgBox "different"
End If
    
End Sub
I chose to force each element to lower case so that A is no different than a, because those Ascii values are not the same. A little less code might be achievable by using the same function pass the string and process each sum, but not much. If the above was a function, you could call it from a query, but that's about the only way I can see a query being involved.

Thanks for your response, Micron. I have actually done something similar, but your solution is better. I have since created a numbered index for each of my sting record and then summed the indices. In your solution, by using the ASC function, I think it means that I won't have to create an index...I'll trial this out.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,180
Messages
5,623,216
Members
415,957
Latest member
Newguy1924

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
Top