Using the result of a Filter formula to identify cells in a table?

[unclefish]

Dear Excelists,

I'd appreciate your comments regarding the following problem.

• I have two separate tables: the first table listing patients (Patient #), the region where they received a treatment (Region) and the treatment option (Treatment). The second table summarizes the patients (Patient #), the treatment they received (Treatment) and the gender of the patients (Gender).
  Please note that patients may have received multiple treatments or may have been treated in multiple regions. The tables have different lenghts, because the first table includes the information on the treated region, while the second doesn't.
• I would like to use the data of the two tables in such a way that I get the number of female and male patients for a combination of region and treatment option.

I created an example of my problem. The real tables include data of hundreds of patients, which is why i can't (don't want to) do this manually.

In this example, i'm interested in patients that were treated in Region1 and that received treatment Option1. I entered this information into the cells C13 and C14, respectively.

I know how to identify the patients treated in Region1 and with treatment option Option1 by filtering the first (green) table with the following formula:

=FILTER(B2:B10,(C2:C10=C13)*(D2:D10=C14))

The matching patients are (in this example) listed in the cells C16:C17. In practice, the number of patients and the lenght of this list may vary, depending on the querried region and treatment option.

But now i'm stuck .
How can is use the results of this formula to filter the second (blue) table and identify the number of female / male patients?

Any help would be highly appreciated!

 

[hagia_sofia]

If it is a correct assumption that every Patient # represents the same person why don't you create a helper column called "Gender" in column E, use lookup function like VLOOKUP or XLOOKUP to obtain information on gender for each patient and then use e.g. COUNTIFS or create a pivot table?

 

[RobP]

Hi

would this work for you ?

=IFERROR(XLOOKUP(FILTER(B2:B10,(C2:C10=C13)*(D2:D10=C14)),F2:F9,H2:H9,""),"")

 

[RobP]

or this could give you patient name & gender :

=IFERROR(HSTACK(FILTER(B2:B10,(C2:C10=C13)*(D2:D10=C14)),XLOOKUP(FILTER(B2:B10,(C2:C10=C13)*(D2:D10=C14)),F2:F9,H2:H9,"")),"None Found")

 

[unclefish]

Thank you very much for you replies:

@hagia_sofia: Your assumption is correct, in both table the Patient # refers to the same person. But i'm afraid I don't fully understand your approach and especially how i can extract the numbers of female and male patients.
@RobP: The formula suggested by you lists the gender of the patients identified by the filter formula. But the information i'm interested in is the number of female and male patients.

My initial idea was to filter the second table to get the number of female / male patients:

=ROWS(UNIQUE(FILTER(H2:H9,(H2:H9="Female")*(F2:F9=C16#))))
=ROWS(UNIQUE(FILTER(H2:H9,(H2:H9="Male")*(F2:F9=C16#))))

I was hoping that F2:F9=C16# could be used to filter colum F of the blue table for the patients identified by the filter of the green table.
But unfortunately this doesn't work.

 

[hagia_sofia]

I presume that you are trying to count the number of male and female patients separately. If it is the case what I suggested was 1) to insert this formula to E2:
XLOOKUP(B2:B10,G2:G9,I2:I9) and then 2) to insert Female and Male into D12 and E12, and then 3) to use a formula that counts, e.g. =IFERROR(ROWS(FILTER($E2#;($C$2:$C$10=$C$13)*($D$2:$D$10=$C$14)*($E2#=D12)));0). Or is it supposed to be something else?

 

[unclefish]

Thank you all for your replies!
I was finally able to find a solution that worked for me. Thanks a lot to hagia_sofia for the suggestion to use a helper column and the VLOOKUP function.

Here is my solution:

• I added a helper column to E2 with

  =VLOOKUP(B2:B10,F2:H9,3)

• Then i filtered the green table + helper column using

  =ROWS(UNIQUE(FILTER(B2:B10,(C2:C10=C13)*(D2:D10=C14)*(E2#="Female"))))

  , providing the number of female patients.
• And finally the same for male patients.

 

[RobP]

=LET(patients,HSTACK(FILTER(B2:B11,(C2:C11=C14)*(D2:D11=C15)),XLOOKUP(FILTER(B2:B11,(C2:C11=C14)*(D2:D11=C15)),F2:F10,H2:H10,"")),data,CHOOSECOLS(patients,2),male,"Male = "&COUNTA(FILTER(data,data="Male")),female,"Female = "&COUNTA(FILTER(data,data="Female")),VSTACK(male,female))

Took a while, but should get you what you want in one formula.

Rob

 

[RobP]

sorry, needed a slight tweak - now will give just values in cells, male on top, female below.

=LET(patients,HSTACK(FILTER(B2:B11,(C2:C11=C14)*(D2:D11=C15)),XLOOKUP(FILTER(B2:B11,(C2:C11=C14)*(D2:D11=C15)),F2:F10,H2:H10,"")),data,CHOOSECOLS(patients,2),male,SUMPRODUCT(--(FILTER(data,data="Male","")<>"")),female,SUMPRODUCT(--(FILTER(data,data="Female","")<>"")),VSTACK(male,female))

 

[unclefish]

@RobP:
Thank's a lot Rob! That's an impressive solution. It works like a charm!

=LET(patients,HSTACK(FILTER(B2:B10,(C2:C10=C13)*(D2:D10=C14)),XLOOKUP(FILTER(B2:B10,(C2:C10=C13)*(D2:D10=C14)),F2:F10,H2:H10,"")),data,CHOOSECOLS(patients,2),male,SUMPRODUCT(--(FILTER(data,data="Male","")<>"")),female,SUMPRODUCT(--(FILTER(data,data="Female","")<>"")),VSTACK(male,female))