Preparing Excel Data for a DB

[zima8172]

Hi,

I have Excel sheets containing financial data in the form:


AccountDesc|Jan 1|Jan2|Jan 3 .......
Salaries|value1|value3|value5 .......
Bonuses|value2|value4|value6 .......
.......

I need to bring it to the form:

Date|AccountDesc|Value
Jan1|Salaries|value1
Jan1|Bonuses|value2
.......
Jan3|Salaries|value5
.......

Your assistance is much appreciated.
Thanks
Polar

 

[jindon]

Back up the file first and try the code

Sub test()
Dim a, i As Integer, ii As Long, b(), n As Long, x As Long
With ActiveSheet
   With .Range("a1").CurrentRegion
      a = .Value
      x = .Rows.Count  * .Columns.Count  * 2 
      ReDim b(1 To x, 1 To 3)
      .Clear
   End With
   b(1,1) = "Date" : b(1,2) = "AccountDesc" : b(1,3) = "Value"
   n = 1
   For i = 2 To UBound(a,2)
      For ii = 2 To UBound(a,1)
         n = n + 1
         b(n,1) = a(i,1) : b(n,2) = a(ii,1) : b(n,3) = a(ii,i)
      Next
   Next
   .Range("a1").Resize(n,3).Value = b
End With
End Sub

 

[zima8172]

Thanks a lot for your help. Your code has generated the following:

Initial Fields:
AccountDesc|Date1|Date2|Date3|Date4....|DateN
Cash receipts|$v1|$v2|$v3|$v4|....|$vn
Loan|$v1-1|$v1-2|$v1-3|$v1-4|....|$v1-n
Payroll|$v2-1|$v2-2|$v2-3|$v2-4....|$v2-n
......
Consulting|$vn-1|$vn-2|$vn-3|$vn-4.....|$vn-n



Change to:

Date|AccountDesc|Value
Cash receips|Cash receipts|$v1
Cash receipts|Loan|$v1-1
Cash receipts|Payroll|$v2-1
....

Seems that the code takes AccountDesc and places it (repeating) into the first column instead on Date fileds.

Really appreciate your help
Polar

 

[jindon]

Can you just change

b(n,1) = a(i,1) : b(n,2) = a(ii,1) : b(n,3) = a(ii,i)

to

b(n,1)=a(1,i) : b(n,2) = a(ii,1) : b(n,3) = a(ii,i)

 

[zima8172]

Thanks a lot! I actually have figured out the change in formula and got it to work. Really appreciate it!