Import Excel Spreadsheet - Move Data to the Correct Record

[j.t.w]

Hello Everyone,

I'm trying to import data that is currently in an Excel spreadsheet
into an Access table. The data (information) looks fine and somewhat
makes sense as you look at it as a spreadsheet. Unfortunately, it won't
import well into a table unless I move cells around before I import or
somehow upon import programmatically move the data to the correct
record. I would like to automate this process if at all possible, as
there are many spreadsheets in this format.

Here is what the spreadsheet looks like...

.. A B C D E F G H I J K L
1 101 John Doe 123 102 Jane Doe 459 103 Mary Smith 985
2 456 126 653
3 789 378 167
4 159 453 439

The numbers in column D, H, and L are dollar amounts. The amounts in
column D belongs to 101 John Doe. The amounts in column H belongs to
102 Jane Doe. The amounts in column L belongs to 103 Mary Smith. And so
on.

Here is what I would like...

.. A B C D E F G
1 101 John Doe 123 456 789 159
2 102 Jane Doe 459 126 378 453
3 103 Mary Smith 985 653 167 439

There are usually more than 3 people(typically 12 to 20, or sometimes
even more), which is why I would like to automate this. Could someone
point me in the right direction or have any suggestions on how to
accomplish this? Any and all help would be appreciated.
Thank you.
j.t.w

 

[j.t.w]

Dear Joseph Meehan,

Thank you for responding. Sorry I didn't get back with you earlier.

I wasn't thinking when I posted my question and as you've suggested,
importing the data into 2 separate tables would be better.

How would I go about preparing the excel spreadsheet to import the data
as you suggest?

As of right now (as far as I can tell), there are only four dollar
amounts per person, which hopefully makes this easier. Although,
sometimes there are blank dollar amounts in the 2nd, 3rd, or 4th rows,
which I would consider 0.

Again, thanks for your help.

j.t.w