Normalize tabled data in Excel

[scriggs]

Hi

I have been provided some data in Excel which I want to normalize and put into Access.

The data comes in like

Weeks and department across here
Sales Sales Sales Sales Ops Ops Ops
Name 1 2 3 4 1 2 3
-----------------------------------------------------
Mark 100 50 40 40 20 30 70
John 70 20 30 50 30 40 80
Pete 80 90 100 20 50 30 90
etc.

I want to normalize like:

Name Week Department Data
-----------------------------------
Mark 1 Sales 100
John 1 Sales 70
Pete 1 Sales 80
Mark 2 Sales 50
Mark 1 Ops 20
etc.

Any ideas how to tackle this - at the moment I am doing very manually in Excel.

 

[dhookom]

What are the actual field names in your "comes in like" table? You could use a union query to normalize.

Also, are the "comes in like" fields going to change over time? Do you need a solution that automatically detects new columns?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[scriggs]

Hi MVP

The fields are not constant and are supplied by a 3rd party - they will change everytime so need to be detected.

The structure is the same with week and dept across the top 2 rows and name down first column. Then any number of columns (can be 255) with the data in each column.

I guess the only way would be to use VBA to open the Excel file and loop through each column but I don't know where to even start with that.

 

[tav1035]

scriggs,
Copy and tranpose.
Then pull John's data down under Mark's.
Then pull Pete's data under John's.
Use this fill handle to fill in the name properly or paste it into the next seven records.
Copy all seven departments and week indicators and it paste below the data that you copied. Do this again.
This is normalized the hard way. I once did over 10,000 record this way, not fun. But the key is that everything has a sequence of seven because you have seven columns of data. Maybe possible to run a macro that loops.
tav

 

[abaldwin]

I have done the same thing with almost a full sheet of data in excel 60k plus lines. It would be to your best interest to write a vba macro to clean up the data in the first sheet by looping and writing records to a different sheet and then import form the new sheet.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.