Please ! Help me with Macro Report

maigsb · Aug 10, 2012

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

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

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

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)

Excel
tables to the web >> Excel Jeanie
HTML 4

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

hiker95 · Aug 12, 2012

maigsb,

There must be something different in your first three posted screenshots of worksheets Branch, Data, and Summary.

My posted screenshots look like yours for worksheets Branch, Data, and Summary.

The screenshots (yours and mine) for worksheets Summary, columns A, B, C, are identical.

The only way to resolve this is for you to attach a link to your workbook on Box.com, containing worksheets Branch, Data, and Summary.

You can upload your workbook to Box Net,

sensitive data scrubbed/removed/changed

mark the workbook for sharing
and provide us with a link to your workbook.

maigsb · Aug 12, 2012

Link to this file for Box Net
https://www.box.com/s/cb766f59418c350c67f7

hiker95 · Aug 12, 2012

maigsb,

The macro did not work correctly in your workbook, because, the following bold unique car names in worksheet Data contain a trailing space character, and so do those in your other table:

Excel Workbook

I

J

K

1

Brand

2

3

???????????

4

Alfa Romeo

5

Alpine

6

Audi

7

Austin

8

Bentley

9

BMW

10

Bristol

11

Bufori

12

Bugatti

13

Buick

14

Cadillac

Data

My macro code contains a vehicle array, that includes the two additional cars per your post:

Code:

v = Array("Acura", "Alfa Romeo", "Alpine", "Aston Martin", "Audi", "Austin", "Bentley", "BMW", "Bristol", "Bufori", "Bugatti", "Buick", "Cadillac")

I can adjust the vehicle array to contain the extra trailing space characters for the vehicles mentioned?????

maigsb · Aug 16, 2012

Many thanks hiker95..... Your code works for me

hiker95 · Aug 17, 2012

maigsb,

You are very welcome. Glad I could help.

Thanks for the feedback.

Come back anytime.

Please ! Help me with Macro Report

maigsb

New Member

hiker95

Well-known Member

Excel Facts

maigsb

New Member

hiker95

Well-known Member

maigsb

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

Please ! Help me with Macro Report

maigsb

New Member

hiker95

Well-known Member

Excel Facts

maigsb

New Member

hiker95

Well-known Member

maigsb

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock