excel 2007 - subtotals

[dlrouse518]

I have a large spreadsheet in Excel 2007 and need to move the subtotals to another spreadsheet - Can someone give me an idea how to do this? THANKS Donna R

 

[SirJB7]

Hi, dlrouse!


Assuming your data in sheet 1 starts in A1 like this:

-----

[pre]

Code	Subcode	Description	Value
1	1	A		10
1	1	B		20
Total 1			30
1	2	C		30
Total 2			30
1	3	D		40
Total 3			40
Total 1				100
2	1	E		50
2	1	F		60
2	1	G		70
Total 1			180
2	2	H		80
2	2	I		90
Total 2			170
Total 2				350
3	1	J		100
Total 1			100
3	2	K		110
Total 2			110
3	3	L		120
Total 3			120
Total 3				330
Total general			780

[/pre]
-----


In sheet 2:

a) copy first row (titles)

b) in A2 type:

=SI(ESERROR(HALLAR("Total";Hoja1!A2));"";Hoja1!A2) -----> in english: =IF(ISERROR(SEARCH("Total",Sheet1!A2)),"",Sheet1!A2)

c) copy A2 to B2

d) in C2 type:

=FILA() -----> in english: =ROW()

e) in D2 type:

=SI(O(A2<>"";B2<>"");Hoja1!D2;"") -----> in english: =IF(OR(A2<>"",B2<>""),Sheet1!D2,"")

f) copy down A2:D2 as needed until grand total displays

g) apply autofilter to whole sheet

h) filter on column D (value) selecting empty cells

i) select entire rows of filtered rows

j) right click and delete/remove all selected rows

k) reset filter


This procedure is a only-formulas solution. There are macro solutions that can help you if you'll have to do this repeatedly.


Just advise.


Regards!