Please ! Help me with Macro Report

M

maigsb

New Member
Joined
Aug 10, 2012
Messages
11
Branch

A
1
Branch
2
Branch1
3
Branch2
4
Branch3
5
Branch4
6
Branch5
7
Branch6
8
Branch7
9
Branch8
10
Branch9
11
Branch10
12
Branch11
13
Branch12

<tbody>
</tbody>


Excel
tables to the web >> Excel Jeanie
HTML 4

Data
A
B
C
D
E
F
G
1
No.
Dealer
Brand
Qty
Amount
Type
Branch
2
1
aaa
Alfa Romeo
2
 5,000,000.00
Auto
Branch1
3
2
bbb
Alfa Romeo
3
 7,000,000.00
Manual
Branch2
4
3
ccc
Alpine
5
 10,000,000.00
Auto
Branch3
5
4
aaa
Alpine
1
 3,000,000.00
Auto
Branch4
6
5
bbb
Alfa Romeo
3
 9,000,000.00
Auto
Branch5
7
6
ccc
Alpine
2
 6,000,000.00
Auto
Branch6
8
7
aaa
Audi
4
 9,000,000.00
Auto
Branch7
9
8
bbb
Audi
3
 8,000,000.00
Auto
Branch8
10
9
ccc
Austin
2
 6,000,000.00
Auto
Branch9
11
10
aaa
BMW
6
 12,000,000.00
Manual
Branch10
12
11
bbb
Bristol
3
 8,000,000.00
Manual
Branch11
13
12
ccc
Bufori
2
 8,000,000.00
Manual
Branch12
14
13
aaa
BMW
1
 8,000,000.00
Manual
Branch4
15
14
bbb
Bristol
3
 8,000,000.00
Manual
Branch5
16
15
ccc
Bufori
2
 8,000,000.00
Manual
Branch6
17
16
aaa
Bugatti
1
 8,000,000.00
Auto
Branch7
18
17
bbb
Buick
3
 8,000,000.00
Auto
Branch8
19
18
ccc
Cadillac
1
 8,000,000.00
Auto
Branch8
20
19
aaa
Audi
3
 8,000,000.00
Auto
Branch9
21
20
bbb
Austin
2
 8,000,000.00
Auto
Branch10
22
21
ccc
Bentley
5
 8,000,000.00
Auto
Branch11
23
22
aaa
BMW
3
 8,000,000.00
Auto
Branch12
24
23
bbb
Bristol
2
 8,000,000.00
Auto
Branch10
25
24
ccc
Bufori
1
 8,000,000.00
Manual
Branch11
26
25
aaa
Alfa Romeo
1
 8,000,000.00
Manual
Branch12
27
26
bbb
Alpine
2
 8,000,000.00
Auto
Branch4
28
27
ccc
Alpine
3
 8,000,000.00
Auto
Branch5
29
28
aaa
Audi
1
 8,000,000.00
Auto
Branch6
30
29
bbb
Austin
2
 8,000,000.00
Auto
Branch7
31
30
ccc
Bentley
1
 8,000,000.00
Auto
Branch12
32
31
aaa
BMW
2
 8,000,000.00
Auto
Branch10
33
32
bbb
Bristol
3
 8,000,000.00
Auto
Branch11
34
33
ccc
Bugatti
2
 8,000,000.00
Auto
Branch12
35
34
aaa
Buick
1
 8,000,000.00
Auto
Branch4
36
35
bbb
Cadillac
3
 8,000,000.00
Auto
Branch1
37
36
ccc
Austin
2
 8,000,000.00
Manual
Branch2
38
37
aaa
Bentley
1
 8,000,000.00
Manual
Branch3
39
38
bbb
BMW
1
 8,000,000.00
Manual
Branch4
40
39
ccc
Bristol
1
 8,000,000.00
Manual
Branch5
41
40
aaa
Bufori
1
 8,000,000.00
Manual
Branch11
42
41
bbb
Bufori
2
 8,000,000.00
Manual
Branch12
43
42
ccc
Alfa Romeo
3
 8,000,000.00
Auto
Branch4
44
43
aaa
Audi
4
 8,000,000.00
Auto
Branch1
45
44
bbb
Austin
3
 8,000,000.00
Auto
Branch2
46
45
ccc
Bentley
2
 8,000,000.00
Auto
Branch3
47
46
aaa
BMW
3
 8,000,000.00
Auto
Branch4
48
47
bbb
Bristol
4
 8,000,000.00
Auto
Branch5
49
48
ccc
Bufori
2
 8,000,000.00
Manual
Branch11
50
49
aaa
Bugatti
1
 8,000,000.00
Manual
Branch12
51
50
bbb
Buick
2
 8,000,000.00
Manual
Branch4
52
51
ccc
Cadillac
2
 8,000,000.00
Manual
Branch5
53
52
aaa
Bentley
1
 8,000,000.00
Manual
Branch11
54
53
bbb
BMW
2
 8,000,000.00
Auto
Branch12
55
54
ccc
Bristol
1
 8,000,000.00
Auto
Branch5
56
55
aaa
Bufori
2
 8,000,000.00
Auto
Branch11
57
56
bbb
Bugatti
3
 8,000,000.00
Auto
Branch12
58
57
ccc
Austin
2
 8,000,000.00
Auto
Branch4
59
58
aaa
Bentley
1
 8,000,000.00
Auto
Branch5
60
59
bbb
BMW
1
 8,000,000.00
