Grouping of related data

[Manexcel]

I am using MsExcel 2016.

On sheet 1 I have a list (300+ rows). Headed by last name and first name and title 1 and title 2 and date.

On sheet 2 I wish to group that data by say; title 1 or by title 2 or by date.

Sheet 1 example data

Last name First name Title 1 Title 2 Date
Smith John Senior manager SM1 01/01/2017
Jones Alan Senior manager SM1 02/02/2017
Bloggs Fred Manager M1 01/01/2017
Bloggs George Manager M1 02/03/2017
Smith Mary Senior Administrator SA1 02/02/2017
Smith Alan Administrator A1 01/06/2017

etc
etc to row 300+

On sheet 2
I wish to display that data by say title 1 or by title 2 or by date. As I have 4 Titles on my sheet1 example I wish to end up with all Senior Managers (2 rows) in one group, Managers (2 rows), senior administrator (1 row) and Administrator (1 row). Each group contains first and last name and title 1 and title 2 and date.

Last name First name Title 1 Title 2 Date
Smith John Senior manager SM1 01/01/2017
Jones Alan Senior manager SM1 02/02/2017

Last name First name Title 1 Title 2 Date
Bloggs Fred Manager M1 01/01/2017
Bloggs George Manager M1 02/03/2017

Last name First name Title 1 Title 2 Date
Smith Mary Senior Administrator SA1 02/02/2017

Last name First name Title 1 Title 2 Date
Smith Alan Administrator A1 01/06/2017

etc


I have been looking at filter and sorting of data and then copy to sheet 2 but this is quite time consuming exercise. Therefore, is there a formula that I can use to achieve the above result?

I thank you for your time and patience.

 

[Fluff]

If you make both the changes, mention by AFPathfinder in post#10, you should be ok.
If not we'll need to know which line of code gives the error.

 

[Manexcel]

Thank you for response. I had values in col C so left as is. However, I did change the autofilter from 3 to 2 and all worked ok. Brilliant!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Manexcel]

I wasn't quite sure if I should open a new thread or continue with this existing thread as it is all related (?). I have been asked if it is possible to change the final layout

from :

Last name First name Title 1 Title 2 Date
Smith John Senior manager SM1 01/01/2017
Jones Alan Senior manager SM1 02/02/2017

Last name First name Title 1 Title 2 Date
Bloggs Fred Manager M1 01/01/2017
Bloggs George Manager M1 02/03/2017

Last name First name Title 1 Title 2 Date
Smith Mary Senior Administrator SA1 02/02/2017

Last name First name Title 1 Title 2 Date
Smith Alan Administrator A1 01/06/2017

etc

To (I have now changed the number of columns to 3 and the criteria is now in col B - as our previous dialog):

I would like the process to group on the criteria in Col B (as before) but have one title (e.g Senior Manager) and those individuals of that level are grouped under that title with only their name and date.

An example of the resultant output I am looking for is outlined below.

Senior Manager

Col A Col B

Last and last name Date

Smith John 01/01/2017
Jones Alan 02/02/2017

Manager

Bloggs Fred 01/01/2017
Bloggs George 02/03/2017

Administrator

Smith Mary 02/02/2017
Smith Alan 01/06/2017

etc

Once again I thank you for your patience and time in trying to resolve my problem.

 

[Fluff]

How about

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
    End With
    SrcSht.AutoFilterMode = False
    
End Sub

 

[Manexcel]

What can I say... but a big thank you. Another brilliant resolution. It is very much appreciated. All worked.

 

[Fluff]

Glad to help & thanks for the feedback

 

[Manexcel]

Hi you very kindly assisted me with a vba script last year that grouped data. This was around December 2017. I now have another and similar issue. Should i raise a new thread?

Thank you.

 

[Fluff]

Hi you very kindly assisted me with a vba script last year that grouped data. This was around December 2017. I now have another and similar issue. Should i raise a new thread?

Thank you.

Yes please

 

[Manexcel]

Thank you for the quick response and clarification. I have raised a new thread as requested.