"Transposing" some columns with ID fields into rows while copying the other data

Question

Suppose I inherited a table in Excel that dealt with processes and individuals, which (among thousands of other rows) looks similar to this.

ID             | Name        | Quality1   | Quality2   | ... | QualityN   |
...
234,014,828,423  James         Low          Hot           .    Blue
212,552,211      Mark          Low          Cold          .    Red
845              Amy           High         Hot           .    White
...

I intend to use this data in Access later, as a reference table where each ID number is distinct and has data to go along with it. The first step is obviously to use the text to column tool in Excel to break down the ID category. This leaves us with something that looks like this.

ID |   |   |   | Name        | Quality1   | Quality2   | ... | QualityN   |
... 
234 014 828 423  James         Low          Hot           .    Blue
212 552 211      Mark          Low          Cold          .    Red
845              Amy           High         Hot           .    White
...

The next part leaves me stuck however. What is the process required (using only Excel, Access, and the associated VBA) that allows me to end up with my desired results?

ID             | Name        | Quality1   | Quality2   | ... | QualityN   |
... 
234              James         Low          Hot           .    Blue
014              James         Low          Hot           .    Blue
828              James         Low          Hot           .    Blue
423              James         Low          Hot           .    Blue
212              Mark          Low          Cold          .    Red
552              Mark          Low          Cold          .    Red
211              Mark          Low          Cold          .    Red
845              Amy           High         Hot           .    White
...

My intuition is telling me to use a number table ranging from 0 to 9999 and then to JOIN over the data bit by bit in Access, but that is code and time intensive, as well as incredibly brutish and inflexible. Is there a more elegant method out there for me to craft my solution?

Answer 1

If you'd like to do this with Excel before it gets to Access, What about a simple nested for loop that grabs the data, and puts in in another sheet?

This little function will do just that:

Option Explicit

Function Consolidate()

    Dim x As Long, y As Long, z As Long
    Dim OutputRow As Long, OutputCol As Long
    Dim MaxRow As Long, MaxIdCol As Long, MaxCol As Long
    Dim HomeSheet As String, NewSheet As String

    'Initialize
    MaxRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row 'Last row with an ID
    MaxIdCol = Cells(1, 1).End(xlToRight).Column - 1 ' Last column with an ID
    MaxCol = Cells(1, Cells.Columns.Count).End(xlToLeft).Column 'Last column overall
    HomeSheet = ActiveSheet.Name 'Where we start from
    NewSheet = Sheets.Add.Name 'Place to put new stuff
    OutputRow = 0 'Counter for where we're putting data

    'Loop over each row
    For x = 1 To MaxRow
        'Loop over each ID column in that row
        For y = 1 To MaxIdCol
            'Is there an ID in this cell?
            If Sheets(HomeSheet).Cells(x, y) <> "" Then
                'Reset loop variables
                OutputRow = OutputRow + 1
                OutputCol = 1
                'Drop the ID in
                Sheets(NewSheet).Cells(OutputRow, OutputCol) = Sheets(HomeSheet).Cells(x, y)
                'copy over the other values
                For z = MaxIdCol + 1 To MaxCol
                    OutputCol = OutputCol + 1
                    Sheets(NewSheet).Cells(OutputRow, OutputCol) = Sheets(HomeSheet).Cells(x, z)
                Next z
            End If
        Next y
    Next x

End Function