Solved: Convert To Database Format

[jmenche]

I received a large spreadsheet that is not in database format because it would not have fit. Basically, its a spreadsheet of product sales by store. There are over 4000 stores and those fields are fine. The problem is that there are over 200 items and each is its own field. If I import this table into Access, is there a way that I can create a new table in 3rd normal form?

I have attached a sample of the data.

Thanks for any help!

[Norie]

Are the products in columns G to HJ?

[jmenche]

Yes! Sorry bout that. I Know that I need a new field called 'Product' and one field called 'Dollar Sales'

Thanks for any help

[OBP]

You can create a table of the product names/codes by simply copying them and on a seperate sheet "Paste Special - Transpose" the data. That will give you a column of "products which you can then import.

[Norie]

Jmenche

Try the attached.

You will need to have a database called Test.mdb in the root of the C:\ drive.

OBP's idea is also a good idea - the process of copying pasting could easily be automated.

[jmenche]

WOOHOO! Thanks Norie!

The transpose wouldn't work because there are really over 4,000 stores. I copied the original data into your worksheet and the macro created a table of nearly 1,000,000 records.

[Norie]

macro created a table of nearly 1,000,000 records.

Is that what you wanted?

As I recall there were over 200 products.

And you have over 4000 stores.

So >4000x>200 is probably going to be >1,000,000.