Importing Excel data
 

Hello, I am fairly inexperienced with Excel. I need to
import data from Excel into an Access database. My
problem is that the data in Excel is not "Normalized".
For example, one record in Excel contains one-to-many
relationships. See example below for one record:

record 1: [ClaimNumber1], [ClaimName], [AssignedAtty],
[InjuryCode1], [InjuryCode2], [InjuryCode3],
[InjuryCode4], ...

There can be up to 25 Injury Codes per claim. In Access I
have a table that stores the Claim information where the
[ClaimNumber] is the primary key] and another table that
stores the [InjuryCodes] per [ClaimNumber] where the
combination of those fields are the primary key.

In order to import this data into my "Normalized" tables
in Access, I believe I need to first transfer the Injury
codes to a separate spreadsheet while still referencing
the claim number. I am assuming that there will need to
be multiple records per claim. Example:

record 1: [ClaimNumber1], [InjuryCode1]
record 2: [ClaimNumber1], [InjuryCode2]
record 3: [ClaimNumber1], [InjuryCode3]
record 4: [ClaimNumber1], [InjuryCode4]

Do I need VBA to do this? If yes, are there resources to
assist with this type of task since I have no experience
with adding VBA in Excel Please let me know if you need
additional information. I appreciate any help offered.

Thank you.

Importing Excel data
 

Copy/paste the code below into a macro code module.
It will make a new Excel sheet and copy data into it.
I assume that the data is in separate cells.

'-------------------------------------------------------
Sub test()
Dim FromSheet As Worksheet
Dim ToSheet As Worksheet
Dim FromRow As Long
Dim ToRow As Long
Dim FromCol As Integer
Dim ClaimNumber
'------------------------------
Set FromSheet = ActiveSheet
Set ToSheet = Worksheets.Add
FromRow = 2
ToRow = 2
'-----------------------------
While FromSheet.Cells(FromRow, 1) < ""
ClaimNumber = FromSheet.Cells(FromRow, 1).Value
FromCol = 4 ' FIRST COLUMN WITH CODE NUMBER
While FromSheet.Cells(FromRow, FromCol).Value < ""
ToSheet.Cells(ToRow, 1).Value = ClaimNumber
ToSheet.Cells(ToRow, 2).Value = FromSheet.Cells(FromRow
2).Value
ToSheet.Cells(ToRow, 3).Value = FromSheet.Cells(FromRow
3).Value
ToSheet.Cells(ToRow, 4).Value = FromSheet.Cells(FromRow
FromCol).Value
FromCol = FromCol + 1
ToRow = ToRow + 1
Wend
FromRow = FromRow + 1
Wend
End Sub
'--------------------------------------------------------

--
Message posted from http://www.ExcelForum.com