Results 1 to 2 of 2

Thread: sort data within the same cell

  1. #1

    sort data within the same cell


    Agents here assign each job multiple pieces of data as part of their work. For audit purposes, I receive 2 strings of data - agent response and correct response. Due to reasons beyond my control, the two strings come in different orders.

    For instance, agent response may be (a, b, c, d, e, f, g) and correct response may be (c, a, b, d, g, f, e). They actually match, but are in different orders.

    Within our analysis, the two strings have to be compared to determine if they are correct. Within my spreadsheet/VBA, I have a basic 1:1 comparison (=if(a1=b1, "correct", "incorrect")). Due to the difference in order, jobs are showing incorrect when they really are not. This adds time to sort these out.

    I'm looking for the most efficient way to address this, whether it be re-ordering the string of data or doing a more complex comparison.

    Any and all suggestions are appreciated.

  2. #2
    VBAX Wizard
    Apr 2007
    I'd use a user defined function

    Option Explicit
    Sub test()
        Dim s1 As String, s2 As String
        s1 = "(a, b, c,                   d, e, f, g)"
        s2 = "(c, a, b, d, g, f, e)"
        MsgBox DoTheyMatch(s1, s2)
        s2 = "(c, a, b, d, g, f, Z)"
        MsgBox DoTheyMatch(s1, s2)
    End Sub
    Function DoTheyMatch(s1 As String, s2 As String) As String
        Dim v1 As Variant, v2 As Variant
        Dim s1a As String, s2a As String, s2b As String
        Dim i As Long
        'replace spaces
        s1a = Replace(s1, " ", vbNullString)
        s2a = Replace(s2, " ", vbNullString)
        'assume leading and trailing parens
        s1a = Mid(s1a, 2, Len(s1a) - 2)
        s2a = Mid(s2a, 2, Len(s2a) - 2)
        v1 = Split(s1a, ",")
        v2 = Split(s2a, ",")
        s2b = Join(v2, "#") & "#"
        'start check
        DoTheyMatch = "Incorrect"
        For i = LBound(v1) To UBound(v1)
            If InStr(s2b, v1(i) & "#") = 0 Then Exit Function
        Next i
        DoTheyMatch = "Correct"
    End Function

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts