Forum Discussion
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?
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 SubYou 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) )- 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.
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 SubYou can try this code. In the attached file you can click the button in cell J3 to start the macro.
- **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.
