Grouping of related data - Page 2

Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Grouping of related data

  1. #11
    MrExcel MVP Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    3,953
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Grouping of related data

     
    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.
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  2. #12
    Board Regular
    Join Date
    Dec 2015
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grouping of related data

    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!

  3. #13
    MrExcel MVP Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    3,953
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Grouping of related data

    Glad we could help & thanks for the feedback
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  4. #14
    Board Regular
    Join Date
    Dec 2015
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grouping of related data

    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.

  5. #15
    MrExcel MVP Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    3,953
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Grouping of related data

    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
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  6. #16
    Board Regular
    Join Date
    Dec 2015
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grouping of related data

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

  7. #17
    MrExcel MVP Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    3,953
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Grouping of related data

      
    Glad to help & thanks for the feedback
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com