Preparing Excel Data for a DB

zima8172

New Member
Joined
Nov 25, 2005
Messages
26
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Back up the file first and try the code
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

New Member
Joined
Nov 25, 2005
Messages
26
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

New Member
Joined
Nov 25, 2005
Messages
26
Thanks a lot! I actually have figured out the change in formula and got it to work. Really appreciate it!
 

Forum statistics

Threads
1,136,926
Messages
5,678,618
Members
419,776
Latest member
mikelowski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top