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.

 

[Fluff]

How about

Sub SplitData2()

   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("A" & Rows.count).End(xlUp).Row
   
   If SrcSht.AutoFilterMode Then SrcSht.AutoFilterMode = False
   With CreateObject("scripting.dictionary")
      For Each Cl In SrcSht.Range("A2:A" & UsdRws)
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Nothing
            SrcSht.Range("A1").AutoFilter 2, Cl.Value
            DestSht.Range("B" & Rows.count).End(xlUp).Offset(1, -1).Value = Cl.Value
            SrcSht.Range("B2:C" & UsdRws).SpecialCells(xlVisible).Copy DestSht.Range("B" & Rows.count).End(xlUp).Offset(1)
         End If
      Next Cl
   End With
   SrcSht.AutoFilterMode = False
    
End Sub

When posting code, please use code tags, the # icon in the reply window.

 

[Manexcel]

Thanks again for the quick response. In running the code you very kindly provided I get the following error;

runtime error 1004
application defined or object defined error.

On test1 (raw data) the data in each of the 3 columns is condensed with filter buttons.
On sheet 2 (output) only the first row of the raw data on test1 is copied BUT only the last name, middle name, first name.
the grade or date is not copied across.

Any thoughts? thanks.

 

[Fluff]

Oops, forgot to change the filter feld

            SrcSht.Range("A1").AutoFilter [COLOR=#ff0000]1[/COLOR], Cl.Value

 

[Manexcel]

This worked just great. thank you so much. It is very much appreciated.

 

[Fluff]

Glad to help & thanks for the feedback

 

[Manexcel]

Good morning I have been performing some more testing on the procedure - all still appears to work ok. However, I get the following message;

autofilter method of range class failed.

But the procedure appears to complete the grouping of data as expected.

Any thoughts? Thanks.

 

[Fluff]

What is the value of Cl when it fails?

 

[Manexcel]

As my knowledge of VBA is minimal can you advise how I get the value of C1 when it fails? If you are referring to the autofilter it is 1. If you are referring to the dataset C1 is "date of grade". What do i need to do to give the info you require? Thanks.

 

[Fluff]

When the code fails, click debug, then hover the mouse over where it says Cl.Value. You should then get a "Tool tip" appear with the value of Cl. What does it say?