Auto
Branch11
61
60
ccc
Bristol
1
 8,000,000.00
Auto
Branch12
62
61
aaa
Bufori
2
 8,000,000.00
Manual
Branch5
63
62
bbb
Bugatti
3
 8,000,000.00
Manual
Branch11
64
63
ccc
Buick
2
 8,000,000.00
Manual
Branch4
65
64
aaa
Cadillac
2
 8,000,000.00
Manual
Branch1
66
65
bbb
Cadillac
1
 8,000,000.00
Manual
Branch2
67
66
ccc
Alfa Romeo
2
 8,000,000.00
Auto
Branch3
68
67
aaa
Alpine
1
 8,000,000.00
Auto
Branch4
69
68
bbb
Alpine
2
 8,000,000.00
Auto
Branch5
70
69
ccc
Audi
1
 8,000,000.00
Auto
Branch4
71
70
aaa
Austin
1
 8,000,000.00
Auto
Branch5

<tbody>
</tbody>


Excel
tables to the web >> Excel Jeanie
HTML 4

I have data in Sheet "Branch" and Sheet "Data" and want to create a new sheet name Sheet "Summary" and include this information into.

Summary


A
B
C
D
E
F
G
H
I
J
K
L
M
Z
AA
5
Brand
Branch1
Branch2
Branch3
Branch4
Branch5
Branch6
TOTAL
6
Qty
Amount
Qty
Amount
Qty
Amount
Qty
Amount
Qty
Amount
Qty
Amount
Qty
Amount
7
Acura
 -
 -
 -
 -
 -
 -
 -
8
Alfa Romeo
2
 5,000,000.00
3
 7,000,000.00
2
 8,000,000.00
3
 8,000,000.00
3
 9,000,000.00
 -
14
 45,000,000.00
9
Alpine
 -
 -
5
10,000,000.00
4
19,000,000.00
5
16,000,000.00
2
 6,000,000.00
16
 51,000,000.00
10
Aston Martin
 -
 -
 -
 -
 -
 -
 -
11
Audi
4
 8,000,000.00
 -
 -
1
 8,000,000.00
 -
1
 8,000,000.00
16
 49,000,000.00
12
Austin
 -
5
16,000,000.00
 -
2
 8,000,000.00
1
 8,000,000.00
 -
14
 54,000,000.00
13
Bentley
 -
 -
3
16,000,000.00
 -
1
 8,000,000.00
 -
11
 48,000,000.00
14
BMW
 -
 -
 -
5
24,000,000.00
 -
 -
19
 68,000,000.00
15
Bristol
 -
 -
 -
 -
9
32,000,000.00
 -
18
 64,000,000.00
16
Bufori
 -
 -
 -
 -
2
 8,000,000.00
2
 8,000,000.00
14
 64,000,000.00
17
Bugatti
 -
 -
 -
 -
 -
 -
10
 40,000,000.00
18
Buick
 -
 -
 -
5
24,000,000.00
 -
 -
8
 32,000,000.00
19
Cadillac
5
16,000,000.00
1
 8,000,000.00
 -
 -
2
 8,000,000.00
 -
9
 40,000,000.00
20
TOTAL
11
29,000,000.00
9
31,000,000.00
10
34,000,000.00
20
91,000,000.00
23
89,000,000.00
5
22,000,000.00
149
555,000,000.00
21
22
Brand
Branch1
Branch2
Branch3
Branch4
Branch5
Branch6
TOTAL
23
Auto
Manual
Auto
Manual
Auto
Manual
Auto
Manual
Auto
Manual
Auto
Manual
Auto
Manual
24
Acura
25
Alfa Romeo
1
1
1
1
1
4
2
26
Alpine
1
3
2
1
7
27
Aston Martin
28
Audi
1
1
1
6
29
Austin
1
1
1
1
6
1
30
Bentley
1
1
1
4
2
31
BMW
1
2
5
3
32
Bristol
2
2
5
3
33
Bufori
1
1
1
7
34
Bugatti
3
2
35
Buick
1
2
2
2
36
Cadillac
1
1
1
1
2
3
37
TOTAL
3
1
1
3
3
1
8
4
7
4
2
1
45
25

<tbody>
</tbody>


Spreadsheet Formulas

Cell
Formula
Z7
=SUM(B7,D7,F7,H7,J7,L7,N7,P7,R7,T7,V7,X7)
AA7
=SUM(C7,E7,G7,I7,K7,M7,O7,Q7,S7,U7,W7,Y7)
Z8
=SUM(B8,D8,F8,H8,J8,L8,N8,P8,R8,T8,V8,X8)
AA8
=SUM(C8,E8,G8,I8,K8,M8,O8,Q8,S8,U8,W8,Y8)
Z9
=SUM(B9,D9,F9,H9,J9,L9,N9,P9,R9,T9,V9,X9)
AA9
=SUM(C9,E9,G9,I9,K9,M9,O9,Q9,S9,U9,W9,Y9)
Z10
=SUM(B10,D10,F10,H10,J10,L10,N10,P10,R10,T10,V10,X10)
AA10
=SUM(C10,E10,G10,I10,K10,M10,O10,Q10,S10,U10,W10,Y10)
Z11
=SUM(B11,D11,F11,H11,J11,L11,N11,P11,R11,T11,V11,X11)
AA11
=SUM(C11,E11,G11,I11,K11,M11,O11,Q11,S11,U11,W11,Y11)
Z12
=SUM(B12,D12,F12,H12,J12,L12,N12,P12,R12,T12,V12,X12)
AA12
=SUM(C12,E12,G12,I12,K12,M12,O12,Q12,S12,U12,W12,Y12)
Z13
=SUM(B13,D13,F13,H13,J13,L13,N13,P13,R13,T13,V13,X13)
AA13
=SUM(C13,E13,G13,I13,K13,M13,O13,Q13,S13,U13,W13,Y13)
Z14
=SUM(B14,D14,F14,H14,J14,L14,N14,P14,R14,T14,V14,X14)
AA14
=SUM(C14,E14,G14,I14,K14,M14,O14,Q14,S14,U14,W14,Y14)
Z15
=SUM(B15,D15,F15,H15,J15,L15,N15,P15,R15,T15,V15,X15)
AA15
=SUM(C15,E15,G15,I15,K15,M15,O15,Q15,S15,U15,W15,Y15)
Z16
=SUM(B16,D16,F16,H16,J16,L16,N16,P16,R16,T16,V16,X16)
AA16
=SUM(C16,E16,G16,I16,K16,M16,O16,Q16,S16,U16,W16,Y16)
Z17
=SUM(B17,D17,F17,H17,J17,L17,N17,P17,R17,T17,V17,X17)
AA17
=SUM(C17,E17,G17,I17,K17,M17,O17,Q17,S17,U17,W17,Y17)
Z18
=SUM(B18,D18,F18,H18,J18,L18,N18,P18,R18,T18,V18,X18)
AA18
=SUM(C18,E18,G18,I18,K18,M18,O18,Q18,S18,U18,W18,Y18)
Z19
=SUM(B19,D19,F19,H19,J19,L19,N19,P19,R19,T19,V19,X19)
AA19
=SUM(C19,E19,G19,I19,K19,M19,O19,Q19,S19,U19,W19,Y19)
B20
=SUM(B7:B19)
C20
=SUM(C7:C19)
D20
=SUM(D7:D19)
E20
=SUM(E7:E19)
F20
=SUM(F7:F19)
G20
=SUM(G7:G19)
H20
=SUM(H7:H19)
I20
=SUM(I7:I19)
J20
=SUM(J7:J19)
K20
=SUM(K7:K19)
L20
=SUM(L7:L19)
M20
=SUM(M7:M19)
Z20
=SUM(B20,D20,F20,H20,J20,L20,N20,P20,R20,T20,V20,X20)
AA20
=SUM(C20,E20,G20,I20,K20,M20,O20,Q20,S20,U20,W20,Y20)
Z24
=SUM(B24,D24,F24,H24,J24,L24,N24,P24,R24,T24,V24,X24)
AA24
=SUM(C24,E24,G24,I24,K24,M24,O24,Q24,S24,U24,W24,Y24)
Z25
=SUM(B25,D25,F25,H25,J25,L25,N25,P25,R25,T25,V25,X25)
AA25
=SUM(C25,E25,G25,I25,K25,M25,O25,Q25,S25,U25,W25,Y25)
Z26
=SUM(B26,D26,F26,H26,J26,L26,N26,P26,R26,T26,V26,X26)
AA26
=SUM(C26,E26,G26,I26,K26,M26,O26,Q26,S26,U26,W26,Y26)
Z27
=SUM(B27,D27,F27,H27,J27,L27,N27,P27,R27,T27,V27,X27)
AA27
=SUM(C27,E27,G27,I27,K27,M27,O27,Q27,S27,U27,W27,Y27)
Z28
=SUM(B28,D28,F28,H28,J28,L28,N28,P28,R28,T28,V28,X28)
AA28
=SUM(C28,E28,G28,I28,K28,M28,O28,Q28,S28,U28,W28,Y28)
Z29
=SUM(B29,D29,F29,H29,J29,L29,N29,P29,R29,T29,V29,X29)
AA29
=SUM(C29,E29,G29,I29,K29,M29,O29,Q29,S29,U29,W29,Y29)
Z30
=SUM(B30,D30,F30,H30,J30,L30,N30,P30,R30,T30,V30,X30)
AA30
=SUM(C30,E30,G30,I30,K30,M30,O30,Q30,S30,U30,W30,Y30)
Z31
=SUM(B31,D31,F31,H31,J31,L31,N31,P31,R31,T31,V31,X31)
AA31
=SUM(C31,E31,G31,I31,K31,M31,O31,Q31,S31,U31,W31,Y31)
Z32
=SUM(B32,D32,F32,H32,J32,L32,N32,P32,R32,T32,V32,X32)
AA32
=SUM(C32,E32,G32,I32,K32,M32,O32,Q32,S32,U32,W32,Y32)
Z33
=SUM(B33,D33,F33,H33,J33,L33,N33,P33,R33,T33,V33,X33)
AA33
=SUM(C33,E33,G33,I33,K33,M33,O33,Q33,S33,U33,W33,Y33)
Z34
=SUM(B34,D34,F34,H34,J34,L34,N34,P34,R34,T34,V34,X34)
AA34
=SUM(C34,E34,G34,I34,K34,M34,O34,Q34,S34,U34,W34,Y34)
Z35
=SUM(B35,D35,F35,H35,J35,L35,N35,P35,R35,T35,V35,X35)
AA35
=SUM(C35,E35,G35,I35,K35,M35,O35,Q35,S35,U35,W35,Y35)
Z36
=SUM(B36,D36,F36,H36,J36,L36,N36,P36,R36,T36,V36,X36)
AA36
=SUM(C36,E36,G36,I36,K36,M36,O36,Q36,S36,U36,W36,Y36)
B37
=SUM(B24:B36)
C37
=SUM(C24:C36)
D37
=SUM(D24:D36)
E37
=SUM(E24:E36)
F37
=SUM(F25:F36)
G37
=SUM(G25:G36)
H37
=SUM(H24:H36)
I37
=SUM(I24:I36)
J37
=SUM(J25:J36)
K37
=SUM(K25:K36)
L37
=SUM(L24:L36)
M37
=SUM(M24:M36)
Z37
=SUM(B37,D37,F37,H37,J37,L37,N37,P37,R37,T37,V37,X37)
AA37
=SUM(C37,E37,G37,I37,K37,M37,O37,Q37,S37,U37,W37,Y37)

<tbody>
</tbody>

<tbody>
</tbody>


Excel
tables to the web >> Excel Jeanie
HTML 4

Is it can be done in any easy way using macro or any other way?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
maigsb,

Welcome to the MrExcel forum.


What version of Excel are you using?


On worksheet Data you have 11 unique vehicles.

On worksheet Summary you have 13 unique vehicles. Where did the two yellow background cells/vehicls come from?




Excel Workbook
AEAF
5Data vs Summary
6DataSummary
7Alfa RomeoAcura
8AlpineAlfa Romeo
9AudiAlpine
10AustinAston Martin
11BentleyAudi
12BMWAustin
13BristolBentley
14BuforiBMW
15BugattiBristol
16BuickBufori
17CadillacBugatti
18Buick
19Cadillac
20
Summary
 
Upvote 0
I use office 2010.
Sheet Branch might be in more than 12 branches.
I can not use a PivotTable because PivotTable that not have lists the Brand Acura and Aston Martin.
I want to have list a Brand Acura and Aston Martin too.
 
Upvote 0
Try this :- Results sheet (2)
NB:- Although "Alcura" and "Aston Martin" are missing from the Results (because they have no Data), if you Add them in column "C" of the data with just a Branch Number in column "G" they will then show in the Results.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG11Aug40
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Dim[/COLOR] col         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rw          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] nCol        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ltcol       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
            [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("G" & Rows.count).End(xlUp))
                [COLOR="Navy"]Set[/COLOR] Rng = Rng.Resize(, 7)
                    Ray = Rng.Value
 [COLOR="Navy"]For[/COLOR] Rw = 1 To UBound(Ray, 1)
    [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Ray(Rw, 3)) Then: n = n + 1: Dic.Add (Ray(Rw, 3)), n
 [COLOR="Navy"]Next[/COLOR] Rw
    ReDim nRay(1 To Dic.count * 2 + 10, 1 To 27)
[COLOR="Navy"]For[/COLOR] Ac = 1 To 12
    col = Ac + Ac
    nRay(1, col) = "Branch" & Ac
    nRay(1, 26) = "Totals"
    nRay(Dic.count + 5, col) = "Branch" & Ac
    nRay(2, col) = "Qty": nRay(2, col + 1) = "Amount"
    nRay(2, 26) = "Qty": nRay(2, 27) = "Amount"
    nRay(Dic.count + 6, col) = "Auto"
    nRay(Dic.count + 6, col + 1) = "Manual"
    nRay(Dic.count + 5, 26) = "Totals"
    nRay(Dic.count + 6, 26) = "Auto"
    nRay(Dic.count + 6, 27) = "Manual"
    [COLOR="Navy"]For[/COLOR] Rw = 1 To UBound(Ray, 1)
       nRay(Dic.Item(Ray(Rw, 3)) + 2, 1) = Ray(Rw, 3)
       nRay(Dic.Item(Ray(Rw, 3)) + Dic.count + 6, 1) = Ray(Rw, 3)
            [COLOR="Navy"]If[/COLOR] Ray(Rw, 7) = "Branch" & Ac [COLOR="Navy"]Then[/COLOR]
                nCol = IIf(Ray(Rw, 6) = "Manual", col + 1, col)
                Ltcol = IIf(nCol Mod 2 = 0, 26, 27)
                nRay(Dic.Item(Ray(Rw, 3)) + Dic.count + 6, nCol) = nRay(Dic.Item(Ray(Rw, 3)) + Dic.count + 6, nCol) + 1
                nRay(Dic.Item(Ray(Rw, 3)) + Dic.count + 6, Ltcol) = nRay(Dic.Item(Ray(Rw, 3)) + Dic.count + 6, Ltcol) + 1
                nRay(Dic.count * 2 + 7, 1) = "Total"
                nRay(Dic.count * 2 + 7, nCol) = nRay(Dic.count * 2 + 7, nCol) + 1
                nRay(Dic.count * 2 + 7, Ltcol) = nRay(Dic.count * 2 + 7, Ltcol) + 1
                nRay(Dic.Item(Ray(Rw, 3)) + 2, col) = nRay(Dic.Item(Ray(Rw, 3)) + 2, col) + Ray(Rw, 4)
                nRay(Dic.Item(Ray(Rw, 3)) + 2, col + 1) = nRay(Dic.Item(Ray(Rw, 3)) + 2, col + 1) + Ray(Rw, 5) '[COLOR="Green"][B],[/B][/COLOR]
                nRay(Dic.Item(Ray(Rw, 3)) + 2, col + 1) = Format(nRay(Dic.Item(Ray(Rw, 3)) + 2, col + 1), "#,##0.00")
                nRay(Dic.Item(Ray(Rw, 3)) + 2, 26) = nRay(Dic.Item(Ray(Rw, 3)) + 2, 26) + Ray(Rw, 4)
                nRay(Dic.Item(Ray(Rw, 3)) + 2, 27) = nRay(Dic.Item(Ray(Rw, 3)) + 2, 27) + Ray(Rw, 5)
                nRay(Dic.Item(Ray(Rw, 3)) + 2, 26) = Format(nRay(Dic.Item(Ray(Rw, 3)) + 2, 26), "#,##0.00")
                nRay(Dic.Item(Ray(Rw, 3)) + 2, 27) = Format(nRay(Dic.Item(Ray(Rw, 3)) + 2, 27), "#,##0.00")
                nRay(Dic.count + 3, col) = nRay(Dic.count + 3, col) + Ray(Rw, 4)
                nRay(Dic.count + 3, col + 1) = nRay(Dic.count + 3, col + 1) + Ray(Rw, 5)
                nRay(Dic.count + 3, col + 1) = Format(nRay(Dic.count + 3, col + 1), "#,##0.00")
                nRay(Dic.count + 3, 1) = "Total"
            [COLOR="Navy"]End[/COLOR] If
  
    [COLOR="Navy"]Next[/COLOR] Rw
                nRay(Dic.count + 3, 27) = Val(nRay(Dic.count + 3, 27)) + nRay(Dic.count + 3, col + 1)
