Complicated Transposition Challenge

[dancole42]

I need help converting a sheet in Excel to a table in Access, but the data in Excel is arranged poorly. Can anyone offer some suggestions?

The Excel table has headers corresponding to each country, and then each record is some data on that country (and yes, there are blanks).

UK US AU
1   2   2
2   1   2
2   3   2
1       1
1
1

I want my table to look like:

UK 1
UK 2
UK 2
UK 1
UK 1
UK 1
US 2
US 1
US 3
AU 2
AU 2
AU 2
AU 1

Any suggestions?

 

[Beetle]

Assuming a table of imported spreadsheet data (tblImport) that looks like this;

And a new table (tblNewData) with the fields CountryCode and DataValue;

Then looping through a recordset of the import table with code like the following;

Private Sub Command4_Click()
  
    Dim strUpdate As String
    Dim i As Long
    
    With CurrentDb.OpenRecordset("Select * From tblImport", dbOpenSnapshot)
        .MoveFirst
        Do While Not .EOF
            For i = 0 To .Fields.Count - 1
                If Nz(.Fields(i).Value, "") <> "" Then
                    strUpdate = "Insert Into tblNewData (CountryCode, DataValue) " _
                           & "Values (""" & .Fields(i).Name & """, """ & .Fields(i).Value & """);"
                    CurrentDb.Execute strUpdate, dbFailOnError
                End If
            Next
            .MoveNext
        Loop
    End With
  
End Sub

Gives these results in the new table;

 

[dancole42]

You are a beautiful, beautiful man... thank you!!!