How can I extrapolate a table into a list

[Jane_at_Stamma]

Hi

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? 

[#2 · Accepted solution]

@Jane_at_Stamma 

Sub wotkshops()

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

Range("K:N").Clear

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

Else
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.

[#3]

@Jane_at_Stamma 

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)
  )

[#4]

**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.

[#5]

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.