Sum Macro

[texasalynn]

I have a file that has my monthly revenue listed by account and parent #. I want to have a macro that will give me a new sheet listing the total by parent#.

An example, accounts 1, 5, 7 belong to parent 14. So I want to summarize those accounts and put the one line total on the new sheet. I hope that is clear.

Thanks . . .
texasalynn

 

[Bass Blayde]

Have you tried the "subtotal" command? You will need to sort your data by Parent, then choose Data, Subtotals, and follow the prompts. Once you've done that, copy the results to a new worksheet. To copy only the subtotals, first click on the appropriate outline box in the upper left corner so only your subtotals are showing. Highlight the area to copy, then choose Edit, Goto, click Special, then click Visible Cells Only, then paste the result where you want it.

Hope this helps.

Bass

 

[plettieri]

hi

There might be some other options, care to post a short sample and the expected outcome so we can see better...You can use the utility noted under this text box Download Colo's cool utility for displaying your Excel Worksheet on the board.

pll

 

[texasalynn]

ok - here is just a same sample of the data

Book3
	A	B	C	D	E
1	Parent	STORE	ACCT	LOCATION	TOTAL
2	823	25	006-0347-0121327	HOOBSGAS	45.00
3	823	25	006-0347-0284919	HOOBSGAS	40.00
4	823	14	006-1788-0151811	HOOBSGAS	80.00
5	823	10	006-1788-0151837	HOOBSGAS	107.00
6	823	22	006-1788-0151845	HOOBSGAS	60.00
7	823	25	006-1788-0151852	HOOBSGAS	45.00
8	824	188	006-1788-0160416	RELIZE-DMGS	212.68
9	825	170	006-1788-0152272	RELIZE-RMSGS	336.00
10	826	170	006-1788-0152264	RELIZE-MCGS	59.75
11	826	160	006-1788-0160432	RELIZE-MCGS	1,894.52
12	827	188	006-1788-0152256	RELIZE-BGS	89.63
13	827	160	006-1788-0160382	RELIZE-BGS	796.00
Sheet1

I want the new sheet to show the parent number and the summarized amounts.

HTH
texasalynn

 

[plettieri]

You could use the sumif function as one choice

index-ptr-titles.xls
	F	G	H	I	J
11	STORE	ACCT	LOCATION	TOTAL
12	823	25	006-0347-0121327	HOOBSGAS	45.00
13	823	25	006-0347-0284919	HOOBSGAS	40.00
14	823	14	006-1788-0151811	HOOBSGAS	8.00
15	823	10	006-1788-0151837	HOOBSGAS	107.00
16	823	22	006-1788-0151845	HOOBSGAS	80.00
17	823	25	006-1788-0151852	HOOBSGAS	45.00
18	824	188	006-1788-0160416	RELIZE-DMGS	212.68
19	825	170	006-1788-0152272	RELIZE-RMSGS	336.00
20	826	170	006-1788-0152264	RELIZE-MCGS	59.75
21	826	160	006-1788-0160432	RELIZE-MCGS
22	827	188	006-1788-0152256	RELIZE-BGS	8,963.00
23	827	160	006-1788-0160382	RELIZE-BGS	796.00
24
25
26
27
28
29				823	325.00
30				824	212.68
31				825	336.00
Sheet5

modifying to fit your needs..

hope this helps

pll

 

[texasalynn]

No - I really have to have a macro to put the summarized data on a new sheet. That was only a small sample. My sheet might have 20,000+ lines. The summary is what is distributed and we don't want the detail listed.

Bass - I like your answer. I have never explored much of the Goto - Special options. That might be what I could use to create a macro.

Thanks . . .
texasalynn