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

**Sintek** · 08-19-2024, 03:47 AM

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

**joeystraw** · 08-19-2024, 08:20 PM

Thank you Sintek, but not quite what I was looking for as far as formatted return results. JeteMc's original solution was exactly what I was looking for as far as how the results returned (except some minor adjustments to produce the desired results) because I need the data to be accumulated and grouped in a summary report format rather than one with blank spaces such as yours.

Also in response to your subsequent post, yes...the formula that JeteMc provided me is extensive and complicated, but my data set will not grow because the Charge Report (which is a summary report of the Census worksheet) is only extracting data from a max of 40 possible records at any given time based on the Census worksheet, so it is self-limiting.

I am grateful and appreciative for your attention and time in coming up with another solution to my problem.

**zzzeddy** · 08-20-2024, 08:59 AM

Hi

..so did you look at my post#9 above???
..did you check my formula changes to fix your falling short issue??
..and I also added a formula to include the patient, bed, date in the Foley section as you requested.

zeddy

**joeystraw** · 08-22-2024, 03:58 PM

Hi zzzeddy,
So sorry, I replied to your post but didn't realize that it didn't post for some reason. Yes, your modified formulas were exactly what I needed. The inclusion of the date in that format works nicely. Thank you so much.

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

How to create a summary 'report'?

[SOLVED] How to create a summary report from several files???

Trying to Create a Summary Report

Is it possible to create a summary report?

[SOLVED] How to create summary of expense report?

not able to create subtotal summary report

How to create summary of expense report?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions