Please ! Help me with Macro Report

M

maigsb

New Member
Joined
Aug 10, 2012
Messages
11
Branch

A

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]
[TD="align: center"]Branch
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]
[TD="align: center"]Branch1
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]
[TD="align: center"]Branch2
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]
[TD="align: center"]Branch3
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]
[TD="align: center"]Branch4
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]
[TD="align: center"]Branch5
[/TD]

[TD="bgcolor: #CACACA, align: center"]7
[/TD]
[TD="align: center"]Branch6
[/TD]

[TD="bgcolor: #CACACA, align: center"]8
[/TD]
[TD="align: center"]Branch7
[/TD]

[TD="bgcolor: #CACACA, align: center"]9
[/TD]
[TD="align: center"]Branch8
[/TD]

[TD="bgcolor: #CACACA, align: center"]10
[/TD]
[TD="align: center"]Branch9
[/TD]

[TD="bgcolor: #CACACA, align: center"]11
[/TD]
[TD="align: center"]Branch10
[/TD]

[TD="bgcolor: #CACACA, align: center"]12
[/TD]
[TD="align: center"]Branch11
[/TD]

[TD="bgcolor: #CACACA, align: center"]13
[/TD]
[TD="align: center"]Branch12
[/TD]

</tbody>


Excel
tables to the web >> Excel Jeanie
HTML 4

Data
A
B
C
D
E
F
G

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]
[TD="align: center"]No.
[/TD]
[TD="align: center"]Dealer
[/TD]
[TD="align: center"]Brand
[/TD]
[TD="align: center"]Qty
[/TD]
[TD="align: center"]Amount
[/TD]
[TD="align: center"]Type
[/TD]
[TD="align: center"]Branch
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Alfa Romeo
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 5,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch1
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Alfa Romeo
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 7,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch2
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Alpine
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: right"] 10,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch3
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Alpine
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 3,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch4
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Alfa Romeo
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 9,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch5
[/TD]

[TD="bgcolor: #CACACA, align: center"]7
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Alpine
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 6,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch6
[/TD]

[TD="bgcolor: #CACACA, align: center"]8
[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Audi
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: right"] 9,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch7
[/TD]

[TD="bgcolor: #CACACA, align: center"]9
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Audi
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch8
[/TD]

[TD="bgcolor: #CACACA, align: center"]10
[/TD]
[TD="align: center"]9
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Austin
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 6,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch9
[/TD]

[TD="bgcolor: #CACACA, align: center"]11
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]BMW
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: right"] 12,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch10
[/TD]

[TD="bgcolor: #CACACA, align: center"]12
[/TD]
[TD="align: center"]11
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Bristol
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch11
[/TD]

[TD="bgcolor: #CACACA, align: center"]13
[/TD]
[TD="align: center"]12
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Bufori
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch12
[/TD]

[TD="bgcolor: #CACACA, align: center"]14
[/TD]
[TD="align: center"]13
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]BMW
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch4
[/TD]

[TD="bgcolor: #CACACA, align: center"]15
[/TD]
[TD="align: center"]14
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Bristol
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch5
[/TD]

[TD="bgcolor: #CACACA, align: center"]16
[/TD]
[TD="align: center"]15
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Bufori
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch6
[/TD]

[TD="bgcolor: #CACACA, align: center"]17
[/TD]
[TD="align: center"]16
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Bugatti
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch7
[/TD]

[TD="bgcolor: #CACACA, align: center"]18
[/TD]
[TD="align: center"]17
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Buick
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch8
[/TD]

[TD="bgcolor: #CACACA, align: center"]19
[/TD]
[TD="align: center"]18
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Cadillac
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch8
[/TD]

[TD="bgcolor: #CACACA, align: center"]20
[/TD]
[TD="align: center"]19
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Audi
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch9
[/TD]

[TD="bgcolor: #CACACA, align: center"]21
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Austin
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch10
[/TD]

[TD="bgcolor: #CACACA, align: center"]22
[/TD]
[TD="align: center"]21
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Bentley
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch11
[/TD]

[TD="bgcolor: #CACACA, align: center"]23
[/TD]
[TD="align: center"]22
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]BMW
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch12
[/TD]

[TD="bgcolor: #CACACA, align: center"]24
[/TD]
[TD="align: center"]23
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Bristol
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch10
[/TD]