[COLOR="Navy"]Next[/COLOR] Ac
  
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
  .Range("A1").Resize(Dic.count * 2 + 10, 27) = nRay
  .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
MsgBox "Run!!"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
maigsb,


Sample raw data worksheets:


Excel Workbook
A
1Branch
2Branch1
3Branch2
4Branch3
5Branch4
6Branch5
7Branch6
8Branch7
9Branch8
10Branch9
11Branch10
12Branch11
13Branch12
14
Branch





Excel Workbook
ABCDEFG
1No.DealerBrandQtyAmountTypeBranch
21aaaAlfa Romeo25,000,000.00AutoBranch1
32bbbAlfa Romeo37,000,000.00ManualBranch2
43cccAlpine510,000,000.00AutoBranch3
54aaaAlpine13,000,000.00AutoBranch4
65bbbAlfa Romeo39,000,000.00AutoBranch5
76cccAlpine26,000,000.00AutoBranch6
87aaaAudi49,000,000.00AutoBranch7
98bbbAudi38,000,000.00AutoBranch8
109cccAustin26,000,000.00AutoBranch9
1110aaaBMW612,000,000.00ManualBranch10
1211bbbBristol38,000,000.00ManualBranch11
1312cccBufori28,000,000.00ManualBranch12
1413aaaBMW18,000,000.00ManualBranch4
1514bbbBristol38,000,000.00ManualBranch5
1615cccBufori28,000,000.00ManualBranch6
1716aaaBugatti18,000,000.00AutoBranch7
1817bbbBuick38,000,000.00AutoBranch8
1918cccCadillac18,000,000.00AutoBranch8
2019aaaAudi38,000,000.00AutoBranch9
2120bbbAustin28,000,000.00AutoBranch10
2221cccBentley58,000,000.00AutoBranch11
2322aaaBMW38,000,000.00AutoBranch12
2423bbbBristol28,000,000.00AutoBranch10
2524cccBufori18,000,000.00ManualBranch11
2625aaaAlfa Romeo18,000,000.00ManualBranch12
2726bbbAlpine28,000,000.00AutoBranch4
2827cccAlpine38,000,000.00AutoBranch5
2928aaaAudi18,000,000.00AutoBranch6
3029bbbAustin28,000,000.00AutoBranch7
3130cccBentley18,000,000.00AutoBranch12
3231aaaBMW28,000,000.00AutoBranch10
3332bbbBristol38,000,000.00AutoBranch11
3433cccBugatti28,000,000.00AutoBranch12
3534aaaBuick18,000,000.00AutoBranch4
3635bbbCadillac38,000,000.00AutoBranch1
Data





Excel Workbook
ABCDEFG
3736cccAustin28,000,000.00ManualBranch2
3837aaaBentley18,000,000.00ManualBranch3
3938bbbBMW18,000,000.00ManualBranch4
4039cccBristol18,000,000.00ManualBranch5
4140aaaBufori18,000,000.00ManualBranch11
4241bbbBufori28,000,000.00ManualBranch12
4342cccAlfa Romeo38,000,000.00AutoBranch4
4443aaaAudi48,000,000.00AutoBranch1
4544bbbAustin38,000,000.00AutoBranch2
4645cccBentley28,000,000.00AutoBranch3
4746aaaBMW38,000,000.00AutoBranch4
4847bbbBristol48,000,000.00AutoBranch5
4948cccBufori28,000,000.00ManualBranch11
5049aaaBugatti18,000,000.00ManualBranch12
5150bbbBuick28,000,000.00ManualBranch4
5251cccCadillac28,000,000.00ManualBranch5
5352aaaBentley18,000,000.00ManualBranch11
5453bbbBMW28,000,000.00AutoBranch12
5554cccBristol18,000,000.00AutoBranch5
5655aaaBufori28,000,000.00AutoBranch11
5756bbbBugatti38,000,000.00AutoBranch12
5857cccAustin28,000,000.00AutoBranch4
5958aaaBentley18,000,000.00AutoBranch5
6059bbbBMW18,000,000.00AutoBranch11
6160cccBristol18,000,000.00AutoBranch12
6261aaaBufori28,000,000.00ManualBranch5
6362bbbBugatti38,000,000.00ManualBranch11
6463cccBuick28,000,000.00ManualBranch4
6564aaaCadillac28,000,000.00ManualBranch1
6665bbbCadillac18,000,000.00ManualBranch2
6766cccAlfa Romeo28,000,000.00AutoBranch3
6867aaaAlpine18,000,000.00AutoBranch4
6968bbbAlpine28,000,000.00AutoBranch5
7069cccAudi18,000,000.00AutoBranch4
7170aaaAustin18,000,000.00AutoBranch5
72
Data





