Create Table with quartils

[vangog]

Hello,
I use very old Excel for XP. I am not experienced with VBA in Excel.
I attach a table.hospitals_.xls
I would like to create table of detailed summery.

I am interested about the age of patients depending on the health state (column I is in Czech)... This is the main goal. I want to create tables with min,Q1,Q2,Q3(i.e Quartils),average,max values. But to have it more detailed, I want there tables:
table 1 for both sex
table 2 for male sex
table 3 for female sex

Yet I need to see the states under the tables... Please see the next table to understand the information I would like to see for the specified sex.

I have checked for some existing solutions like here:
vbaexpress.com "Calculating Min,Max,Quartile in dynamic ranges" code in post #8 ...

I thing the code is pretty cool and I could use something similar, but I do not need to work with multiple sheets. I would like to select all input table data and the table sould be added in the active sheet under the selected table.

So I think the main thing to do here is to proccess the data in a Range so that they are limited somehow... Actually two conditions:
1) sex
2) state
And then from the limited Range I need to generate the quartiles... etc.

So can you help with this? Remember, I have old Excel, so I miss many modern functions.

[p45cal]

Well, I dug out my old laptop with Excel 2003 on it, see attached.
I had difficulty with vba and the State types - the strings came out differently so I've used strings without non-UK characters in - you'll just have to change them back, both on the sheet and in the code.
There's a macro called blah, which you can run by clicking any of the buttons.
Best if you start with a cell(s) selected in the table you want to summarise before you run it. It will select the current region and ask you to confirm (or adjust) the selection.
Then it will add a summary table 3 rows below whatever you selected, overwriting anything that might have been there. Test it on any of the 3 sheets.
The macro adds a lot of formulae and leaves them there so that you can confirm they are correct. If you're happy with the formulae they can be replaced by their values; towards the end of the macro there a line:

'.Value = .Value 'converts formlae to plain values.

which is currently inactive. To remove the formulae you just need to enable this line by removing the leftmost apostrophe.

On the sheet List1, there's a pivot table at cell N12 which summarises the table, but only min,max,count and average so that you can check (some of) the results. You can delete this pivot table.

[vangog]

Thank you very much. Your code is very professional. I also tried something, but a lot of code just to get the table range. I wonder that the variables like SexColm don't need declaration! I look I will need a lot of time to learn to understand this code. Can you please explain some lines? For example this:

SexColm = Application.Match("Sex", SceHeaders, 0)
SexR1C1 = DataBody.Columns(SexColm).Address(ReferenceStyle:=xlR1C1)

[p45cal]

First, there's a mistake; the line

Set myRng = Selection

should not be there at all - I forgot to delete it.

You dont have to declare variables. I declared one (myRng) because I wanted it to be a range (not a variant) because before anything is assigned to it it is Nothing rather than Empty and I wanted to test for that in the line

If Not myRng Is Nothing Then

re:

SexColm = Application.Match("Sex", SceHeaders, 0)

In the attached, on sheet List3 cell F3, I've put the equivalent function:
=MATCH("Sex",G7:O7,0)
It tells me which column number the Sex column is in the source range, and I use it later in:

SexR1C1 = DataBody.Columns(SexColm).Address(ReferenceStyle:=xlR1C1)

SexR1C1 becomes the address of the source range Sex column, in R1C1 style (rather then A1 style eg. R8C8:R23C8 rather than $H$8:$H$23). I could have used the A1 style references but when you record a macro of you entering a formula you get the likes of this (this was on the List3 sheet):

Range("J28:J31").Select
    Selection.FormulaR1C1 = "=RC[-1]/SUM(R28C9:R31C9)"

which can be condensed to:

Range("J28:J31").FormulaR1C1 = "=RC[-1]/SUM(R28C9:R31C9)"

so I just stayed with that.
You'll notice the R28C9:R31C9, it's this sort of thing I put into a variable and use in composing a formula.

