Sorting/Combining Text Strings and Numbers

[mjstebbs]

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

[TABLE="class: grid, width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]*[/TD]
[TD="width: 64"]*[/TD]
[TD="width: 64"]*[/TD]
[TD="width: 64"]*[/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]10.05654[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]10.05654[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]10.05654[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]10.05654[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]20.46506[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]20.46506[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]TRAP GX[/TD]
[TD="align: right"]10.05654[/TD]
[TD="align: right"]10.2929[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]TRAP GX[/TD]
[TD="align: right"]7.719677[/TD]
[TD="align: right"]7.542407[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]7.542407[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]7.542407[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]TRAP GX[/TD]
[TD="align: right"]7.719677[/TD]
[TD="align: right"]7.542407[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]7.542407[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]7.542407[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]7.542407[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]7.542407[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]7.542407[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]10.05654[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]10.05654[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]10.05654[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]20.46506[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]20.46506[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]7.542407[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD]TRAP GX[/TD]
[TD="align: right"]10.2929[/TD]
[TD="align: right"]10.41933[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]20.46506[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





[TABLE="width: 333"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD]4 5 6 7 31 32 33[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]10.05654[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9 10 35 37 40[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]20.46506[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]TRAP GX[/TD]
[TD="align: right"]10.05654[/TD]
[TD="align: right"]10.2929[/TD]
[/TR]
[TR]
[TD]15 19[/TD]
[TD]TRAP GX[/TD]
[TD="align: right"]7.719677[/TD]
[TD="align: right"]7.542407[/TD]
[/TR]
[TR]
[TD]16 17 20 23 24 25 26 38[/TD]
[TD]UNI GX[/TD]
[TD="align: right"]7.542407[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD]TRAP GX[/TD]
[TD="align: right"]10.2929[/TD]
[TD="align: right"]10.41933[/TD]
[/TR]
</tbody>[/TABLE]

 

[tonyyy]

mjstebs,

Welcome to the Board.

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

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

 

[mjstebbs]

tonyyy,

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

 

[tonyyy]

You're very welcome...