After the macro in a new worksheet Summary:


Excel Workbook
ABCDEFGHIJKLM
1
2
3
4
5BrandBranch1Branch2Branch3Branch4Branch5Branch6
6QtyAmountQtyAmountQtyAmountQtyAmountQtyAmountQtyAmountQty
7Acura
8Alfa Romeo25,000,000.0037,000,000.0028,000,000.0038,000,000.0039,000,000.00
9Alpine510,000,000.00419,000,000.00516,000,000.0026,000,000.00
10Aston Martin
11Audi48,000,000.0018,000,000.0018,000,000.00
12Austin516,000,000.0028,000,000.0018,000,000.00
13Bentley316,000,000.0018,000,000.00
14BMW524,000,000.00
15Bristol932,000,000.00
16Bufori28,000,000.0028,000,000.00
17Bugatti
18Buick524,000,000.00
19Cadillac516,000,000.0018,000,000.0028,000,000.00
20TOTAL1129,000,000.00931,000,000.001034,000,000.002091,000,000.002389,000,000.00522,000,000.00
21
22BrandBranch1Branch2Branch3Branch4Branch5Branch6
23AutoManualAutoManualAutoManualAutoManualAutoManualAutoManualAuto
24Acura
25Alfa Romeo11111
26Alpine1321
27Aston Martin
28Audi111
29Austin1111
30Bentley111
31BMW21
32Bristol22
33Bufori11
34Bugatti
35Buick21
36Cadillac1111
37TOTAL133113484712
38
Summary





Excel Workbook
NOPQRSTUVWXY
1
2
3
4
5Branch7Branch8Branch9Branch10Branch11Branch12
6AmountQtyAmountQtyAmountQtyAmountQtyAmountQtyAmountQty
7
818,000,000.00
9
10
1149,000,000.0038,000,000.0038,000,000.00
1228,000,000.0026,000,000.0028,000,000.00
13616,000,000.0018,000,000.00
14820,000,000.0018,000,000.00516,000,000.00
1528,000,000.00616,000,000.0018,000,000.00
16632,000,000.00416,000,000.00
1718,000,000.0038,000,000.00624,000,000.00
1838,000,000.00
1918,000,000.00
20725,000,000.00724,000,000.00514,000,000.001236,000,000.002280,000,000.001880,000,000.00
21
22Branch7Branch8Branch9Branch10Branch11Branch12
23ManualAutoManualAutoManualAutoManualAutoManualAutoManualAuto
24
251
26
27
28111
29111
30111
311112
321111
33312
341112
351
361
37332136446
38
Summary





Excel Workbook
ZAA
1
2
3
4
5TOTAL
6AmountQty
7
81445,000,000.00
91651,000,000.00
10
111649,000,000.00
121454,000,000.00
131148,000,000.00
141968,000,000.00
151864,000,000.00
161464,000,000.00
171040,000,000.00
18832,000,000.00
19940,000,000.00
20149555,000,000.00
21
22TOTAL
23ManualAuto
24
2524
267
27
286
2916
3024
3135
3235
3371
3423
3522
3632
372545
38
Summary





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub CreateSummary()
' hiker95, 08/11/2012
' http://www.mrexcel.com/forum/showthread.php?652735-Please-!-Help-me-with-Macro-Report
Dim wB As Worksheet, wD As Worksheet, wS As Worksheet
Dim r As Long, lr As Long, lrd As Long, lrs As Long, nr As Long, c As Long, lc As Long, nc As Long
Dim v As Variant, amt As Long, qty As Double
Dim m As String, a As String, d As Range
Application.ScreenUpdating = False
v = Array("Acura", "Alfa Romeo", "Alpine", "Aston Martin", "Audi", "Austin", "Bentley", "BMW", "Bristol", "Bufori", "Bugatti", "Buick", "Cadillac")
Set wB = Worksheets("Branch")
Set wD = Worksheets("Data")
If Not Evaluate("ISREF(Summary!A1)") Then Worksheets.Add(After:=wD).Name = "Summary"
Set wS = Worksheets("Summary")
wS.UsedRange.Clear
lr = wB.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
With wS.Cells(5, 1).Resize(2)
  .Value = Application.Transpose([{"Brand","Qty"}])
  .HorizontalAlignment = xlCenter
End With
c = 2
For r = 2 To lr Step 1
  With wS.Cells(5, c)
    .Value = wB.Cells(r, 1).Value
    .Resize(, 2).MergeCells = True
    .HorizontalAlignment = xlCenter
  End With
  With wS.Cells(6, c).Resize(, 2)
    .Value = [{"Amount","Qty"}]
    .HorizontalAlignment = xlCenter
  End With
  c = c + 2
