{"id":158,"date":"2008-10-23T23:29:45","date_gmt":"2008-10-24T05:29:45","guid":{"rendered":"http:\/\/bililite.nfshost.com\/blog\/?p=158"},"modified":"2008-10-23T23:30:44","modified_gmt":"2008-10-24T05:30:44","slug":"turning-a-table-into-a-list-in-excel","status":"publish","type":"post","link":"https:\/\/bililite.com\/blog\/2008\/10\/23\/turning-a-table-into-a-list-in-excel\/","title":{"rendered":"Turning a table into a list in Excel"},"content":{"rendered":"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:\r\n\r\n\r\n
<\/td>Rosh Hashana 1<\/td>Rosh Hashana 2<\/td>Yom Kippur<\/td><\/tr>\r\n
Maariv<\/td>Person 1<\/td>Person 2<\/td>Person 3<\/td><\/tr>\r\n
Shacharit<\/td>Person 4<\/td>Person 5<\/td>Person 6<\/td><\/tr>\r\n
Musaf<\/td>Person 7<\/td>Person 8<\/td>Person 9<\/td><\/tr>\r\n<\/tbody>\r\n<\/table>\r\nAnd turn it into this:\r\n\r\n\r\n<\/tbody>\r\n<\/table>\r\n\r\nI used code like this:\r\n
\r\nSub createlist()\r\n    Dim source As Range\r\n    Set source = Range(\"B2:D4\")\r\n    Dim dest As Range\r\n    Set dest = Range(\"F1\")\r\n    dest.Range(\"A1:C1\").Value = Array(\"When\", \"What\", \"Who\")\r\n    Dim target As Range\r\n    Dim c As Range\r\n    For Each c In source.Cells\r\n        If Not IsEmpty(c.Value) Then\r\n            If IsEmpty(dest.Range(\"A2\").Value) Then\r\n                Set target = dest.Range(\"A2\")\r\n            Else\r\n                Set target = dest.End(xlDown).Range(\"A2\")\r\n            End If\r\n            target.Range(\"C1\").Formula = \"=\" & c.Address\r\n            target.Range(\"B1\").Formula = \"=\" & c.EntireRow.Range(\"A1\").Address ' row header\r\n            target.Range(\"A1\").Formula = \"=\" & c.EntireColumn.Range(\"A1\").Address ' column header\r\n        End If\r\n    Next c\r\nEnd Sub\r\n<\/code><\/pre>\r\n\r\n
The key tricks was getting column\/row headers with .EntireColumn\/Row.Range(\"A1\")<\/code>, and appending to the end of a list with\r\n.End(xlDown).Range(\"A2\")<\/code>. Unfortunately, End(xlDown)<\/code> 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\")<\/code>, which should get the next<\/em> line, throws an error. Hence the If IsEmpty(dest.Range(\"A2\").Value) Then<\/code>.<\/p>\r\n
The If Not IsEmpty(c.Value) Then<\/code> line allows for blank spots in the original table to be ignored. VBA doesn't have a Continue<\/code> statement, just an equivalent for break<\/code>, called Exit For<\/code>.","protected":false},"excerpt":{"rendered":"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 […]","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"_links":{"self":[{"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/posts\/158"}],"collection":[{"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/comments?post=158"}],"version-history":[{"count":11,"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/posts\/158\/revisions"}],"predecessor-version":[{"id":169,"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/posts\/158\/revisions\/169"}],"wp:attachment":[{"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/media?parent=158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/categories?post=158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/tags?post=158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
When<\/td>What<\/td>Who<\/td><\/tr>\r\n
Rosh Hashana 1<\/td>Maariv<\/td>Person 1<\/td><\/tr>\r\n
Rosh Hashana 1<\/td>Shacharit<\/td>Person 4<\/td><\/tr>\r\n
Rosh Hashana 1<\/td>Musaf<\/td>Person 7<\/td><\/tr>\r\n
Rosh Hashana 2<\/td>Maariv<\/td>Person 2<\/td><\/tr>\r\n
Rosh Hashana 2<\/td>Shacharit<\/td>Person 5<\/td><\/tr>\r\n
Rosh Hashana 2<\/td>Musaf<\/td>Person 8<\/td><\/tr>\r\n
Yom Kippur<\/td>Maariv<\/td>Person 3<\/td><\/tr>\r\n
Yom Kippur<\/td>Shacharit<\/td>Person 6<\/td><\/tr>\r\n
Yom Kippur<\/td>Musaf<\/td>Person 9<\/td><\/tr>\r\n