import rows from excel into Access

[Westley via AccessMonster.com]

I have an excel spreadsheet with the following data in row format. This shows
the project, the employee name and the total hours for each employee for that
month. sample below:

resource jan feb mar
project#1
susan 40 40 40
jeff 40 40 40
mary 20 30 10
project#2
colin
ray
joe


i need to import this information into access so that the project name is a
column and the employee name is a column.
jan feb mar
susan project#1 40 40 40
jeff project#1 40 40 40
mary project#1
colin project#2
ray project#2
joe project #2


Does anyone know how to do this?

Thanks,
Westley

 

[John Nurick]

Hi Westley,

Is this a one-off task or something that will need to be done regularly
under program control?

Assuming it's a one-off, I'd do it by adding a "project" column to the
worksheet. I'd put a formula in the cells of this column that looked at
the value in the adjacent cell in the "resource" column. If that value
is a project number, the formula would display it; otherwise it would
get the value from the cell above.

Assuming "resources" are in column A and "projects" in column B, and
that the "#" character is what distinguishes a project number, the
formula in C2 could be

=IF(ISERR(FIND("#",B2)),C1,B2)

and the result would be

resource project jan feb mar
project#1 project#1
susan project#1 40 40 40
jeff project#1 40 40 40
mary project#1 20 30 10
project#2 project#2
colin project#2
ray project#2
joe project#2
....

Having got this far, save the workbook and import the data. Finally, use
a delete query to get rid of all records where the "resource" field
contains a project number.