Sorting complex data in Excel - Help!

[emilyshaw93]

Hi all,

I have a list of data as follows:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NAME[/TD]
[TD]REFERENCES[/TD]
[TD][/TD]
[TD]NAME (Sorted)[/TD]
[TD]REFERENCES (Sorted)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]36, 87, JR, 11, 26
[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]JR, 4, 11, 26, 36, 85, 87, 96[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Emily[/TD]
[TD]63, ES, 14, 1, 35
[/TD]
[TD][/TD]
[TD]Emily[/TD]
[TD]ES, 1, 14, 35, 63[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Kate[/TD]
[TD]KM, 73, 25, 84, 13
[/TD]
[TD][/TD]
[TD]Kate[/TD]
[TD]KM, 13, 25, 62, 73, 84, 94[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]John[/TD]
[TD]85, 96, 4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Kate[/TD]
[TD]94, 62, 25, 13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



What I want to do is get the data in column B to sort into column E automatically. I want multiple data sets for John, for example, to combine, sort in ascending order, and remove duplicates (as shown in E above), automatically.

I have set up column D to sort names and remove duplicates within the list in column A already - I need a formula to do a similar thing for putting the data in column B into column E.

Any help much appreciated!

Emily

 

[RickXL]

You might need something more like this, then:

Sub SortData()
    Dim i As Long, j As Long, sl As Object, sal As Object, ele As Variant, ws1 As Worksheet, ws2 As Worksheet
    Set sl = CreateObject("System.Collections.SortedList")
    Set ws1 = ThisWorkbook.Worksheets("Initial List")
    Set ws2 = ThisWorkbook.Worksheets("Scraping List")
    With ws1
        For i = 2 To .Cells(.Rows.Count, "F").End(xlUp).Row
            If sl.ContainsKey(.Cells(i, "F").Value) Then Set sal = sl.GetByIndex(sl.IndexOfKey(.Cells(i, "F").Value)) Else Set sal = CreateObject("System.Collections.ArrayList")
            For Each ele In Split(.Cells(i, "D"), ", ")
                If Not sal.Contains(ele) Then sal.Add Format(ele, "00")
            Next
            If Not sl.ContainsKey(.Cells(i, "F").Value) Then sl.Add .Cells(i, "F").Value, sal
        Next
    End With
    For i = 0 To sl.Count - 1
        sl.GetByIndex(i).Sort
        ws2.Range("B2").Offset(i) = sl.GetKey(i)
        ws2.Range("I2").Offset(i) = Join(sl.GetByIndex(i).toarray, ", ")
    Next
End Sub

 

[emilyshaw93]

Thanks Rick.

Saying that this part needs de-bugging but I can't work out what's wrong with it:

If sl.ContainsKey(.Cells(i, "F").Value) Then

Emily

 

[emilyshaw93]

It says 'Key cannot be null. Parameter name: key' ?

 

[RickXL]

I presume column F on row i must be empty?

 

[emilyshaw93]

The original document references are in column F of the 'Initial List' sheet.

The destination for the compiled list of references if column 'I' in the 'Scraping List' sheet.

 

[RickXL]

l think the statement with the error is:

If sl.ContainsKey(.Cells(i, "F").Value) Then Set sal = sl.GetByIndex(sl.IndexOfKey(.Cells(i, "F").Value)) Else Set sal = CreateObject("System.Collections.ArrayList")

Is that right?

If it is then it is looking at Sheet: Initial List, Column: F, Row iu where i is the index from the For/Next loop.

I don't know what the value of i is. You are the one with the spreadsheet.
My current guess is that somewhere in Sheet: Initial List, Column: F there is a missing value.

You could "ask" Excel what the valus of i is when the error occurs by hovering the mouse over it. Or by entering ?i and hitting Enter in the Debug Window or by adding a
Debug.Print i
statement on the previous line. I would expect any missing value to be visible though.