Next r
With wS.Cells(5, c)
  .Value = "TOTAL"
  .Resize(, 2).MergeCells = True
  .HorizontalAlignment = xlCenter
End With
With wS.Cells(6, c).Resize(, 2)
  .Value = [{"Amount","Qty"}]
  .HorizontalAlignment = xlCenter
End With
nr = wS.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wS.Cells(nr, 1).Resize(13).Value = Application.Transpose(v)
lrd = wD.Cells(Rows.Count, 1).End(xlUp).Row
lrs = wS.Cells(Rows.Count, 1).End(xlUp).Row
With wS.Range("B" & nr & ":B" & lrs)
  .Formula = "=SUMPRODUCT(--(Data!$G$2:$G$" & lrd & "=B$5),--(Data!$C$2:$C$" & lrd & "=$A7),Data!$D$2:$D$" & lrd & ")"
  .HorizontalAlignment = xlCenter
End With
With wS.Range("C" & nr & ":C" & lrs)
  .Formula = "=SUMPRODUCT(--(Data!$G$2:$G$" & lrd & "=B$5),--(Data!$C$2:$C$" & lrd & "=$A7),Data!$E$2:$E$" & lrd & ")"
  .NumberFormat = "#,##0.00"
  .HorizontalAlignment = xlCenter
End With
lc = wS.Cells(nr - 1, Columns.Count).End(xlToLeft).Column
For c = 4 To lc - 3 Step 2
  wS.Range("B" & nr & ":C" & lrs).Copy wS.Range(wS.Cells(nr, c), wS.Cells(lrs, c + 1))
Next c
For r = nr To lrs Step 1
  amt = 0: qty = 0
  For c = 2 To lc - 2 Step 2
    amt = amt + wS.Cells(r, c)
    qty = qty + wS.Cells(r, c + 1)
  Next c
  With wS.Cells(r, lc - 1)
    .Value = amt
    .HorizontalAlignment = xlCenter
  End With
  With wS.Cells(r, lc)
    .Value = qty
    .NumberFormat = "#,##0.00"
    .HorizontalAlignment = xlCenter
  End With
Next r
With wS.Cells(lrs + 1, 1)
  .Value = "TOTAL"
  .HorizontalAlignment = xlCenter
End With
amt = 0: qty = 0
For c = 2 To lc - 2 Step 2
  With wS.Cells(lrs + 1, c)
    .Value = Application.Sum(wS.Range(wS.Cells(nr, c), wS.Cells(lrs, c)))
    .HorizontalAlignment = xlCenter
  End With
  amt = amt + Application.Sum(wS.Range(wS.Cells(nr, c), wS.Cells(lrs, c)))
  With wS.Cells(lrs + 1, c + 1)
    .Value = Application.Sum(wS.Range(wS.Cells(nr, c + 1), wS.Cells(lrs, c + 1)))
    .HorizontalAlignment = xlCenter
    .NumberFormat = "#,##0.00"
  End With
  qty = qty + Application.Sum(wS.Range(wS.Cells(nr, c + 1), wS.Cells(lrs, c + 1)))
Next c
With wS.Cells(lrs + 1, lc - 1)
  .Value = amt
  .HorizontalAlignment = xlCenter
End With
With wS.Cells(lrs + 1, lc)
  .Value = qty
  .HorizontalAlignment = xlCenter
  .NumberFormat = "#,##0.00"
End With
nr = wS.Range("A" & Rows.Count).End(xlUp).Offset(2).Row
With wS.Cells(nr, 1).Resize(2)
  .Value = Application.Transpose([{"Brand","Auto"}])
  .HorizontalAlignment = xlCenter
End With
c = 2
For r = 2 To lr Step 1
  With wS.Cells(nr, c)
    .Value = wB.Cells(r, 1).Value
    .Resize(, 2).MergeCells = True
    .HorizontalAlignment = xlCenter
  End With
  With wS.Cells(nr + 1, c).Resize(, 2)
    .Value = [{"Manual","Auto"}]
    .HorizontalAlignment = xlCenter
  End With
  c = c + 2
Next r
With wS.Cells(nr, c)
  .Value = "TOTAL"
  .Resize(, 2).MergeCells = True
  .HorizontalAlignment = xlCenter
End With
With wS.Cells(nr + 1, c).Resize(, 2)
  .Value = [{"Manual","Auto"}]
  .HorizontalAlignment = xlCenter
End With
nr = wS.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wS.Cells(nr, 1).Resize(13).Value = Application.Transpose(v)
lrs = wS.Cells(Rows.Count, 1).End(xlUp).Row
For c = 2 To lc - 2 Step 2
  m = Split(Columns(c).Address, "$")(2)
  a = Split(Columns(c + 1).Address, "$")(2)
  With wS.Cells(nr, c)
    .Formula = "=COUNTIFS(Data!$G$2:$G$" & lrd & ",$" & m & "$" & nr - 2 & ",Data!$F$2:$F$" & lrd & ",$" & m & "$" & nr - 1 & ",Data!$C$2:$C$" & lrd & ",$A" & nr & ")"
    .HorizontalAlignment = xlCenter
    '.Value = .Value
  End With
  wS.Cells(nr, c).Copy wS.Range(wS.Cells(nr + 1, c), wS.Cells(lrs, c))
  With wS.Cells(nr, c + 1)
    .Formula = "=COUNTIFS(Data!$G$2:$G$" & lrd & ",$" & m & "$" & nr - 2 & ",Data!$F$2:$F$" & lrd & ",$" & a & "$" & nr - 1 & ",Data!$C$2:$C$" & lrd & ",$A" & nr & ")"
    .HorizontalAlignment = xlCenter
    '.Value = .Value
  End With
  wS.Cells(nr, c + 1).Copy wS.Range(wS.Cells(nr + 1, c + 1), wS.Cells(lrs, c + 1))
