Collate most common text values from multiple worksheets + more

[zookcruzr]

Hello all,
This is a twofold Question/Post so please specify which one you are answering or querying about.

I have a spreadsheet recording details of incidents that occur in my area with individual worksheets for every month.
These incidents are broken down with the following columns;

Date

Event No

Patient Name

Offence or MH condition

Drug Related?

Alcohol Related?

Suburb

Time at Scene (Record Minutes)

Travel Time

Hospital Waiting Time

Return travel time

Total Time

Officers Involved

Sec 22 Police vehicle Escort

Sec 22 Ambulance with police Escort

Sec 20 Ambuance with police Escort

Section 20 Ambulance Escort

Other

As you can see, I'm not really in a position to post the whole spreadsheet as it contains personal information.

The last worksheet I have a formatted report to print off giving summaries of various fields for the year, broken down by the month.

Q1:
For each month I have the most common "offence or MH condition". This field HAS to be a free text field, so I cannot create a standard list for drop downs.
At the moment I am using the following array formula;
{=INDEX('Jan 14'!D:d,MATCH(MAX(COUNTIF('Jan 14'!D:d,'Jan 14'!D:d)),COUNTIF('Jan 14'!D:d,'Jan 14'!D:d),0))}
- it is D: D but it converted this to hence the lower case d - and yes I know it doesn't matter with case lol -

My question is, whether or not I can do this without using an array formula, as it slows down the data entry for the fields it refers to.
I am not against using a macro, BUT, I am not familiar or versed in the process.

Q2:
I'd like to collate the "top 20" patients and how many times they have been involved for that year.
The issue I am having is referencing multiple worksheets to collate this information as they are text fields, AND have to remain as such.
I currently have been playing with a pivot table set up, but am unsure how to reference multiple worksheets and limiting to the 'top 20'.
I think I could possibly create lists (200 rows to ensure total capture) of this column for each month and use those lists in a pivot table or macro, but doing so is beyond my knowledge.
What would be the best way to approach this?

NB: Because this spreadsheet is stored on a shared network drive I want to limit the total size of the file and calculations it has to do to ensure the speedy opening/saving and data entry.
It will be used by people who are not familiar with Excel let alone could be called computer literate. So I am trying to make most things autopopulate/autocalculate.


Any help you can offer would be highly appreciated, but if it is in macro form please dumb it down as I am not versed in it, as stated above.

Regards,

Levi

[zookcruzr]

I think I may have solved part of Q2.
I have created a PivotTable using 'Multiple Consolidation Ranges'.
I've been able to sort the Patients by their 'count' ie how many times they appear across all worksheets.

What I am now trying to do is limit this list to the 'Top 20', however this function is not returning what I want lol.

[Rizky]

Maybe you should attach your sample workbook to attrack the solver here, and with explain the expected results too, its difficult without seeing your file, to attach click "Go Advanced" button and find Paperclip button to attach your file

Regards

[Bob Phillips]

You could try creating a name range for offences to restrict the formula from using the whole column. The formula for the named range could be say named as offences with a refersto value of

='JAN 14'!$D2:INDEX('JAN 14'!$D:$D,COUNTA('JAN 14'!$D:$D),1)

It would have to be a local worksheet name not a workbook name as you would have one on every sheet. Your calculation formula would still be an array formula, but it would be

=INDEX('JAN 14'!offences,MATCH(MAX(COUNTIF('JAN 14'!offences,'JAN 14'!offences)),COUNTIF('JAN 14'!offences,'JAN 14'!offences),0))

If that doesn't speed it up, you could always set calculation mode to manual when entering data and reset back to automatic afterwards.

[p45cal]

I think I may have solved part of Q2.
I have created a PivotTable using 'Multiple Consolidation Ranges'.
I've been able to sort the Patients by their 'count' ie how many times they appear across all worksheets.

What I am now trying to do is limit this list to the 'Top 20', however this function is not returning what I want lol.

Yes, I have found that using multiple consolidation ranges in a pivot makes it very hard to get what you want.

What I'd do is consolidate all the source data into one table; you have the date in the leftmost column, so a pivot table can be restricted to report on any period you want.
Personally, I'd keep the source data as a single table, and ditch the monthly sheets, however, if you want you could keep the monthly sheets and have a macro consolidate the data by copying all the sheets into one table, but I'd need a sample workbook to work with, with say the sensitive data adjusted (not removed!). You could do this with find and replace.

[zookcruzr]

Thank you for all your responses. Its appreciated.

