grouping of data

[Manexcel]

I am using MsExcel 2016.

On sheet test11 I have a list of (12000+ rows). Headed by Name and Grade and date. Data starts in A2

On sheet sheet2 I wish to group that data by say; name.

test 1 example data (the name field does have the surname, first name middle name all in one column)

Name Grade Date
Smith, John charles, Grade 3, 01-jan-18
Smith, John charles, Grade 2, 01-jan-17
Smith, John charles, Grade 1, 01-jan-16
jones, fred bloggs, Grade 1, 01-jan-18
smith, jack, grade 1,01-jan-16
smith, jack, grade 2,01-feb-18

etc
to row 12000+

On sheet 2

I wish to display / group that data by say name. As I could have many rows with same surname names, but different first and middle names, each with different grades and dates I wish to end up with all the data
being grouped as below.

Name Grade Date
Smith, John charles Grade 3 01-jan-18
Grade 2 01-jan-17
Grade 1 01-jan-16
jones, fred bloggs Grade 1 01-jan-18
smith, jack Grade 1 01-jan-16
Grade 2 01-feb-18
Etc.

The VBA script that was very kindly provided before is below;

Sub SplitData()

    Dim SrcSht As Worksheet
    Dim DestSht As Worksheet
    Dim UsdRws As Long
    Dim Cl As Range
    
Application.ScreenUpdating = False

    Set SrcSht = ThisWorkbook.Sheets("test1")
    Set DestSht = ThisWorkbook.Sheets("Sheet2")
    UsdRws = SrcSht.Range("B" & Rows.Count).End(xlUp).Row
    With CreateObject("scripting.dictionary")
        For Each Cl In SrcSht.Range("B2:B" & UsdRws)
            If Not .Exists(Cl.Value) Then
                .Add Cl.Value, Nothing
                SrcSht.Range("A1").AutoFilter 2, Cl.Value
                If .Count = 1 Then
                    DestSht.Range("A1") = Cl.Value
                    DestSht.Range("A1").Font.Bold = True
                    SrcSht.Range("C1:C" & UsdRws).SpecialCells(xlVisible).copy DestSht.Range("A" & Rows.Count).End(xlUp).Offset(2, 1)
                    SrcSht.Range("A1:A" & UsdRws).SpecialCells(xlVisible).copy DestSht.Range("A" & Rows.Count).End(xlUp).Offset(2)
                Else
                    DestSht.Range("A" & Rows.Count).End(xlUp).Offset(3) = Cl.Value
                    DestSht.Range("A" & Rows.Count).End(xlUp).Font.Bold = True
                    SrcSht.Range("C2:C" & UsdRws).SpecialCells(xlVisible).copy DestSht.Range("A" & Rows.Count).End(xlUp).Offset(2, 1)
                    SrcSht.Range("A2:A" & UsdRws).SpecialCells(xlVisible).copy DestSht.Range("A" & Rows.Count).End(xlUp).Offset(2)
                End If
            End If
        Next Cl

I thank you for your assistance.

 

[Manexcel]

This proving challenging to achieve. When running the procedure I am now getting the following message box.

runtime error 1004
application defined or
object defined in error

with the option of OK or Help buttons

I need to click either Ok or help buttons, otherwise I cannot move to the debug option as you suggested

For further information which may assist. My dataset is 12k+ rows. The procedure runs and creates the output on the destination sheet. I know it goes to the end of the dataset because the names beginning with "Z" appearing on the destination sheet

Is there an option to run automatically the procedure BUT in debug mode which will give you the C1 value?

Does the above assist? Thanks again.

 

[Fluff]

Not sure what's going wrong & I can't replicate your errors.
Are either of your sheets protected?
Do you have any merged cells?

 

[Manexcel]

The sheets are not protected and there are no merged cells. However, over the weekend I performed some additional testing. I have reduced the number lines of data to process down to around 9000, and to date all appears to work ok. I dont get the error messages as previously described.

So once again I thank you so much for all your assistance and patience.

 

[Fluff]

Glad to help & sorry I couldn't fully resolve the problem