The variables SexColm, StateColm & AgeColm didn't really need to be variables (I only use each one once elsewhere in the code). In general I put things into variables (a) if I'm going to use it more than once and (b) if it helps me to understand/write my own code! On the other hand, setting those variables separately does allow some error checking in the case that the header is not found (but I haven't done any error checking).

In Module2 of the attached I've added a variation of the macro (blah2) which is a little shorter; it extends the quartile loop from 1 to 3 to 0 to 4 because quartile 0 is the minimum and quartile 4 is the maximum, so we no longer need specific max/min formulae. See the two summary tables in the List3 sheet. Also it does a single fill down of 6 columns instead of doing it 1 column at a time.

To help understand code execution, you can step through the code with F8 on the keyboard while viewing the values of variables in the Locals pane of the VBE:

2021-12-23_171938.jpg

You can also sprinkle some Select statements and MsgBox statements so you can watch on the sheet where things are. I've done that in both macros in the attached and postfixed those lines with 'debug line so that you delete/deactivate them later (because they just get in the way).

[vangog]

Thank you for your explanation. This is very valuable for me. I learnt a lot.
What is meanning of the Sce shortcut in "SceHeaders" variable name?

I read this:
CurrentRegion
"Returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns. Read-only."

With real hospital data which I want to process, every WorkSheet contains two lines of information (A1 and A2), 3rd row is empty. The table starts on row 4 .. Is it possible to skip first three rows?

So far I finished before the Loops, and I will continue to learn the rest later.

[p45cal]

Thank you for your explanation. This is very valuable for me. I learnt a lot.
What is meanning of the Sce shortcut in "SceHeaders" variable name?

Sce is my short for Source.

I read this:
CurrentRegion
"Returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns. Read-only."

With real hospital data which I want to process, every WorkSheet contains two lines of information (A1 and A2), 3rd row is empty. The table starts on row 4 .. Is it possible to skip first three rows?

Well if the 3rd row is truly empty above the table then as long as you start with any selected cell below row 3 before you run the macro, it should only select below row 3. If the area is not bound by a blank row and a blank column then you'll just have to select the table manually when it asks you to confirm at the message box (input box) at the start of the macro. The current region business was only to try and save you time - you can override it.

The vba .currentregion is the same as what you get when you select a cell on the sheet, then press F5 on the keyboard, then click Special… then choose Current region and click OK.

[vangog]

I am lost here:

=RC[-1]/SUM(R28C9:R31C9)

What is the =RC[-1]?


Or =RC? Its clear that it means Row Column...
So for Example =RC2
Which used here:

.Offset(, 2).FormulaR1C1 = "=SUMPRODUCT(--(" & StateR1C1 & "=RC" & StateCllCol & "))"

Which is a mystery for me too, because there is no multiplication. Does the sign = have some special meaning here? Is this adding?

[p45cal]

RC[-1]
Same row, column to the left of the cell the formula is in.

RC
Same column and same row ie. the same cell! Would never use and would create a circular reference, the cell referring to itself.

RC2
Same row, 2nd column of the sheet (column B)

In the formula, without the double-negative:
2021-12-24_002030.jpg

adding the double-negative:
2021-12-24_002048.jpg

Althought there is no multiplying (the PRODUCT part of SUMPRODUCT) the SUM part is is still taking place.

See https://powerspreadsheets.com/r1c1-formular1c1-vba/

[vangog]

Thank you for your effort. Now I am trying to write my own code. Basically to use the code of you on the real data... real table. So I start like this:

Selection.CurrentRegion.Select
Set originalTable = Range(Selection.Address)
If originalTable.Rows...

Now I would like to do a test of the first row of originalTable.Rows, if it is less than 4 ... so I if result is true I will select cell A4 and then I will repeat Selection.CurrentRegion.Select ... To get the original table (source table). This will way I will skip the description of the table which is above it.

[vangog]

I got it:

Selection.CurrentRegion.Select
Set originalTable = Range(Selection.Address)
If ActiveCell.Row < 4 Then
 ActiveSheet.Cells(4, 1).Select
 Selection.CurrentRegion.Select
End If

[p45cal]

If you're going to use that you can shorten it a bit:

Set originalTable = Selection.CurrentRegion
If originalTable.Row < 4 Then Cells(4, 1).CurrentRegion.Select

but you'll probably get away with just

Set originalTable = Cells(4, 1).CurrentRegion

then if you want a visual chaeck of what originalTable is just type
originalTable.select in the Immediate pane of the VBE (Ctrl+G of you can't see it).

[vangog]

Sounds strange but now when I run your code the debugger cries that there is not declaration for the variable names ... So I need to declare them all.

One thing I am thinking of. ... When I prepare this summery table (slowly learning because I am lazy on the X-mas time), I think: There will be data from March to December 2020 and the result will be summery for one period. But would it be hard to improve the code so, that we could have even more tables (or maybe just as a possibility to move that tables of Females, Males and Both to separate WorkSheets) which would reflect the situation for every week. So for example in Sheet named: "Males" there would be the tables of 52 or 53 weeks.
But then it's neccesery to ask, if the design of the table(s) should change. Because haveing 52 tables seems too hard to plot the data onto diagram.

[snb]

Did you consider Pivottables ?

[vangog]

No... I do not know to use them. Especialy how to add the formulas there.

Question:
How to make error check? Here I do twice search:

  If IsError(Application.Match("Sex", SourceHeaders, 0)) Then
        MsgBox "Error"
        Exit Sub
  End If
  SexColNo = Application.Match("Sex", SourceHeaders, 0)

[snb]

Pivottables Formulas ???

[vangog]

Oh, maybe not formulas. The first file uploaded by p45cal, WorkSheet 1: List 1, P14:S21. I don't know how he obtained this numbers, but they had to be calculated somehow.

[p45cal]

Sounds strange but now when I run your code the debugger cries that there is not declaration for the variable names ... So I need to declare them all.

Remove Option Explicit from the top of the code-module.

But would it be hard to improve the code so, that we could have even more tables (or maybe just as a possibility to move that tables of Females, Males and Both to separate WorkSheets) which would reflect the situation for every week. So for example in Sheet named: "Males" there would be the tables of 52 or 53 weeks.
But then it's neccesery to ask, if the design of the table(s) should change. Because haveing 52 tables seems too hard to plot the data onto diagram.

You're asking for quite a few things and none of them is trivial.

Although you can do formulae in pivot tables (it comes under the guise of Calculated Items and Calculated fields), and neither allows the easy production of quartiles/percentiles.
It could easily be done in Power Pivot, and probably Power Query but your version of Excel has neither of these.

How to make error check? Here I do twice search:

  If IsError(Application.Match("Sex", SourceHeaders, 0)) Then
        MsgBox "Error"
        Exit Sub
  End If
  SexColNo = Application.Match("Sex", SourceHeaders, 0)

I mentioned this in msg#4:

On the other hand, setting those variables separately does allow some error checking in the case that the header is not found (but I haven't done any error checking).

SexColm = Application.Match("Sex", SceHeaders, 0)
If IsError(SexColm) Then…

At least upload a file with a more than just 1 week's data - I may get time to do something - maybe not.

[vangog]

I will upload it later (tomorow probably), but not here, the file is too big. I could paste my code here and send you the link to download the data.

At least one problem I have solved. This is code to convert date to week no.

TheDay = CDbl(Selection.Range("A5"))
weekNo = (TheDay - 43831) / 7
' if weekNo > 1.1.2021 and weekNo<1.1.2022
If (TheDay > 44196) And (TheDay < 44562) Then
  weekNo = weekNo - (44562 - 44196) ' weekNo -= 365
End If

[vangog]

I am getting in trouble with for loop combined with match.
Can you help please? Macro is too slow.
You can download the file from here:
https://www.uschovna.cz/download/TO7...KJ/RDZ4DS4RF5/
when you will download the file let me know to delete that immediately.

My problem is in module Step2_rename_hospitals... In the first For loop. I think the problem could be on the line:

m = Application.Match(OriginalTable.Cells(i, NemocniceCol).Value, Nemocnice.Columns(2), 0)

Honestly, I am not sure what exactly is this line doing. Is the search limited per line number i? Or does this continue to seek on the next rows? If the match is performed only on the line i, then how to boost this step?

You will run this macro if you click on first button and then on newly created button.

This macro replaces strings of hospitals ("Nemocnice") and districts ("Kraje") by reference to Nemocnice sheet.