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.

 

[srizki]

I used the same question but different heading, I got a response
autosorting ???
that took my question off the unanswered list, and I did not get response, so I had to use correct heading and had to post it again.

 

[srizki]

To make my question simple,
There are say 50 rows, I want to keep those 50 rows but in a different lay out. Right now I move those rows by cut and paste. Since I do it ever month, from same lay out to another lay out that stays the same each month, I was wondering if there is was to do this by some other way than cut and paste.

 

[erik.van.geit]

To make my question simple,
There are say 50 rows, I want to keep those 50 rows but in a different lay out. Right now I move those rows by cut and paste. Since I do it ever month, from same lay out to another lay out that stays the same each month, I was wondering if there is was to do this by some other way than cut and paste.

Hi, again,

A moderator PM'd me to confirm this thread can be continued...

your tables were removed since they disturbed the webpage layout
you might try out "Table-It": as far as I know, this tool hasn't never disturbed overall layout

this is what you posted
   A        B                            C              D       E         
 6 G/L ACCT                              Balance        Expense I/C       
 7 #        Description                  12/31/2005     YTD     Transfers 
 8                                                                        
 9 1911     Land Improvement-Cyndabrook  -              -       3641      
10 1912     Land Improvement-Garth       (1,308,866)    57575             
11 1915     Land Improvement-Alexander   (13,880,00)    2603              
12               Subtotal                (1,322,745.88) 60177,9           
13                                                                        
14 1916     Bldg Alexander               (791,399)      145364            
15 1918     Bldg Cancer Ctr              (312,376)      26100             
16 1919     Lease Improvement-4721 Garth (76,585)       7602              
17 1921     Bldg Cyndabrook              -              8441    1015907   
18 1922     Bldg Garth Rd.               (2,978,290)    106572            
19 1923     Bldg POB                     (190,998)      288               

Blad1
[Table-It] version 06 by Erik Van Geit
what to do with this?
what's the result you want to get ?

best regards,
Erik

 

[srizki]

Thank you very much,
I tried to delete the table, but some how it did not work.
I thank you again for your reply.

 

[srizki]

