Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying formulas into column from row information
I'm sure this an ongoing issue for some, but here's an example of my data:
4010 4015 4030 4040 94 1000.00 2000.00 3000.00 1000.00 10 244.44 488.89 733.33 244.44 20 266.67 533.33 800.00 266.67 30 266.67 533.33 800.00 266.67 40 133.33 266.67 400.00 133.33 50 88.89 177.78 266.67 88.89 We are trying to create an allocation template in Excel that used to store/process information vertically, but was a monster to maintain. So, we're trying to store/process horizontally. The problem is, on the second tab I want to generate a list basically to import into the g/l software as follows: 401094 -1000.00 401010 244.44 401020 266.67 401030 266.67 401040 133.33 401050 88.89 401594 -2000.00 401510 488.89 etc.. Is there an easy way to do this? The first row contains account numbers (right now I have 55) and the first column contains cost center (right now I have 30). Thanks for any help you guys can offer! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying formulas into column from row information
A macro will work. In the macro below, change the size of TBL as required in
2nd line of the macro. Sub SplitTable() Set TBL = Sheets("Sheet1").Range("A1:E7") TRows = TBL.Rows.Count TCols = TBL.Columns.Count NewRow = 1 For RowCount = 2 To TRows For ColCount = 2 To TCols AccountNum = Val(Trim(TBL(1, ColCount)) & _ Trim(TBL(RowCount, 1))) Data = TBL(RowCount, ColCount) With Sheets("Sheet2") .Range("A" & NewRow) = AccountNum .Range("B" & NewRow) = Data NewRow = NewRow + 1 End With Next ColCount Next RowCount End Sub "suzilea" wrote: I'm sure this an ongoing issue for some, but here's an example of my data: 4010 4015 4030 4040 94 1000.00 2000.00 3000.00 1000.00 10 244.44 488.89 733.33 244.44 20 266.67 533.33 800.00 266.67 30 266.67 533.33 800.00 266.67 40 133.33 266.67 400.00 133.33 50 88.89 177.78 266.67 88.89 We are trying to create an allocation template in Excel that used to store/process information vertically, but was a monster to maintain. So, we're trying to store/process horizontally. The problem is, on the second tab I want to generate a list basically to import into the g/l software as follows: 401094 -1000.00 401010 244.44 401020 266.67 401030 266.67 401040 133.33 401050 88.89 401594 -2000.00 401510 488.89 etc.. Is there an easy way to do this? The first row contains account numbers (right now I have 55) and the first column contains cost center (right now I have 30). Thanks for any help you guys can offer! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying formulas into column from row information
One play ..
Assuming your source table as posted is in Sheet1, where in B1:E1 a 4010, 4015, 4030, 4040 in A2:A7 a 94, 10, ... (ie 6 row headers) In another sheet, In A2: =INDEX(Sheet1!$B$1:$E$1,INT((ROWS($1:1)-1)/6)+1)&OFFSET(Sheet1!$A$2,MOD(ROWS($1:1)-1,6),) In B2: =IF(MOD(ROWS($1:1)-1,6)=0,-OFFSET(Sheet1!$B$2,MOD(ROWS($1:1)-1,6),INT((ROWS($1:1)-1)/6)),OFFSET(Sheet1!$B$2,MOD(ROWS($1:1)-1,6),INT((ROWS($1:1)-1)/6))) Select A2:B2, copy down as far as required to exhaust the data. It seems to return the exact results that you seek Adapt the above to suit your actual 55 acc nos (the col headers) & 30 cost centres (the row headers): 1. For the formula in A2, change Sheet1!$B$1:$E$1 to Sheet1!$B$1:$BD$1 (since you have 55 acc nos/col headers) 2. For both formulas in A2 & B2, change all the "6" instances within the INT and MOD bits -- which refer to the # of cost centre items in A2:A7 -- to "30" (that's your actuals) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,000 Files:362 Subscribers:62 xdemechanik --- "suzilea" wrote: I'm sure this an ongoing issue for some, but here's an example of my data: 4010 4015 4030 4040 94 1000.00 2000.00 3000.00 1000.00 10 244.44 488.89 733.33 244.44 20 266.67 533.33 800.00 266.67 30 266.67 533.33 800.00 266.67 40 133.33 266.67 400.00 133.33 50 88.89 177.78 266.67 88.89 We are trying to create an allocation template in Excel that used to store/process information vertically, but was a monster to maintain. So, we're trying to store/process horizontally. The problem is, on the second tab I want to generate a list basically to import into the g/l software as follows: 401094 -1000.00 401010 244.44 401020 266.67 401030 266.67 401040 133.33 401050 88.89 401594 -2000.00 401510 488.89 etc.. Is there an easy way to do this? The first row contains account numbers (right now I have 55) and the first column contains cost center (right now I have 30). Thanks for any help you guys can offer! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying formulas but changing the column # | Excel Discussion (Misc queries) | |||
Copying a Column of Formulas between Excel Files | Excel Worksheet Functions | |||
copying a column of cells containing formulas without them changin | Excel Discussion (Misc queries) | |||
Copying formulas to end of Column | Excel Discussion (Misc queries) | |||
Copying Formulas From One Column To Another?? | Excel Worksheet Functions |