Grouping of related data

Manexcel

Board Regular
Joined
Dec 28, 2015
Messages
127
Office Version
  1. 365
Platform
  1. Windows
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.
 
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.
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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!
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
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.
 
Upvote 0
How about
Code:
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
 
Upvote 0
What can I say... but a big thank you. Another brilliant resolution. It is very much appreciated. All worked.
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Thank you for the quick response and clarification. I have raised a new thread as requested.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top