+ Reply to Thread
Results 1 to 13 of 13

Make a summary of three sheets in one sheet

  1. #1
    Registered User
    Join Date
    10-11-2012
    Location
    egypt
    MS-Off Ver
    Excel 2013
    Posts
    30

    Make a summary of three sheets in one sheet

    Hi ,

    I need a help to make a summary of 3 sheets Seg1 Seg2 Seg3 in other sheet named Summary.
    when making a summary sheet I need to keep the formatting of the main 3 sheets .
    In the Summary sheet the zones should be listed horizontally as clearly explained in the attached Sample file.

    please note the seg sheets could contain over 100 of rows and could contain more zones.




    please help , this is a daily task for me.

    much appreciated.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,901

    Re: Make a summary of three sheets in one sheet

    Before I begin this project, it is important to know if you have updated your version of Excel. It has been 9 years since you joined this forum, yet your profile still indicates XL2007. Are you using a later version? If so, please update your profile as there are more efficient ways to solve your issue with newer versions of excel.

  3. #3
    Registered User
    Join Date
    10-11-2012
    Location
    egypt
    MS-Off Ver
    Excel 2013
    Posts
    30

    Re: Make a summary of three sheets in one sheet

    sorry for that , I updated to xl2013.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,901

    Re: Make a summary of three sheets in one sheet

    Since you are using XL2013, you can employ Power Query.

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    1
    segment zone Data.Case # Data.Branch Data.amount Branch segment Data.Case # Data.zone Data.amount Branch zone Data.Case # Data.segment Data.amount
    2
    Seg1 A
    1
    Br1
    1000
    Br2 Seg1
    6
    B
    300
    Br2 C
    10
    Seg1
    900
    3
    Seg1 A
    2
    Br1
    500
    Br2 Seg1
    7
    B
    450
    Br2 C
    11
    Seg1
    1050
    4
    Seg1 A
    3
    Br1
    300
    Br2 Seg1
    8
    B
    600
    Br3 C
    12
    Seg1
    1200
    5
    Seg1 A
    4
    Br1
    400
    Br2 Seg1
    9
    B
    750
    Br3 C
    13
    Seg1
    1350
    6
    Seg1 A
    5
    Br2
    150
    Br2 Seg2
    8
    B
    2210
    Br3 C
    12
    Seg2
    3290
    7
    Seg2 A
    1
    Br1
    320
    Br2 Seg2
    9
    B
    2480
    Br3 C
    13
    Seg2
    3560
    8
    Seg2 A
    2
    Br1
    590
    Br2 Seg2
    10
    B
    2750
    Br3 C
    12
    Seg3
    1300
    9
    Seg2 A
    3
    Br1
    860
    Br2 Seg2
    11
    B
    3020
    Br3 C
    13
    Seg3
    590
    10
    Seg2 A
    4
    Br1
    1130
    Br1 Seg3
    3
    B
    200
    11
    Seg2 A
    5
    Br2
    1400
    Br1 Seg3
    4
    B
    200
    12
    Seg2 A
    6
    Br2
    1670
    Br2 Seg3
    5
    B
    200
    13
    Seg2 A
    7
    Br2
    1940
    Br2 Seg3
    6
    B
    200
    14
    Seg3 A
    1
    Br1
    700
    Br2 Seg3
    7
    B
    200
    15
    Seg3 A
    2
    Br1
    1200
    Br2 Seg3
    8
    B
    200
    16
    Br2 Seg3
    9
    B
    300
    17
    Br2 Seg3
    10
    B
    200
    18
    Br2 Seg3
    11
    B
    1000
    Sheet: Append A

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-11-2012
    Location
    egypt
    MS-Off Ver
    Excel 2013
    Posts
    30

    Re: Make a summary of three sheets in one sheet

    many thanks for the reply.
    this is not what im looking for.
    i want the result to look exactly as in the summary sheet attached.

    the second thing, im using my workplace PC and i cannt use power query.

    please help with a VBA code.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,901

    Re: Make a summary of three sheets in one sheet

    Will have to be someone else. I do not have the skill set to achieve your expected results. Good Luck.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,687

    Cool Hi ! Try this !


    According to the attachment a VBA demonstration as a beginner starter to paste to the Summary worksheet module :

    PHP Code: 
    Sub Demo1()
            
    Cells.Clear
            Columns
    .ColumnWidth StandardWidth
            Columns
    .Hidden False
            Application
    .ScreenUpdating False
            R
    & = 2
        
    For S% = 1 To 3
            With Sheets
    (S).UsedRange
                   
    .Columns(4).AdvancedFilter 2, , [A2], True
                    V 
    = [A2].CurrentRegion
                
    For L% = 2 To UBound(V)
                        
    Z$ = "Zone " V(L1)
                        
    Application.Match(ZUsedRange.Rows(1), 0)
                    If 
    IsError(CThen
                             C 
    N% * 7
                             N 
    1
                        With Cells
    (C).Resize(, 5)
                            .
    HorizontalAlignment xlCenterAcrossSelection
                            
    .Interior.Color vbYellow
                            
    .Cells(1).Value2 Z
                        End With
                    End 
    If
                         
    Cells(RC).Value2 = .Parent.Name
                         
    [A3].Value2 V(L1)
                        .
    AdvancedFilter 2, [A2:A3], Cells(2C)
                    
    With Cells(Cells(2C).CurrentRegion.Rows.Count 24)
                        .
    Font.Color vbRed
                        
    .HorizontalAlignment xlCenter
                        
    .Formula "=SUM(" Range(Cells(3, .Column), .Cells(0)).Address ")"
                         
    Cells(R1).Value2 = .Value2
                    End With
                    With Cells
    (RC).Resize(, 2)
                        .
    Font.Bold True
                        
    .HorizontalAlignment xlCenter
                    End With
                Next
            End With
                R 
    UsedRange.Rows.Count 2
        Next
            
    [A2].CurrentRegion.Clear
            Application
    .ScreenUpdating True
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !

  8. #8
    Registered User
    Join Date
    10-11-2012
    Location
    egypt
    MS-Off Ver
    Excel 2013
    Posts
    30

    Re: Hi ! Try this !

    this works like a charm , thank a million MAN.
    but please some points to clarify more :
    - can you comment the code.
    - what version of excel can i apply this code.
    - if I want to add more columns to seg sheets , what needs to be changed in the code.
    - if i want to filter by branch not by zones , what needs to be changed in the code.
    please help again,
    thanks in advance.

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,687

    Arrow Re: Make a summary of three sheets in one sheet


    As all is explained in VBA help

    Whatever the Excel version it should rock !

    For more columns you must change the C = 3 + N% * 7 codeline and the With Cells(C).Resize(, 5) codeline

    To filter by branch you must amend the .Columns(4). codeline and the Z$ = codeline
    Last edited by Marc L; 05-01-2021 at 08:45 PM.

  10. #10
    Registered User
    Join Date
    10-11-2012
    Location
    egypt
    MS-Off Ver
    Excel 2013
    Posts
    30

    Re: Make a summary of three sheets in one sheet

    it really rocks !

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,687

    Lightbulb Try this


    My revamped demonstration where you just have to amend the constant F to filter by another column (try 2)
    and nothin' to change in case of more columns :

    PHP Code: 
    Sub Demo1r()
      Const 
    4
            Cells
    .Clear
            Columns
    .ColumnWidth StandardWidth
            Columns
    .Hidden False
            Application
    .ScreenUpdating False
            R
    & = 2
        
    For S% = 1 To 3
            With Sheets
    (S).UsedRange
                    
    If 1 Then B$ = .Cells(F).Text " "K% = .Columns.CountM% = 2
                   
    .Columns(F).AdvancedFilter 2, , [A2], True
                    V 
    = [A2].CurrentRegion
                
    For L% = 2 To UBound(V)
                        
    Z$ = V(L1)
                        
    Application.Match(ZUsedRange.Rows(1), 0)
                    If 
    IsError(CThen
                             C 
    N% * M
                             N 
    1
                        With Cells
    (C).Resize(, K)
                            .
    HorizontalAlignment xlCenterAcrossSelection
                            
    .Interior.Color vbYellow
                            
    .Cells(1).Value2 Z
                        End With
                    End 
    If
                         
    Cells(RC).Value2 = .Parent.Name
                         
    [A3].Value2 V(L1)
                        .
    AdvancedFilter 2, [A2:A3], Cells(2C)
                    
    With Cells(Cells(2C).CurrentRegion.Rows.Count 24)
                        .
    Font.Color vbRed
                        
    .HorizontalAlignment xlCenter
                        
    .Formula "=SUM(" Range(Cells(3, .Column), .Cells(0)).Address ")"
                         
    Cells(R1).Value2 = .Value2
                    End With
                    With Cells
    (RC).Resize(, 2)
                        .
    Font.Bold True
                        
    .HorizontalAlignment xlCenter
                    End With
                Next
            End With
                R 
    UsedRange.Rows.Count 2
        Next
            
    [A2].CurrentRegion.Clear
            Application
    .ScreenUpdating True
    End Sub 

  12. #12
    Registered User
    Join Date
    10-11-2012
    Location
    egypt
    MS-Off Ver
    Excel 2013
    Posts
    30

    Re: Try this

    This is perfect , it helps me alot.
    can this be modified so there will be fixed number of empty rows between tables , say one empty row only.

    thanks in advance.

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,687

    Arrow Re: Make a summary of three sheets in one sheet


    Nothing to change as this is already the case

    Anyway you can mod the calculation of the variable R.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 10-24-2013, 11:01 AM
  2. Copy data from several sheets to one to make a summary
    By Wim81 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-17-2013, 08:42 AM
  3. trying to make a button on summary page to make new working sheet
    By dbzisme in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-07-2013, 05:41 PM
  4. [SOLVED] Summary sheet - copy cell contents from multiple sheets to one summary sheet
    By jsmity in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2013, 10:32 AM
  5. Create a summary from multiple sheets on a master/summary sheet
    By detribus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-07-2012, 08:04 PM
  6. Replies: 12
    Last Post: 05-27-2012, 07:19 PM
  7. [SOLVED] Lookup the datas from all sheets and make summary on a separate sheet
    By PRADEEPB270 in forum Excel General
    Replies: 1
    Last Post: 03-30-2012, 03:17 AM
  8. How to make summary from many sheets
    By moh.3564 in forum Excel General
    Replies: 5
    Last Post: 01-14-2012, 06:14 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1