[TD="bgcolor: #CACACA, align: center"]25
[/TD]
[TD="align: center"]24
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Bufori
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch11
[/TD]

[TD="bgcolor: #CACACA, align: center"]26
[/TD]
[TD="align: center"]25
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Alfa Romeo
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch12
[/TD]

[TD="bgcolor: #CACACA, align: center"]27
[/TD]
[TD="align: center"]26
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Alpine
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch4
[/TD]

[TD="bgcolor: #CACACA, align: center"]28
[/TD]
[TD="align: center"]27
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Alpine
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch5
[/TD]

[TD="bgcolor: #CACACA, align: center"]29
[/TD]
[TD="align: center"]28
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Audi
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch6
[/TD]

[TD="bgcolor: #CACACA, align: center"]30
[/TD]
[TD="align: center"]29
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Austin
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch7
[/TD]

[TD="bgcolor: #CACACA, align: center"]31
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Bentley
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch12
[/TD]

[TD="bgcolor: #CACACA, align: center"]32
[/TD]
[TD="align: center"]31
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]BMW
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch10
[/TD]

[TD="bgcolor: #CACACA, align: center"]33
[/TD]
[TD="align: center"]32
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Bristol
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch11
[/TD]

[TD="bgcolor: #CACACA, align: center"]34
[/TD]
[TD="align: center"]33
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Bugatti
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch12
[/TD]

[TD="bgcolor: #CACACA, align: center"]35
[/TD]
[TD="align: center"]34
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Buick
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch4
[/TD]

[TD="bgcolor: #CACACA, align: center"]36
[/TD]
[TD="align: center"]35
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Cadillac
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch1
[/TD]

[TD="bgcolor: #CACACA, align: center"]37
[/TD]
[TD="align: center"]36
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Austin
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch2
[/TD]

[TD="bgcolor: #CACACA, align: center"]38
[/TD]
[TD="align: center"]37
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Bentley
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch3
[/TD]

[TD="bgcolor: #CACACA, align: center"]39
[/TD]
[TD="align: center"]38
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]BMW
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch4
[/TD]

[TD="bgcolor: #CACACA, align: center"]40
[/TD]
[TD="align: center"]39
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Bristol
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch5
[/TD]

[TD="bgcolor: #CACACA, align: center"]41
[/TD]
[TD="align: center"]40
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Bufori
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch11
[/TD]

[TD="bgcolor: #CACACA, align: center"]42
[/TD]
[TD="align: center"]41
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Bufori
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch12
[/TD]

[TD="bgcolor: #CACACA, align: center"]43
[/TD]
[TD="align: center"]42
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Alfa Romeo
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch4
[/TD]

[TD="bgcolor: #CACACA, align: center"]44
[/TD]
[TD="align: center"]43
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Audi
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch1
[/TD]

[TD="bgcolor: #CACACA, align: center"]45
[/TD]
[TD="align: center"]44
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Austin
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch2
[/TD]

[TD="bgcolor: #CACACA, align: center"]46
[/TD]
[TD="align: center"]45
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Bentley
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch3
[/TD]

[TD="bgcolor: #CACACA, align: center"]47
[/TD]
[TD="align: center"]46
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]BMW
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch4
[/TD]

[TD="bgcolor: #CACACA, align: center"]48
[/TD]
[TD="align: center"]47
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Bristol
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch5
[/TD]

[TD="bgcolor: #CACACA, align: center"]49
[/TD]
[TD="align: center"]48
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Bufori
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch11
[/TD]

[TD="bgcolor: #CACACA, align: center"]50
[/TD]
[TD="align: center"]49
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Bugatti
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch12
[/TD]

[TD="bgcolor: #CACACA, align: center"]51
[/TD]
[TD="align: center"]50
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Buick
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch4
[/TD]

[TD="bgcolor: #CACACA, align: center"]52
[/TD]
[TD="align: center"]51
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Cadillac
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch5
[/TD]

[TD="bgcolor: #CACACA, align: center"]53
[/TD]
[TD="align: center"]52
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Bentley
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch11
[/TD]

[TD="bgcolor: #CACACA, align: center"]54
[/TD]
[TD="align: center"]53
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]BMW
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch12
[/TD]

[TD="bgcolor: #CACACA, align: center"]55
[/TD]
[TD="align: center"]54
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Bristol
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch5
[/TD]

[TD="bgcolor: #CACACA, align: center"]56
[/TD]
[TD="align: center"]55
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Bufori
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch11
[/TD]

[TD="bgcolor: #CACACA, align: center"]57
[/TD]
[TD="align: center"]56
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Bugatti
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch12
[/TD]

[TD="bgcolor: #CACACA, align: center"]58
[/TD]
[TD="align: center"]57
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Austin
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch4
[/TD]

[TD="bgcolor: #CACACA, align: center"]59
[/TD]
[TD="align: center"]58
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Bentley
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch5
[/TD]

[TD="bgcolor: #CACACA, align: center"]60
[/TD]
[TD="align: center"]59
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]BMW
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch11
[/TD]

[TD="bgcolor: #CACACA, align: center"]61
[/TD]
[TD="align: center"]60
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Bristol
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch12
[/TD]

[TD="bgcolor: #CACACA, align: center"]62
[/TD]
[TD="align: center"]61
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Bufori
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch5
[/TD]

[TD="bgcolor: #CACACA, align: center"]63
[/TD]
[TD="align: center"]62
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Bugatti
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch11
[/TD]

[TD="bgcolor: #CACACA, align: center"]64
[/TD]
[TD="align: center"]63
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Buick
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch4
[/TD]

[TD="bgcolor: #CACACA, align: center"]65
[/TD]
[TD="align: center"]64
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Cadillac
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch1
[/TD]

[TD="bgcolor: #CACACA, align: center"]66
[/TD]
[TD="align: center"]65
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Cadillac
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Branch2
[/TD]

[TD="bgcolor: #CACACA, align: center"]67
[/TD]
[TD="align: center"]66
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Alfa Romeo
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch3
[/TD]

[TD="bgcolor: #CACACA, align: center"]68
[/TD]
[TD="align: center"]67
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Alpine
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch4
[/TD]

[TD="bgcolor: #CACACA, align: center"]69
[/TD]
[TD="align: center"]68
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]Alpine
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch5
[/TD]

[TD="bgcolor: #CACACA, align: center"]70
[/TD]
[TD="align: center"]69
[/TD]
[TD="align: center"]ccc
[/TD]
[TD="align: center"]Audi
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch4
[/TD]

[TD="bgcolor: #CACACA, align: center"]71
[/TD]
[TD="align: center"]70
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]Austin
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Branch5
[/TD]

</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
Acura
Alfa Romeo
Alpine
Aston Martin
Audi
Austin
Bentley
BMW
Bristol
Bufori
Bugatti
Buick
Cadillac
Acura
Alfa Romeo
Alpine
Aston Martin
Audi
Austin
Bentley
BMW
Bristol
Bufori
Bugatti
Buick
Cadillac

<tbody>
[TD="bgcolor: #CACACA, align: center"]5
[/TD]
[TD="align: center"]Brand
[/TD]
[TD="colspan: 2, align: center"]Branch1
[/TD]
[TD="colspan: 2, align: center"]Branch2
[/TD]
[TD="colspan: 2, align: center"]Branch3
[/TD]
[TD="colspan: 2, align: center"]Branch4
[/TD]
[TD="colspan: 2, align: center"]Branch5
[/TD]
[TD="colspan: 2, align: center"]Branch6
[/TD]
[TD="colspan: 2, align: center"]TOTAL
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]
[TD="align: center"]Qty
[/TD]
[TD="align: center"]Amount
[/TD]
[TD="align: center"]Qty
[/TD]
[TD="align: center"]Amount
[/TD]
[TD="align: center"]Qty
[/TD]
[TD="align: center"]Amount
[/TD]
[TD="align: center"]Qty
[/TD]
[TD="align: center"]Amount
[/TD]
[TD="align: center"]Qty
[/TD]
[TD="align: center"]Amount
[/TD]
[TD="align: center"]Qty
[/TD]
[TD="align: center"]Amount
[/TD]
[TD="align: center"]Qty
[/TD]
[TD="align: center"]Amount
[/TD]

[TD="bgcolor: #CACACA, align: center"]7
[/TD]

[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]

[TD="bgcolor: #CACACA, align: center"]8
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: center"] 5,000,000.00
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"] 7,000,000.00
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"] 8,000,000.00
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"] 8,000,000.00
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"] 9,000,000.00
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]14
[/TD]
[TD="align: center"] 45,000,000.00
[/TD]

