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

workshop list.xlsx15 KB

BI & Data Analysis

excel

Formulas and Functions

OliverScheurich

Jul 03, 2022

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.

workshops.xlsm20 KB

PeterBartholomew1

Silver Contributor

Jul 03, 2022

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

Jane_at_Stamma

Copper Contributor

Jul 03, 2022

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.

Forum Discussion

How can I extrapolate a table into a list

Resources