SOLVED

# How can I extrapolate a table into a list

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?

4 Replies
best response confirmed by Jane_at_Stamma (Copper Contributor)
Solution

# Re: How can I extrapolate a table into a list

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

# Re: How can I extrapolate a table into a list

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

# Re: How can I extrapolate a table into a list

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

# Re: How can I extrapolate a table into a list

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.