Copying formulas into column from row information

[suzilea]

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!

 

[Joel]

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

 

[Max]

One play ..

Assuming your source table as posted is in Sheet1, where
in B1:E1 are: 4010, 4015, 4030, 4040
in A2:A7 are: 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