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.

 

[AFPathfinder]

A formula is only going to affect the cell that it is in, so I think the only solution would be VBA (unless you export to Access and create a report from the table).

In VBA, I'd think using AdvancedFilter to load a unique value list off column C data into an array and then looping through the array, filtering column C on the value, and copy/pasting onto sheet 2 would be a decent way to accomplish the task. How familiar are you with VBA/macros?

 

[Manexcel]

Thank you so much for your response. Unfortunately I have very little knowledge of vba.

But if you think that vba is the only option is there any chance you can provide the vba code is resolve?

thank you.

 

[Fluff]

How about

Sub SpliData()

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

    Set SrcSht = ThisWorkbook.Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
    UsdRws = SrcSht.Range("C" & Rows.Count).End(xlUp).Row
    With CreateObject("scripting.dictionary")
        For Each Cl In SrcSht.Range("C2:C" & UsdRws)
            If Not .exists(Cl.Value) Then
                .Add Cl.Value, Nothing
                SrcSht.Range("A1").AutoFilter 3, Cl.Value
                If .Count = 1 Then
                    SrcSht.Range("A1:E" & UsdRws).SpecialCells(xlVisible).copy Sheets("[COLOR=#ff0000]Sheet2[/COLOR]").Range("A1")
                Else
                    SrcSht.Range("A1:E" & UsdRws).SpecialCells(xlVisible).copy Sheets("[COLOR=#ff0000]Sheet2[/COLOR]").Range("A" & Rows.Count).End(xlUp).Offset(3)
                End If
            End If
        Next Cl
    End With
    SrcSht.AutoFilterMode = False
    
End Sub

This assumes that your header is row 1 with data starting in A2
Change the sheet names in red to suit

 

[AFPathfinder]

Does the order matter when positions are placed on sheet 2?

 

[Manexcel]

No the order does not matter. Thanks

 

[Manexcel]

Thank you so much. This worked just great.

However, do I have the ability in the code to either increase or decrease the number of columns? I can change the sheet names - no problem but I cannot see where I can change the number of columns. If I reduce a column the procedure fails.

In addition, does the header row have to be repeated for each group? (This is a new "request" so apologies now)

Thanks again.

 

[Fluff]

This will copy the header row for the first group only

Sub SpliData()

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

    Set SrcSht = ThisWorkbook.Sheets("Sheet1")
    UsdRws = SrcSht.Range("C" & Rows.Count).End(xlUp).Row
    With CreateObject("scripting.dictionary")
        For Each Cl In SrcSht.Range("[COLOR=#ff0000]C2:C[/COLOR]" & UsdRws)
            If Not .exists(Cl.Value) Then
                .Add Cl.Value, Nothing
                SrcSht.Range("A1").AutoFilter 3, Cl.Value
                If .Count = 1 Then
                    SrcSht.Range("[COLOR=#0000ff]A1:E[/COLOR]" & UsdRws).SpecialCells(xlVisible).Copy Sheets("Sheet2").Range("A1")
                Else
                    SrcSht.Range("[COLOR=#0000ff]A2:E[/COLOR]" & UsdRws).SpecialCells(xlVisible).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(3)
                End If
            End If
        Next Cl
    End With
    SrcSht.AutoFilterMode = False
    
End Sub

The part in Red designates the column with the criteria (In this case Title 1)
The 2 parts in blue are the columns to be copied.

 

[Manexcel]

The procedure works fine. Thank you.

However, if I change my search criteria changes from column "C" to column "B" I get error messages.

I changed C2:C to B2:B. Do I also need to change the other references to "C" in the procedure to "B"? I noticed there are a number of lines that contain "C". As I am getting error messages run time error 1004 and application defined or object defined error.

Once again Thank you for your time a patience.

 

[AFPathfinder]

The other references that show "C1" are actually an object variable and not a direct cell reference. However, I think you'll want to change the "C" in this reference to "B" if you don't have any values in column C:

[COLOR=#333333]UsdRws = SrcSht.Range("[/COLOR][COLOR=#ff0000]C[/COLOR][COLOR=#333333]" & Rows.Count).End(xlUp).Row[/COLOR]

You might also need to change this line from "3" to "2" if the filter needs to be applied to column B:

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