[TD="bgcolor: #CACACA, align: center"]9
[/TD]

[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]10,000,000.00
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]19,000,000.00
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]16,000,000.00
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"] 6,000,000.00
[/TD]
[TD="align: center"]16
[/TD]
[TD="align: center"] 51,000,000.00
[/TD]

[TD="bgcolor: #CACACA, align: center"]10
[/TD]

[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]

[TD="bgcolor: #CACACA, align: center"]11
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: center"] 8,000,000.00
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"] 8,000,000.00
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"] 8,000,000.00
[/TD]
[TD="align: center"]16
[/TD]
[TD="align: center"] 49,000,000.00
[/TD]

[TD="bgcolor: #CACACA, align: center"]12
[/TD]

[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]16,000,000.00
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"] 8,000,000.00
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"] 8,000,000.00
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]14
[/TD]
[TD="align: center"] 54,000,000.00
[/TD]

[TD="bgcolor: #CACACA, align: center"]13
[/TD]

[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]16,000,000.00
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"] 8,000,000.00
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]11
[/TD]
[TD="align: center"] 48,000,000.00
[/TD]

[TD="bgcolor: #CACACA, align: center"]14
[/TD]

[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]24,000,000.00
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]19
[/TD]
[TD="align: center"] 68,000,000.00
[/TD]

[TD="bgcolor: #CACACA, align: center"]15
[/TD]

[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]9
[/TD]
[TD="align: center"]32,000,000.00
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]18
[/TD]
[TD="align: center"] 64,000,000.00
[/TD]

[TD="bgcolor: #CACACA, align: center"]16
[/TD]

[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"] 8,000,000.00
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"] 8,000,000.00
[/TD]
[TD="align: center"]14
[/TD]
[TD="align: center"] 64,000,000.00
[/TD]

[TD="bgcolor: #CACACA, align: center"]17
[/TD]

[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"] 40,000,000.00
[/TD]

[TD="bgcolor: #CACACA, align: center"]18
[/TD]

[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]24,000,000.00
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"] 32,000,000.00
[/TD]

[TD="bgcolor: #CACACA, align: center"]19
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: center"]16,000,000.00
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"] 8,000,000.00
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"] 8,000,000.00
[/TD]
[TD="align: center"][/TD]
[TD="align: center"] -
[/TD]
[TD="align: center"]9
[/TD]
[TD="align: center"] 40,000,000.00
[/TD]

[TD="bgcolor: #CACACA, align: center"]20
[/TD]
[TD="align: center"]TOTAL
[/TD]
[TD="align: center"]11
[/TD]
[TD="align: center"]29,000,000.00
[/TD]
[TD="align: center"]9
[/TD]
[TD="align: center"]31,000,000.00
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]34,000,000.00
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]91,000,000.00
[/TD]
[TD="align: center"]23
[/TD]
[TD="align: center"]89,000,000.00
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]22,000,000.00
[/TD]
[TD="align: center"]149
[/TD]
[TD="align: center"]555,000,000.00
[/TD]

[TD="bgcolor: #CACACA, align: center"]21
[/TD]

[TD="bgcolor: #CACACA, align: center"]22
[/TD]
[TD="align: center"]Brand
[/TD]
[TD="colspan: 2, align: center"]Branch1
[/TD]
[TD="colspan: 2, align: center"]Branch2
[/TD]
[TD="colspan: 2, align: center"]Branch3
[/TD]
[TD="colspan: 2, align: center"]Branch4
[/TD]
[TD="colspan: 2, align: center"]Branch5
[/TD]
[TD="colspan: 2, align: center"]Branch6
[/TD]
[TD="colspan: 2, align: center"]TOTAL
[/TD]

[TD="bgcolor: #CACACA, align: center"]23
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Manual
[/TD]
[TD="align: center"]Auto
[/TD]
[TD="align: center"]Manual
[/TD]

[TD="bgcolor: #CACACA, align: center"]24
[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="bgcolor: #CACACA, align: center"]25
[/TD]

[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]2
[/TD]

