Copying formatting

[srizki]

am trying to copy format from one sheet to another, and this is how it goes,

I receive a worksheet every month from another department, and then I sort that worksheet the way we want by moving rows up or down by cutting and pasting.

Let me give an example, in original sheet I receive asset numbers in column A and they are grouped by the location, I change the group by kind of assets regardless of where it is. There are sub-totals and blank rows after sub-totals.



I do this every month. I am sure there must be a better and faster way to do it, can anyone please help me.

 

[erik.van.geit]

my interpretation of your last example:
delete all subtotals
insert two rows for each group of 4 rows without caring what data are in the rows
1st new row = subtotal
2nd new row stays empty
at then end add grandtotal
this is surely NOT what you want

 

[srizki]

I created on my self,
this is the original one

123 Land Goteborg 800.00
124 Equipment Goteborg 200.00
125 Building Goteborg 900.00
Subtotal 1,900.00

126 Land Stockholm 7,000.00
127 Equipment Stockholm 300.00
128 Building Stockholm 600.00
Subtotal 7,900.00

129 Land Sidcup 100.00
130 Equipment Sidcup 900.00
131 Building Sidcup 800.00
132 Snow Trucks 500.00
133 Oil Rig 700.00
Sub total 3,000.00
Grand Total 12,800.00

This is what I want,
123 Land Goteborg 800.00
126 Land Stockholm 7,000.00
129 Land Sidcup 100.00
Sub total 7,900.00

124 Equipment Goteborg 200.00
127 Equipment Stockholm 300.00
130 Equipment Sidcup 900.00
132 Snow Trucks 500.00
133 Oil Rig 700.00
Sub total 2,600.00

125 Building Goteborg 900.00
128 Building Stockholm 600.00
131 Building Sidcup 800.00
Sub total 2,300.00
Grand Total 12,800.00

 

[srizki]

Or in html

Book7
	A	B	C	D	E
1
2	123	LandGoteborg			800.00
3	124	EquipmentGoteborg			200.00
4	125	BuildingGoteborg			900.00
5		Subtotal			1,900.00
6
7	126	LandStockholm			7,000.00
8	127	EquipmentStockholm			300.00
9	128	BuildingStockholm			600.00
10		Subtotal			7,900.00
11
12	129	LandSidcup			100.00
13	130	EquipmentSidcup			900.00
14	131	BuildingSidcup			800.00
15	132	SnowTrucks			500.00
16	133	OilRig			700.00
17		Subtotal			3,000.00
18		GrandTotal			12,800.00
Sheet1

and this is what I want,

Book7
	A	B	C	D	E
20	123	LandGoteborg			800.00
21	126	LandStockholm			7,000.00
22	129	LandSidcup			100.00
23		Subtotal			7,900.00
24
25	124	EquipmentGoteborg			200.00
26	127	EquipmentStockholm			300.00
27	130	EquipmentSidcup			900.00
28	132	SnowTrucks			500.00
29	133	OilRig			700.00
30		Subtotal			2,600.00
31
32	125	BuildingGoteborg			900.00
33	128	BuildingStockholm			600.00
34	131	BuildingSidcup			800.00
35		Subtotal			2,300.00
36		GrandTotal			12,800.00
Sheet1

I hope it works this time,
thanks for your patient.

 

[erik.van.geit]

the closest I can get is
delete subtotallines
sorting as explained before
insert a helper column which retrieves the first part of column B, using a formula like
=LEFT(C2,FIND(" ",B2)-1)
(will only work if column B has a space)

   A   B         C                   D  E  F             
 2 123 Land      Land Goteborg                   800.00  
 3 126 Land      Land Stockholm                7,000.00   
 4 129 Land      Land Sidcup                     100.00  
 5 124 Equipment Equipment Goteborg              200.00  
 6 127 Equipment Equipment Stockholm             300.00  
 7 130 Equipment Equipment Sidcup                900.00  
 8 132 Snow      Snow Trucks                     500.00  
 9 133 Oil       Oil Rig                         700.00  
10 125 Building  Building Goteborg               900.00  
11 128 Building  Building Stockholm              600.00  
12 131 Building  Building Sidcup                 800.00  

test
[Table-It] version 06 by Erik Van Geit

RANGE   FORMULA (1st cell)
B2:B12  =LEFT(C2,FIND(" ",C2)-1)

[Table-It] version 06 by Erik Van Geit

this would allow to subtotal (changes in helper column)

   A     B               C                   D  E  F        
 1 index helper          etcetera                           
 2 123   Land            Land Goteborg             800,00   
 3 126   Land            Land Stockholm            7000,00  
 4 129   Land            Land Sidcup               100,00   
 5       Total Land                                7900,00  
 6 124   Equipment       Equipment Goteborg        200,00   
 7 127   Equipment       Equipment Stockholm       300,00   
 8 130   Equipment       Equipment Sidcup          900,00   
 9       Total Equipment                           1400,00  
10 132   Snow            Snow Trucks               500,00   
11       Total Snow                                500,00   
12 133   Oil             Oil Rig                   700,00   
13       Total Oil                                 700,00   
14 125   Building        Building Goteborg         900,00   
15 128   Building        Building Stockholm        600,00   
16 131   Building        Building Sidcup           800,00   
17       Total Building                            2300,00  
18       Grand Total                               12800,00 

Blad2
[Table-It] version 06 by Erik Van Geit