How can I extrapolate a table into a list

Copper Contributor



I've spend a fruitless day trying to do what I know is a simple task. And utterly failed.  I've a schedule of workshops over 4 days taking place in up to 6 different rooms.  When we work out which workshop is going where it can be dumped into a timetable.  So far so glorious.  Now I just want to be able to squirt out the workshop numbers for each day, in day/time order, with the room number next to it.  What could be simpler.  Damned if I know.  Would it be easier to use Word to get this list? 

4 Replies
best response confirmed by Jane_at_Stamma (Copper Contributor)


Sub wotkshops()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim MaxRow As Integer


MaxRow = Range("A" & Rows.Count).End(xlUp).Row

k = 2
For i = 3 To MaxRow

For j = 3 To 8

If Cells(i, j) <> "" Then
Cells(k, 11).Value = Cells(i, 1).Value
Cells(k, 12).Value = Cells(i, 2).Value
Cells(k, 13).Value = Cells(2, j).Value
Cells(k, 14).Value = Cells(i, j).Value
k = k + 1

End If

Next j

Next i

Range(Cells(2, 11), Cells(k, 11)).NumberFormat = "hh: mm"
Range(Cells(2, 12), Cells(k, 12)).NumberFormat = "dddd dd"

End Sub

You can try this code. In the attached file you can click the button in cell J3 to start the macro.


This option requires 365 insider beta at present.

= LET(
      wshp, TOCOL(ScheduleTbl),
      room, TOCOL(IF(ScheduleTbl<>"", rooms)),
      day,  TOCOL(IF(ScheduleTbl<>"", days)),
      time, TOCOL(IF(ScheduleTbl<>"", times)),
      list, HSTACK(wshp,room,day,time),
      SORT(FILTER(list, wshp<>""),3)
**bleep** I wish I could do that. Beautiful and super swift, thank you SO MUCH! I should have asked first thing rather than spewing out countless faulty sheets. That is fantastic.
Hi Peter - That LOOKS pretty neat. I'm not sure I have 365 insider beta, so I've gone with Quadruple_Pawn's solution. But thank you. Such a brilliant community.