[TD="bgcolor: #CACACA, align: center"]26
[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"][/TD]

[TD="bgcolor: #CACACA, align: center"]27
[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="bgcolor: #CACACA, align: center"]28
[/TD]

[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]

[TD="bgcolor: #CACACA, align: center"]29
[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]30
[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]2
[/TD]

[TD="bgcolor: #CACACA, align: center"]31
[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]3
[/TD]

[TD="bgcolor: #CACACA, align: center"]32
[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]3
[/TD]

[TD="bgcolor: #CACACA, align: center"]33
[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]7
[/TD]

[TD="bgcolor: #CACACA, align: center"]34
[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]2
[/TD]

[TD="bgcolor: #CACACA, align: center"]35
[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]2
[/TD]

[TD="bgcolor: #CACACA, align: center"]36
[/TD]

[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3
[/TD]

[TD="bgcolor: #CACACA, align: center"]37
[/TD]
[TD="align: center"]TOTAL
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]45
[/TD]
[TD="align: center"]25
[/TD]

</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?
 
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
Acura
Alfa Romeo
Alpine
Aston Martin
Audi
Austin
Bentley
BMW
Bristol
Bufori
Bugatti
Buick
Cadillac
Acura
Alfa Romeo
Alpine
Aston Martin
Audi
Austin
Bentley
BMW
Bristol
Bufori
Bugatti
Buick
Cadillac


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


[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: center"]Brand[/TD]

[TD="colspan: 2, align: center"]Branch1[/TD]


[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: center"]Qty[/TD]

[TD="align: center"]Amount[/TD]

[TD="align: center"]Qty[/TD]


[TD="bgcolor: #CACACA, align: center"]7[/TD]



[TD="bgcolor: #CACACA, align: center"]8[/TD]



[TD="bgcolor: #CACACA, align: center"]9[/TD]



[TD="bgcolor: #CACACA, align: center"]10[/TD]



[TD="bgcolor: #CACACA, align: center"]11[/TD]



[TD="align: center"]4[/TD]

[TD="align: center"]8,000,000.00[/TD]


[TD="bgcolor: #CACACA, align: center"]12[/TD]



[TD="bgcolor: #CACACA, align: center"]13[/TD]



[TD="bgcolor: #CACACA, align: center"]14[/TD]



[TD="bgcolor: #CACACA, align: center"]15[/TD]



[TD="bgcolor: #CACACA, align: center"]16[/TD]



[TD="bgcolor: #CACACA, align: center"]17[/TD]



[TD="bgcolor: #CACACA, align: center"]18[/TD]



[TD="bgcolor: #CACACA, align: center"]19[/TD]



[TD="bgcolor: #CACACA, align: center"]20[/TD]

[TD="align: center"]TOTAL[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]8,000,000.00[/TD]


[TD="bgcolor: #CACACA, align: center"]21[/TD]



[TD="bgcolor: #CACACA, align: center"]22[/TD]

[TD="align: center"]Brand[/TD]

[TD="colspan: 2, align: center"]Branch1[/TD]


[TD="bgcolor: #CACACA, align: center"]23[/TD]

[TD="align: center"]Auto[/TD]

[TD="align: center"]Manual[/TD]

[TD="align: center"]Auto[/TD]


[TD="bgcolor: #CACACA, align: center"]24[/TD]



[TD="bgcolor: #CACACA, align: center"]25[/TD]



[TD="bgcolor: #CACACA, align: center"]26[/TD]



[TD="bgcolor: #CACACA, align: center"]27[/TD]



[TD="bgcolor: #CACACA, align: center"]28[/TD]



[TD="align: center"]1[/TD]


[TD="bgcolor: #CACACA, align: center"]29[/TD]



[TD="bgcolor: #CACACA, align: center"]30[/TD]



[TD="bgcolor: #CACACA, align: center"]31[/TD]



[TD="bgcolor: #CACACA, align: center"]32[/TD]



[TD="bgcolor: #CACACA, align: center"]33[/TD]



[TD="bgcolor: #CACACA, align: center"]34[/TD]



[TD="bgcolor: #CACACA, align: center"]35[/TD]



[TD="bgcolor: #CACACA, align: center"]36[/TD]



[TD="bgcolor: #CACACA, align: center"]37[/TD]

[TD="align: center"]TOTAL[/TD]



[TD="align: center"]1[/TD]

</tbody>


Excel
tables to the web >> Excel Jeanie
HTML 4

This really should be like this.
Summary


ABC
Acura
Alfa Romeo
Alpine
Aston Martin
Audi
Austin
Bentley
BMW
Bristol
Bufori
Bugatti
Buick
Cadillac
Acura
Alfa Romeo
Alpine
Aston Martin
Audi
Austin
Bentley
BMW
Bristol
Bufori
Bugatti
Buick
Cadillac


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


[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: center"]Brand[/TD]

[TD="colspan: 2, align: center"]Branch1[/TD]


[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: center"]Qty[/TD]

[TD="align: center"]Amount [/TD]


[TD="bgcolor: #CACACA, align: center"]7[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"] - [/TD]


[TD="bgcolor: #CACACA, align: center"]8[/TD]



[TD="align: center"]2[/TD]

[TD="align: center"] 5,000,000.00 [/TD]


[TD="bgcolor: #CACACA, align: center"]9[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"] - [/TD]


[TD="bgcolor: #CACACA, align: center"]10[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"] - [/TD]


[TD="bgcolor: #CACACA, align: center"]11[/TD]



[TD="align: center"]4[/TD]

[TD="align: center"] 8,000,000.00 [/TD]


[TD="bgcolor: #CACACA, align: center"]12[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"] - [/TD]


[TD="bgcolor: #CACACA, align: center"]13[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"] - [/TD]


[TD="bgcolor: #CACACA, align: center"]14[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"] - [/TD]


[TD="bgcolor: #CACACA, align: center"]15[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"] - [/TD]


[TD="bgcolor: #CACACA, align: center"]16[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"] - [/TD]


[TD="bgcolor: #CACACA, align: center"]17[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"] - [/TD]


[TD="bgcolor: #CACACA, align: center"]18[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"] - [/TD]


[TD="bgcolor: #CACACA, align: center"]19[/TD]



[TD="align: center"]5[/TD]

[TD="align: center"]16,000,000.00 [/TD]


[TD="bgcolor: #CACACA, align: center"]20[/TD]

[TD="align: center"]TOTAL[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]29,000,000.00 [/TD]


[TD="bgcolor: #CACACA, align: center"]21[/TD]



[TD="bgcolor: #CACACA, align: center"]22[/TD]

[TD="align: center"]Brand[/TD]

[TD="colspan: 2, align: center"]Branch1[/TD]


[TD="bgcolor: #CACACA, align: center"]23[/TD]

[TD="align: center"]Auto[/TD]

[TD="align: center"]Manual [/TD]


[TD="bgcolor: #CACACA, align: center"]24[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"]0[/TD]


[TD="bgcolor: #CACACA, align: center"]25[/TD]



[TD="align: center"]1[/TD]

[TD="align: center"]0[/TD]


[TD="bgcolor: #CACACA, align: center"]26[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"]0[/TD]


[TD="bgcolor: #CACACA, align: center"]27[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"]0[/TD]


[TD="bgcolor: #CACACA, align: center"]28[/TD]



[TD="align: center"]1[/TD]

[TD="align: center"]0[/TD]


[TD="bgcolor: #CACACA, align: center"]29[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"]0[/TD]


[TD="bgcolor: #CACACA, align: center"]30[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"]0[/TD]


[TD="bgcolor: #CACACA, align: center"]31[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"]0[/TD]


[TD="bgcolor: #CACACA, align: center"]32[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"]0[/TD]


[TD="bgcolor: #CACACA, align: center"]33[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"]0[/TD]


[TD="bgcolor: #CACACA, align: center"]34[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"]0[/TD]


[TD="bgcolor: #CACACA, align: center"]35[/TD]



[TD="align: center"]0[/TD]

[TD="align: center"]0[/TD]


[TD="bgcolor: #CACACA, align: center"]36[/TD]



[TD="align: center"]1[/TD]

[TD="align: center"]1[/TD]


[TD="bgcolor: #CACACA, align: center"]37[/TD]

[TD="align: center"]TOTAL[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]1[/TD]

</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
291
Fluff
Fluff
B
  • Question Question
VLOOKUP or INDEX/MATCH Result Skips Blanks Help
Replies
5
Views
929
Aladin Akyurek
Aladin Akyurek
vds1
  • Question Question
Please help me designing query
Replies
8
Views
592
vds1
vds1
A
  • Question Question
Conditional sum help?
Replies
5
Views
5K
stanleydgromjr
S
N
  • Question Question
Advanced Filter, xlFilterCopy and macro help
Replies
3
Views
2K
n0esc
N

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