Next c
For r = nr To lrs Step 1
  amt = 0: qty = 0
  For c = 2 To lc - 2 Step 2
    amt = amt + wS.Cells(r, c)
    qty = qty + wS.Cells(r, c + 1)
  Next c
  With wS.Cells(r, lc - 1)
    .Value = amt
    .HorizontalAlignment = xlCenter
  End With
  With wS.Cells(r, lc)
    .Value = qty
    .HorizontalAlignment = xlCenter
  End With
Next r
With wS.Cells(lrs + 1, 1)
  .Value = "TOTAL"
  .HorizontalAlignment = xlCenter
End With
amt = 0: qty = 0
For c = 2 To lc - 2 Step 2
  With wS.Cells(lrs + 1, c)
    .Value = Application.Sum(wS.Range(wS.Cells(nr, c), wS.Cells(lrs, c)))
    .HorizontalAlignment = xlCenter
  End With
  amt = amt + Application.Sum(wS.Range(wS.Cells(nr, c), wS.Cells(lrs, c)))
  With wS.Cells(lrs + 1, c + 1)
    .Value = Application.Sum(wS.Range(wS.Cells(nr, c + 1), wS.Cells(lrs, c + 1)))
    .HorizontalAlignment = xlCenter
  End With
  qty = qty + Application.Sum(wS.Range(wS.Cells(nr, c + 1), wS.Cells(lrs, c + 1)))
Next c
With wS.Cells(lrs + 1, lc - 1)
  .Value = amt
  .HorizontalAlignment = xlCenter
End With
With wS.Cells(lrs + 1, lc)
  .Value = qty
  .HorizontalAlignment = xlCenter
End With
For Each d In wS.UsedRange
  If d.Value = 0 Then d = ""
Next d
With wS.UsedRange
  .Value = .Value
  .Columns.AutoFit
End With
wS.Activate
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the CreateSummary macro.
 
Upvote 0
Thanks for all the answers.
but hiker95 i run macro to answer this.
Summary


ABC
5BrandBranch1
6QtyAmountQty
7Acura
8Alfa Romeo
9Alpine
10Aston Martin
11Audi48,000,000.00
12Austin
13Bentley
14BMW
15Bristol
16Bufori
17Bugatti
18Buick
19Cadillac
20TOTAL48,000,000.00
21
22BrandBranch1
23AutoManualAuto
24Acura
25Alfa Romeo
26Alpine
27Aston Martin
28Audi 1
29Austin
30Bentley
31BMW
32Bristol
33Bufori
34Bugatti
35Buick
36Cadillac
37TOTAL 1


<colgroup>
<col style="width: 30px; font-weight: bold;">
<col style="width: 89px;">
<col style="width: 58px;">
<col style="width: 94px;"></colgroup>
<tbody>

</tbody>


Excel
tables to the web >> Excel Jeanie
HTML 4

This really should be like this.
Summary


ABC
5BrandBranch1
6QtyAmount
7Acura 0 -
8Alfa Romeo 2 5,000,000.00
9Alpine0 -
10Aston Martin 0 -
11Audi4 8,000,000.00
12Austin0 -
13Bentley 0 -
14BMW 0 -
15Bristol0 -
16Bufori0 -
17Bugatti0 -
18Buick 0 -
19Cadillac 516,000,000.00
20TOTAL1129,000,000.00
21
22BrandBranch1
23AutoManual
24Acura 00
25Alfa Romeo 10
26Alpine00
27Aston Martin 00
28Audi10
29Austin00
30Bentley 00
31BMW 00
32Bristol00
33Bufori00
34Bugatti00
35Buick 00
36Cadillac 11
37TOTAL31


<colgroup>
<col style="width: 30px; font-weight: bold;">
<col style="width: 154px;">
<col style="width: 42px;">
<col style="width: 122px;"></colgroup>
<tbody>

</tbody>


Spreadsheet Formulas

CellFormula
B20=SUM(B7:B19)
C20=SUM(C7:C19)
B37=SUM(B24:B36)
C37=SUM(C24:C36)


<tbody>

</tbody>


<tbody>

</tbody>


Excel
tables to the web >> Excel Jeanie
HTML 4
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Populate verbatim with criteria
Replies
7
Views
82
Fluff
Fluff
B
  • Question
VLOOKUP or INDEX/MATCH Result Skips Blanks Help
Replies
5
Views
843
Aladin Akyurek
Aladin Akyurek
vds1
  • Question
Please help me designing query
Replies
8
Views
495
vds1
vds1
A
  • Question
Conditional sum help?
Replies
5
Views
5K
stanleydgromjr
S
N
  • Question
Advanced Filter, xlFilterCopy and macro help
Replies
3
Views
2K
n0esc
N

Forum statistics

Threads
1,215,022
Messages
6,122,716
Members
449,093
Latest member
Mnur

Share this page

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