Excel Database Conversion (Record Creation)
 

I am often faced with the task of moving data from excel workbooks
into numerous databases (Access, OLAP, SQL). Here is my challenge:

I have a spreadsheet with the months listed in a row and the accounts
listed in a column, I need to create a record for each account and
month intersection, see second example below. I have been doing this
by simply cutting and pasting but the spreadsheets are growing faster
that I can convert them, and there has to be a more efficient way of
accomplishing this task. I am not trying to write VBA.


Aug Sep
Cash 10 50
Stocks 20 60
Bonds 30 70
Property 40 80


Aug Cash 10
Aug Stocks 20
Aug Bonds 30
Aug Property 40
Sep Cash 50
Sep Stocks 60
Sep Bonds 70
Sep Property 80

Excel Database Conversion (Record Creation)
 

But do you want someone else to write some VBA, or are you just not
allowed to use a macro solution?

Pete

On Sep 21, 9:10 pm, banker123 wrote:
I am often faced with the task of moving data from excel workbooks
into numerous databases (Access, OLAP, SQL). Here is my challenge:

I have a spreadsheet with the months listed in a row and the accounts
listed in a column, I need to create a record for each account and
month intersection, see second example below. I have been doing this
by simply cutting and pasting but the spreadsheets are growing faster
that I can convert them, and there has to be a more efficient way of
accomplishing this task. I am not trying to write VBA.

Aug Sep
Cash 10 50
Stocks 20 60
Bonds 30 70
Property 40 80

Aug Cash 10
Aug Stocks 20
Aug Bonds 30
Aug Property 40
Sep Cash 50
Sep Stocks 60
Sep Bonds 70
Sep Property 80

Excel Database Conversion (Record Creation)
 

Not allowed to use a macro solution.

Excel Database Conversion (Record Creation)
 

It looks like you could use John Walkenbach's "reverse pivot table" technique:
http://www.j-walk.com/ss/excel/usertips/tip068.htm

You can move the columns to the order you want and then sort the data by
whatever column you want.



banker123 wrote:

I am often faced with the task of moving data from excel workbooks
into numerous databases (Access, OLAP, SQL). Here is my challenge:

I have a spreadsheet with the months listed in a row and the accounts
listed in a column, I need to create a record for each account and
month intersection, see second example below. I have been doing this
by simply cutting and pasting but the spreadsheets are growing faster
that I can convert them, and there has to be a more efficient way of
accomplishing this task. I am not trying to write VBA.

Aug Sep
Cash 10 50
Stocks 20 60
Bonds 30 70
Property 40 80

Aug Cash 10
Aug Stocks 20
Aug Bonds 30
Aug Property 40
Sep Cash 50
Sep Stocks 60
Sep Bonds 70
Sep Property 80

--

Dave Peterson