Skip to content

Turning a table into a list in Excel

The yomim tovim are over, so I will hopefully have a chance to play with jQuery again. But on the programming side, one thing I had to do was turn a table of honors, something like this:
Rosh Hashana 1Rosh Hashana 2Yom Kippur
MaarivPerson 1Person 2Person 3
ShacharitPerson 4Person 5Person 6
MusafPerson 7Person 8Person 9
And turn it into this:
Rosh Hashana 1MaarivPerson 1
Rosh Hashana 1ShacharitPerson 4
Rosh Hashana 1MusafPerson 7
Rosh Hashana 2MaarivPerson 2
Rosh Hashana 2ShacharitPerson 5
Rosh Hashana 2MusafPerson 8
Yom KippurMaarivPerson 3
Yom KippurShacharitPerson 6
Yom KippurMusafPerson 9
I used code like this:

Sub createlist()
    Dim source As Range
    Set source = Range("B2:D4")
    Dim dest As Range
    Set dest = Range("F1")
    dest.Range("A1:C1").Value = Array("When", "What", "Who")
    Dim target As Range
    Dim c As Range
    For Each c In source.Cells
        If Not IsEmpty(c.Value) Then
            If IsEmpty(dest.Range("A2").Value) Then
                Set target = dest.Range("A2")
                Set target = dest.End(xlDown).Range("A2")
            End If
            target.Range("C1").Formula = "=" & c.Address
            target.Range("B1").Formula = "=" & c.EntireRow.Range("A1").Address ' row header
            target.Range("A1").Formula = "=" & c.EntireColumn.Range("A1").Address ' column header
        End If
    Next c
End Sub

The key tricks was getting column/row headers with .EntireColumn/Row.Range("A1"), and appending to the end of a list with .End(xlDown).Range("A2"). Unfortunately, End(xlDown) is too clever; if the region contains only one item (like the column header only), it goes all the way to the end of the spreadsheet and the .Range("A2"), which should get the next line, throws an error. Hence the If IsEmpty(dest.Range("A2").Value) Then.

The If Not IsEmpty(c.Value) Then line allows for blank spots in the original table to be ignored. VBA doesn't have a Continue statement, just an equivalent for break, called Exit For.

Post a Comment

Your email is never published nor shared. Required fields are marked *