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

Hello,

I have a spreadsheet with a structured table (Census) that has names (column D6:D45). There are categories that are checked for each patient. I need a summary page (Charge Report sheet) that returns categorized lists (12) with the names and corresponding bed numbers from the table.

Thank you for any assistance that can be provided!


2N Charge Nurse Tool v2.1.xlsm

JeteMc,
Thanks so much. Just what I was looking for. An additional tweak that would help (but not critical) is that some of the columns of data may have a date entered instead of an 'X'. Is it possible to include the entered date within the returned results if present? For example, if a date is entered in the column 'Falls' (col AD), can the returned result be 'PatientName, Bed, Date'?

Again, thank you so much for your expertise and quick and effective solution.

Thank you.

Hi joeystraw

I used your file in post#7 and fixed the formulas on sheet [Charge Report]
It now doesn't fall short of the full list on the [Census] sheet.

I also added a formula to include the patient, bed, date in the Foley section as you requested.
I could get rid of the unwanted X in the Foley if you really want, but perhaps you might like to try first.

Hope you find this useful.
There is a fair bit of scope to simplify those horrible formulas!

zeddy

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.

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

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.