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.
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.
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.
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(L, 1) C = Application.Match(Z, UsedRange.Rows(1), 0) If IsError(C) Then C = 3 + N% * 7 N = N + 1 With Cells(C).Resize(, 5) .HorizontalAlignment = xlCenterAcrossSelection .Interior.Color = vbYellow .Cells(1).Value2 = Z End With End If Cells(R, C).Value2 = .Parent.Name [A3].Value2 = V(L, 1) .AdvancedFilter 2, [A2:A3], Cells(R + 2, C) With Cells(R + Cells(R + 2, C).CurrentRegion.Rows.Count + 2, C + 4) .Font.Color = vbRed .HorizontalAlignment = xlCenter .Formula = "=SUM(" & Range(Cells(R + 3, .Column), .Cells(0)).Address & ")" Cells(R, C + 1).Value2 = .Value2 End With With Cells(R, C).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 » ! ◄ ◄
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.
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 F = 4 Cells.Clear Columns.ColumnWidth = StandardWidth Columns.Hidden = False Application.ScreenUpdating = False R& = 2 For S% = 1 To 3 With Sheets(S).UsedRange If S = 1 Then B$ = .Cells(F).Text & " ": K% = .Columns.Count: M% = K + 2 .Columns(F).AdvancedFilter 2, , [A2], True V = [A2].CurrentRegion For L% = 2 To UBound(V) Z$ = B & V(L, 1) C = Application.Match(Z, UsedRange.Rows(1), 0) If IsError(C) Then C = 3 + N% * M N = N + 1 With Cells(C).Resize(, K) .HorizontalAlignment = xlCenterAcrossSelection .Interior.Color = vbYellow .Cells(1).Value2 = Z End With End If Cells(R, C).Value2 = .Parent.Name [A3].Value2 = V(L, 1) .AdvancedFilter 2, [A2:A3], Cells(R + 2, C) With Cells(R + Cells(R + 2, C).CurrentRegion.Rows.Count + 2, C + 4) .Font.Color = vbRed .HorizontalAlignment = xlCenter .Formula = "=SUM(" & Range(Cells(R + 3, .Column), .Cells(0)).Address & ")" Cells(R, C + 1).Value2 = .Value2 End With With Cells(R, C).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
Bookmarks