I know the easiest option would be to post a sample, however to the get the results I want I would need to change the names and keep atleast 20 entries in each sheet. All while maintaining low percentage of duplicity. Too much work . I use Excel to limit the manual data manipulation and entry I have to do lol.

I feel the best option may be to have a macro collating all the data I want in one sheet so it can be ordered alphabetically, then use the pivot table to order by count and limit to top 20. I just can't get my head around macros yet.

Bob, that does make sense thankyou. Would that ignore blanks?

Essentially I'm trying to make it simple and usable for plebs like myself, but pretty and self calculating for the management

Whilst writing this I have decided to make a sample lol. I have removed the last names, suburb and internal reference numbers as they are not important for what I need to do. I have also limited the data to around 20 each month, BUT, any solutions need to accommodate for more than 20. I have limited to a max of 200. It is very unlikely ever to get over 100, but you never know.

If I need to explain what I need further, please ask, but it should be self explanatory coupled with the sheet and op.
I have left all the months so any formula or macro will still work. I have only back-captured data to April, so that is why there is none further than that.

[p45cal]

I know the easiest option would be to post a sample, however to the get the results I want I would need to change the names and keep atleast 20 entries in each sheet. All while maintaining low percentage of duplicity. Too much work .

…and even more work for those considering trying to answer your query.

Whilst writing this I have decided to make a sample lol.

That's better!

A macro to be put in a standard code-module f the file you attached:

Sub blah()
SheetsToExclude = "SAMPLE PIVOT,Reports,Single Table,New Pivot"
Headers = Array("Date", "Event No", "Patient Name", "Offence or MH condition", "Drug Related?", "Alcohol Related? ", "Suburb", "Time at Scene (Record Minutes)", "Travel Time", "Hospital Waiting Time", "Return travel time", "Total Time", "Sec 22 Police vehicle Escort", "Sec 22 Ambulance  with police Escort", "Sec 20 Ambuance  with police Escort", "Section 20 Ambulance Escort", "Other")
Set SingleTableSheet = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
SingleTableSheet.Cells(1, "A").Resize(, UBound(Headers) + 1) = Headers
SingleTableSheet.Name = "Single Table"
DestRow = 2
For Each sht In ThisWorkbook.Worksheets
  If InStr(SheetsToExclude, sht.Name) = 0 Then
    With sht
      '.Select
      lr = .Cells(.Rows.Count, "A").End(xlUp).Row
      If lr > 1 Then
        'process
        .Cells(2, "A").Resize(lr - 1, 17).Copy SingleTableSheet.Cells(DestRow, 1)
        DestRow = DestRow + lr - 1
      End If
    End With
  End If
Next sht
End Sub

and an attached workbook with just a consolidated table made by the macro, then I added 2 pivot tables manually.

[zookcruzr]

Thanks p45cal. I appreciate the help. I have a bad tendency to write as I think, so I apologise if I offended anyone or came across as ungrateful.

The macro works perfectly, I was hoping this would help me to have a layered (2 tiered) ordering within the Pivot Table of 'Top20 Patients', however it didn't help.

In relation to Q1 (array formula), I couldn't work out a formula to replace the array one, even with the consolidated data.
I have resorted to turning to Manual Calculation, calculating then saving the document. This is fine for me, however for someone who struggles with the simplest tasks within Excel it may be too comlicated, and they wouldn't be able to wait for the array formula to catch up (1 - 2 sec) once data is entered in the cell referenced.

I am starting to think a macro would be the best thing for both problems. I could set it up like a form (as in access), so that a manager could just press a button, have the macro run and formulate the data in a printable report. Essentially what I have done, but have a macro to automatically do it with a press of a button.

So.... Q3: Is it possible for a macro to;
a) calculate multiple formulae in different specified cells (one of which would be the array formula in question)?
b) create a specifically formatted - consolidated ranged pivot table and sort it based on a sum value range?

Thank you in advance to anyone who takes the time to answer. If clarification is needed please ask.

[p45cal]

Would you be happy to ditch the monthly sheets in favour of a single table?
It would:
1. Make the formulae much easier, especially if it's a real excel Table.
2. Not be prone to users putting new information on the wrong sheet.
3. Allow any further reporting tobe much easier.
Downside:
Users getting the day/month element of the date wrong (although this can be mitigated (and, in the current setup, it looks like they have to put the date in correctly anyway)).

[zookcruzr]

Believe it or not, by having it in one table would complicate things.
It is the way it is due to the procedural training that has already been drummed into the officers.
I understand what you are saying, however this is the way it has to stay.
Plus that decision is not up to me, and has already been made lol