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

*	*	*	*
*	*	*	*
*	*	*	*
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

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

*	*	*	*
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

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[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...