The information comes to me as, grouped by location of those assets, I moved the ROWS only up or down to put same kind assets together regardless of the locations.
Is there a faster way (by Vlookup or index or matching and sorting, so it can be organized the way I want. Lets say raw worksheet sheet A and amended worksheet sheet B. I receive sheet A always the same way and change it to sheet B always the same way, same numbers of column and rows.

Thank you.

 

[erik.van.geit]

The information comes to me as, grouped by location of those assets, I moved the ROWS only up or down to put same kind assets together regardless of the locations.
Is there a faster way (by Vlookup or index or matching and sorting, so it can be organized the way I want. Lets say raw worksheet sheet A and amended worksheet sheet B. I receive sheet A always the same way and change it to sheet B always the same way, same numbers of column and rows.

Thank you.

as long as you talk in misteries (or half-misteries) it's impossible to provide a good answer
remember: the only example remaining in this thread is the table I displayed !
we need to know what the expected result is

just create a nice little sample

did you try SORT (see menu data) ?

 

[srizki]

   A        B                            C                             D                            
 6 G/L ACCT                              Balance                       Expense                      
 7 #        Description                  12/31/2005                    YTD                          
 8                                                                                                  
 9 1911     Land Improvement-Cyndabrook                             -                            -  
10 1912     Land Improvement-Garth                  (1,308,866)                         57,575      
11 1915     Land Improvement-Alexander                   (13,880)                         2,603     
12               Subtotal                      (1,322,745.88)                      60,177.90        
13                                                                                                  
14 1916     Bldg Alexander                             (791,399)                      145,364       
15 1918     Bldg Cancer Ctr                            (312,376)                        26,100      
16 1919     Lease Improvement-4721 Garth                 (76,585)                         7,602     
17 1921     Bldg Cyndabrook                                         -                     8,441     
18 1922     Bldg Garth Rd.                          (2,978,290)                       106,527       
19 1923     Bldg POB                                   (190,998)                             288    
20 1927     IP II                                   (1,021,100)                         61,187      
21 1928     IP I                                       (545,976)                        10,406      
22 1929     Bldg Imp IP I                              (174,122)                        24,405      
23 1929     Bldg Imp - SJMG                              (14,236)                                   
24 1931     Bldg Imp - Cyndabrook                                   -                     4,633     
25 1932     Bldg Imp Garth                        (11,174,090)                        944,430       
26 1933     Bldg Imp Decker POB                        (400,847)                                    
27 1935     Bldg Imp IP II                             (556,710)                        31,981      
28 1936     Lease Improvement-Highland                   (77,396)                         2,784     
29 1937     Lease Improvement-Crosby                                -                               
30 1938     Lease Improvement-Riser Bldg                   (3,510)                           251    
31 1939     Lease Improvement-Garth Bldg               (153,878)                        11,788      
32               Subtotal                    (18,471,512.58)                  1,386,188.03          

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

RANGE   FORMULA (1st cell)
C12:D12 =SUM(C9:C11)
C32:D32 =SUM(C14:C31)
D10     =7162.54+7189.19+7189.24+7210.61+7301.34+7311.85+7105.3+7104.95
D11     =325.36+325.36+325.36+325.36+325.36+325.36+325.36+325.36
D14     =18170.54+18170.54+18170.54+18170.53+18170.54+18170.53+18170.54+18170.53
D15     =3262.44+3262.44+3262.44+3262.44+3262.45+3262.44+3262.46+3262.44
D16     =950.26+950.26+950.26+950.26+950.26+950.25+950.26+950.25
D17     =4220.56+4220.55
D18     =13315.88+13315.86+13315.89+13315.81+13315.94+13315.78+13315.95+13315.77
D19     =35.97+35.97+35.97+35.97+35.97+35.97+35.97+35.97
D20     =7648.37+7648.37+7648.38+7648.37+7648.38+7648.37+7648.38+7648.37
D21     =1300.78+1300.78+1300.78+1300.78+1300.78+1300.78+1300.78+1300.78
D22     =3038.86+3052.27+3052.3+3052.27+3052.3+3052.26+3052.31+3052.25
D24     =2316.46+2316.45
D25     =117715.14+118779.64+118789.32+118709.91+79+117581.8+79+117887.52+79+117286.39+79+117364.49
D27     =3903.82+4028.82+4049.86+4028.79+21+4028.9+21+4028.78+21+4028.94+21+3799.57
D28     =347.99+347.99+347.99+347.99+347.99+347.99+347.99+348
D30     =31.43+31.43+31.43+31.43+31.45+31.43+31.45+31.43
D31     =1473.54+1473.54+1473.54+1473.54+1473.54+1473.54+1473.54+1473.54

[Table-It] version 06 by Erik Van Geit

this how I want, I dont know if it will put all the rows.

RANGE   FORMULA (1st cell)
C12:D12 =SUM(C9:C11)
C22:D22 =SUM(C15:C21)
C39:D39 =SUM(C24:C38)
D10     =7162.54+7189.19+7189.24+7210.61+7301.34+7311.85+7105.3+7104.95
D11     =325.36+325.36+325.36+325.36+325.36+325.36+325.36+325.36
D15     =950.26+950.26+950.26+950.26+950.26+950.25+950.26+950.25
D16     =347.99+347.99+347.99+347.99+347.99+347.99+347.99+348
D18     =31.43+31.43+31.43+31.43+31.45+31.43+31.45+31.43
D19     =1473.54+1473.54+1473.54+1473.54+1473.54+1473.54+1473.54+1473.54
D21     =819.46+910.51-819.46+969.16+969.14+969.18+969.14
D24     =18170.54+18170.54+18170.54+18170.53+18170.54+18170.53+18170.54+18170.53
D25     =3262.44+3262.44+3262.44+3262.44+3262.45+3262.44+3262.46+3262.44
D26     =4220.56+4220.55
D27     =13315.88+13315.86+13315.89+13315.81+13315.94+13315.78+13315.95+13315.77
D28     =35.97+35.97+35.97+35.97+35.97+35.97+35.97+35.97
D29     =7648.37+7648.37+7648.38+7648.37+7648.38+7648.37+7648.38+7648.37
D30     =1300.78+1300.78+1300.78+1300.78+1300.78+1300.78+1300.78+1300.78
D31     =3038.86+3052.27+3052.3+3052.27+3052.3+3052.26+3052.31+3052.25
D33     =2316.46+2316.45
D34     =117715.14+118779.64+118789.32+118709.91+79+117581.8+79+117887.52+79+117286.39+79+117364.49
D36     =3903.82+4028.82+4049.86+4028.79+21+4028.9+21+4028.78+21+4028.94+21+3799.57
D37     =57.28+57.28+57.28
D38     =35000.22+35092.32+35092.75+35092.73+35126.89+35126.84+35313.94+35320.72

[Table-It] version 06 by Erik Van Geit

thanks

 

[srizki]

Oops it did not work, here it is again,
   A        B                            C                           D                          
 6 G/L ACCT                              Balance                     Expense                    
 7 #        Description                  12/31/2005                  YTD                        
 8                                                                                              
 9 1911     Land Improvement-Cyndabrook                           -                          -  
10 1912     Land Improvement-Garth                (1,308,866)                  57,575.02        
11 1915     Land Improvement-Alexander                 (13,880)                  2,602.88       
12               Subtotal                    (1,322,745.88)                    60,177.90        
13                                                                                              
14                                                                                              
15 1919     Lease Improvement-4721 Garth               (76,585)                       7,602     
16 1936     Lease Improvement-Highland                 (77,396)                       2,784     
17 1937     Lease Improvement-Crosby                              -                             
18 1938     Lease Improvement-Riser Bldg                 (3,510)                         251    
19 1939     Lease Improvement-Garth Bldg             (153,878)                      11,788      
20 1947     Lease Imp-Baker                              (4,980)                                
21 1959     Lease Imp-Surgery Center                              -                   4,787     
22                                                   (316,349)              27,213              
23                                                                                              
24 1916     Bldg Alexander                           (791,399)                    145,364       
25 1918     Bldg Cancer Ctr                          (312,376)                      26,100      
26 1921     Bldg Cyndabrook                                       -                   8,441     
27 1922     Bldg Garth Rd.                        (2,978,290)                     106,527       
28 1923     Bldg POB                                 (190,998)                           288    
29 1927     IP II                                 (1,021,100)                       61,187      
30 1928     IP I                                     (545,976)                      10,406      
31 1929     Bldg Imp IP I                            (174,122)                      24,405      
32 1929     Bldg Imp - SJMG                            (14,236)                                 
33 1931     Bldg Imp - Cyndabrook                                 -                   4,633     
34 1932     Bldg Imp Garth                      (11,174,090)                      944,430       
35 1933     Bldg Imp Decker POB                      (400,847)                                  

Sheet1 (2)
[Table-It] version 06 by Erik Van Geit

RANGE   FORMULA (1st cell)
C12:D12 =SUM(C9:C11)
C22:D22 =SUM(C15:C21)
D10     =7162.54+7189.19+7189.24+7210.61+7301.34+7311.85+7105.3+7104.95
D11     =325.36+325.36+325.36+325.36+325.36+325.36+325.36+325.36
D15     =950.26+950.26+950.26+950.26+950.26+950.25+950.26+950.25
D16     =347.99+347.99+347.99+347.99+347.99+347.99+347.99+348
D18     =31.43+31.43+31.43+31.43+31.45+31.43+31.45+31.43
D19     =1473.54+1473.54+1473.54+1473.54+1473.54+1473.54+1473.54+1473.54
D21     =819.46+910.51-819.46+969.16+969.14+969.18+969.14
D24     =18170.54+18170.54+18170.54+18170.53+18170.54+18170.53+18170.54+18170.53
D25     =3262.44+3262.44+3262.44+3262.44+3262.45+3262.44+3262.46+3262.44
D26     =4220.56+4220.55
D27     =13315.88+13315.86+13315.89+13315.81+13315.94+13315.78+13315.95+13315.77
D28     =35.97+35.97+35.97+35.97+35.97+35.97+35.97+35.97
D29     =7648.37+7648.37+7648.38+7648.37+7648.38+7648.37+7648.38+7648.37
D30     =1300.78+1300.78+1300.78+1300.78+1300.78+1300.78+1300.78+1300.78
D31     =3038.86+3052.27+3052.3+3052.27+3052.3+3052.26+3052.31+3052.25
D33     =2316.46+2316.45
D34     =117715.14+118779.64+118789.32+118709.91+79+117581.8+79+117887.52+79+117286.39+79+117364.49

[Table-It] version 06 by Erik Van Geit

 

[erik.van.geit]

please delete all those unnecessary formulas which you displayed
next time use "only data" from the menu create "table-it"

what you are trying to do is not logic, at least I don't see the logic: perhaps there is a criterion I don't see ?

you will get a result close to your layout when sorting this way:
1st criteria column B
2nd column A
   A        B                            C              D            
 6 G/L ACCT                              Balance        Expense      
 7 #        Description                  31/12/05       YTD          
 8 1916     Bldg Alexander               -791,399       145,364      
 9 1918     Bldg Cancer Ctr              -312,376       26,1         
10 1921     Bldg Cyndabrook              -              8,441        
11 1922     Bldg Garth Rd.               -2.978.290     106,527      
12 1931     Bldg Imp - Cyndabrook        -              4,633        
13 1929     Bldg Imp - SJMG              -14,236                     
14 1933     Bldg Imp Decker POB          -400,847                    
15 1932     Bldg Imp Garth               -11.174.090    944,43       
16 1929     Bldg Imp IP I                -174,122       24,405       
17 1935     Bldg Imp IP II               -556,71        31,981       
18 1923     Bldg POB                     -190,998       288          
19 1928     IP I                         -545,976       10,406       
20 1927     IP II                        -1.021.100     61,187       
21 1915     Land Improvement-Alexander   -13,88         2,603        
22 1911     Land Improvement-Cyndabrook  -              -            
23 1912     Land Improvement-Garth       -1.308.866     57,575       
24 1919     Lease Improvement-4721 Garth -76,585        7,602        
25 1937     Lease Improvement-Crosby     -                           
26 1939     Lease Improvement-Garth Bldg -153,878       11,788       
27 1936     Lease Improvement-Highland   -77,396        2,784        
28 1938     Lease Improvement-Riser Bldg -3,51          251          
29          Subtotal                     -1.322.745,88  60.177,90    
30          Subtotal                     -18.471.512,58 1.386.188,03 

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

 

[srizki]

Ok, Next time I will use only data, and thanks for creating this table code.
I will not sort it, any way, (the numbers you see in the first column are the ones I put so may be I can use vlookup by the GL account # and then sort it by they number I put, but this gave me #N/A errors because of blanks, and when I sorted it all #N/A did not go as we want. So Vlookup failed.
So let me try to explain it differently,
Let’s say the original table has
A
B
C
Subtotal
Space
D
E
SUBTOTAL
SPACE
F
G
H
GRAND TOTAL

And I want.
A
B
C
D
SUBTOTAL
SPACE
E
F
G
H
Subtotal
Grand total

Subtotal and grand totals are not important, I can do it manually after the sheet has done, the lay out is the one that takes time.
Thanks Erik