Results 1 to 4 of 4

Thread: Sorting/Combining Text Strings and Numbers

  1. #1
    New Member
    Join Date
    Jul 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sorting/Combining Text Strings and Numbers

    Hi everyone,

    I am trying to sort the following data into the combined data following. I can't think of a way to do this and have been stuck for quite a while. Basically the goal is to list the numbers in the first column separated by a space, followed by the unique columns. Any tips, suggestions, or formulas would be greatly appreciated. Also, sorry if the format of this post is not correct, if I can help or provide more info, let me know. Thanks

    * * * *
    * * * *
    * * * *
    4 UNI GX 10.05654
    5 UNI GX 10.05654
    6 UNI GX 10.05654
    7 UNI GX 10.05654
    9 UNI GX 20.46506
    10 UNI GX 20.46506
    14 TRAP GX 10.05654 10.2929
    15 TRAP GX 7.719677 7.542407
    16 UNI GX 7.542407
    17 UNI GX 7.542407
    19 TRAP GX 7.719677 7.542407
    20 UNI GX 7.542407
    * * * *
    * * * *
    23 UNI GX 7.542407
    24 UNI GX 7.542407
    25 UNI GX 7.542407
    26 UNI GX 7.542407
    * * * *
    31 UNI GX 10.05654
    32 UNI GX 10.05654
    33 UNI GX 10.05654
    35 UNI GX 20.46506
    * * * *
    37 UNI GX 20.46506
    38 UNI GX 7.542407
    39 TRAP GX 10.2929 10.41933
    40 UNI GX 20.46506





    * * * *
    4 5 6 7 31 32 33 UNI GX 10.05654
    9 10 35 37 40 UNI GX 20.46506
    14 TRAP GX 10.05654 10.2929
    15 19 TRAP GX 7.719677 7.542407
    16 17 20 23 24 25 26 38 UNI GX 7.542407
    39 TRAP GX 10.2929 10.41933

  2. #2
    Board Regular tonyyy's Avatar
    Join Date
    Jun 2015
    Location
    Grants Pass, Oregon
    Posts
    1,647
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sorting/Combining Text Strings and Numbers

    mjstebs,

    Welcome to the Board.

    If you're comfortable with a vba approach, you might consider the following...

    Code:
    Sub CombineText_1064366()
    Dim LastRow As Long, i As Long, r As Long
    Dim lyst As String
    Dim arr1 As Variant, arr2() As Variant
    Dim dict As Object, ky As Variant
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    arr1 = Range("A1:D" & LastRow)
    Set dict = CreateObject("Scripting.Dictionary")
    For i = 4 To UBound(arr1)
        dict(arr1(i, 2) & arr1(i, 3) & arr1(i, 4)) = 1
    Next i
    
    ReDim arr2(1 To dict.Count, 1 To 4)
    r = 1
    For Each ky In dict.keys()
        For i = 4 To UBound(arr1)
            If arr1(i, 2) & arr1(i, 3) & arr1(i, 4) = ky Then
                If lyst <> "" Then
                    lyst = lyst & " " & arr1(i, 1)
                Else
                    lyst = arr1(i, 1)
                End If
                arr2(r, 2) = arr1(i, 2)
                arr2(r, 2) = arr1(i, 2)
                arr2(r, 3) = arr1(i, 3)
                arr2(r, 4) = arr1(i, 4)
            End If
        Next i
        arr2(r, 1) = lyst
        r = r + 1
        lyst = ""
    Next ky
    
    Sheets.Add after:=Sheets(Sheets.Count)
    Range("A1:D" & dict.Count).Value = arr2
    Columns.AutoFit
    End Sub
    Cheers,

    tonyyy
    Windoze 7 / Excel 2010

    How to post your Excel data
    How to post your vba code

  3. #3
    New Member
    Join Date
    Jul 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting/Combining Text Strings and Numbers

    tonyyy,

    I don't have too much exposure to VBA but I was able to get this working. Thank you for the help!

  4. #4
    Board Regular tonyyy's Avatar
    Join Date
    Jun 2015
    Location
    Grants Pass, Oregon
    Posts
    1,647
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sorting/Combining Text Strings and Numbers

    You're very welcome...
    Windoze 7 / Excel 2010

    How to post your Excel data
    How to post your vba code

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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