How to create a summary (categorized) report from a table

It is easier to help when we can see expected results instead of the incorrect results yielded by formulas.
That said, it seems that the following in cells B6 and down on the Charge Report sheet will display what you want:

Formula:

=IFERROR(INDEX(PatientName,AGGREGATE(15,6,(ROW(Census!M$6:M$45)-ROW(Census!M$5))/(Census!M$6:M$45="x")/(PatientName<>""),ROWS(B$5:B5)))&" - "&INDEX(Census!B$6:B$45,AGGREGATE(15,6,(ROW(Census!M$6:M$45)-ROW(Census!M$5))/(Census!M$6:M$45="x")/(PatientName<>""),ROWS(B$5:B5))),"")

Let us know if you have any questions.

I am away from my computer for a few days. Will be glad to look at this when I am able.

I believe that this modification of the formula in post #2 will do what you want:

Formula:

=IFERROR(INDEX(PatientName,AGGREGATE(15,6,(ROW(Census!M$6:M$45)-ROW(Census!M$5))/(Census!M$6:M$45="x")/(PatientName<>""),ROWS(B$5:B5)))&" - "&INDEX(Census!B$6:B$45,AGGREGATE(15,6,(ROW(Census!M$6:M$45)-ROW(Census!M$5))/(Census!M$6:M$45="x")/(PatientName<>""),ROWS(B$5:B5))),"")&IFERROR(" - "&TEXT(1/(1/INDEX(Census!AD$6:AD$45,AGGREGATE(15,6,(ROW(Census!M$6:M$45)-ROW(Census!M$5))/(Census!M$6:M$45="x")/(PatientName<>""),ROWS(B$5:B5)))),"m/d"),"")

Let us know if you have any questions.

Thank you for your modified code but I tried it and doesn't seem to populate data correctly. I have attached the WB for your review. In the example, column AB ('Foley') has 3 dates and 1 'X' in various rows. The formula populates only 1 name without the date and not the others. Not sure if I replaced the column references correctly so could be totally my error.

Also, I just discovered a problem with the first solution you provided. On the Census WS, I have all cells with 'X's in a 2 columns to test if it populates the whole corresponding columns in the worksheet Charge Report (B5:B45 and C5:C45). It doesn't not populate the last cell in the 2 columns on the Census (J6:J45, N6:N45). I.e. it falls stops 1 short of the full list on the Census (it should show 'TEST20 Bed 40' as the final item in both lists).

Your setup is not really condusive to your requirement...
See result sheet...Headings are in order of census sheet...so perhaps change accordingly...

Sub J3v16()
Dim Data, Temp, Arr, Crit As String, i As Long, ii As Long, x As Long
Arr = [{8,9,10,11,12,13,14,15,20,21,22,23,24,25,26,27,28,29}]
Data = Sheets("census").ListObjects(1).Range
ReDim Temp(1 To UBound(Data), 1 To 18)
For i = 2 To UBound(Data)
    If Data(i, 3) <> "" Then
        Crit = Data(i, 3) & " Bed " & Data(i, 1)
        x = x + 1
        For ii = 1 To UBound(Arr)
            If Data(i, Arr(ii)) <> "" Then
                Temp(x, ii) = IIf(IsDate(Data(i, Arr(ii))), Crit & " " & Data(i, Arr(ii)), Crit)
            End If
        Next ii
    End If
Next i
With Sheets("Result") '! For testing purposes...
    .Rows(2 & ":" & .Rows.Count).Delete
    .Cells(2, 1).Resize(x, 18) = Temp
    .UsedRange.Columns.AutoFit
End With
With Sheets("Charge Report") '! Change headings accordingly...
    .Rows(4 & ":" & .Rows.Count).Delete
    .Cells(4, 2).Resize(x, 18) = Temp
    .UsedRange.Columns.AutoFit
End With
End Sub

There is a fair bit of scope to simplify those horrible formulas!

I agree...Do away with them altogether...
Sadly as your dataset grows...your manual manipulation and opportunity for error increases...and then you get left with, as per your sample, one big table with blank data and a summary sheet that is limited only to what formulas are allowing...
Go with automation...not formulation...