Formula to return entire row with multiple conditions.

[MissWorkaholic]

Hi

I have a spreadsheet that has 10 columns (month, year, ward etc) and over 800 rows. On a separate sheet I would like it to display the entire row that have "ward 1", "January" and "2013". I would like this information to omit the rows that do not meet the condition without leaving blank rows.

Can anyone please help?

Many thanks

MissWorkaholic

 

[Robert Mika]

Hi

I have a spreadsheet that has 10 columns (month, year, ward etc) and over 800 rows. On a separate sheet I would like it to display the entire row that have "ward 1", "January" and "2013". I would like this information to omit the rows that do not meet the condition without leaving blank rows.

Can anyone please help?

Many thanks

MissWorkaholic

Hello and welcome to MrExcel.
How does your data look like?
Can you post an example?
Please see below how to do it.

 

[MissWorkaholic]

Hello and welcome to MrExcel.
How does your data look like?
Can you post an example?
Please see below how to do it.

Hi

Thanks for your reply. I have created a table below, does this help?
What I would like to do is on "sheet 2" bring the rows that are on the "outpatients" ward in "December" "2012"

Many thanks

MissWorkaholic

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][TABLE="width: 46"]
<colgroup><col width="46"></colgroup><tbody>[TR]
[TD="class: xl22, width: 46"]Incident Code Description
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Month
[/TD]
[TD]Year
[/TD]
[TD]Ward
[/TD]
[TD]Harm Caused
[/TD]
[TD]Incident description
[/TD]
[TD]Ref
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Information incorrect
[/TD]
[TD]12
[/TD]
[TD]2012
[/TD]
[TD]Outpatients
[/TD]
[TD]No harm
[/TD]
[TD]Desription data
[/TD]
[TD]97865
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Unexpected complication
[/TD]
[TD]12
[/TD]
[TD]2012
[/TD]
[TD]Renal
[/TD]
[TD]Low harm
[/TD]
[TD]Desription data
[/TD]
[TD]95874
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Other Patient accident
[/TD]
[TD]1
[/TD]
[TD]2013
[/TD]
[TD]Theatres
[/TD]
[TD]No harm
[/TD]
[TD]Desription data
[/TD]
[TD]92382
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Lack of equipment
[/TD]
[TD]11
[/TD]
[TD]2012
[/TD]
[TD]Theatres
[/TD]
[TD]High
[/TD]
[TD]Desription data
[/TD]
[TD]92385
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Food/mealtime problems
[/TD]
[TD]12
[/TD]
[TD]2012
[/TD]
[TD]Outpatients
[/TD]
[TD]Low harm
[/TD]
[TD]Desription data
[/TD]
[TD]95885
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Transfer - delay
[/TD]
[TD]12
[/TD]
[TD]2012
[/TD]
[TD]Pathology
[/TD]
[TD]Low harm
[/TD]
[TD]Desription data
[/TD]
[TD]92635
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Communication Issues
[/TD]
[TD]12
[/TD]
[TD]2012
[/TD]
[TD]Pathology
[/TD]
[TD]No harm
[/TD]
[TD]Desription data
[/TD]
[TD]90876
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Inadequate documentation
[/TD]
[TD]11
[/TD]
[TD]2012
[/TD]
[TD]Renal
[/TD]
[TD]High
[/TD]
[TD]Desription data
[/TD]
[TD]91250
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Robert Mika]

Sheet2
I1=Outpatients
J1-12
k1=2012
A2 drag down and accross:
=IFERROR(INDEX(Sheet1!$A$2:$G$9,SMALL(IF(Sheet1!$B$2:$B$9=$J$1,IF(Sheet1!$C$2:$C$9=$K$1,IF(Sheet1!$D$2:$D$9=$I$1,ROW(D$2:$D$9)-ROW($A$2)+1))),ROWS(Sheet1!$A$2:A3)),MATCH(A$1,Sheet1!$A$1:$G$1,0)),"")
Confirm Control+Shift+Enter

 

[MissWorkaholic]

Sheet2
I1=Outpatients
J1-12
k1=2012
A2 drag down and accross:
=IFERROR(INDEX(Sheet1!$A$2:$G$9,SMALL(IF(Sheet1!$B$2:$B$9=$J$1,IF(Sheet1!$C$2:$C$9=$K$1,IF(Sheet1!$D$2:$D$9=$I$1,ROW(D$2:$D$9)-ROW($A$2)+1))),ROWS(Sheet1!$A$2:A3)),MATCH(A$1,Sheet1!$A$1:$G$1,0)),"")
Confirm Control+Shift+Enter

Just tried this but it returned #NAME? for all selected cells.

 

[Robert Mika]

Just tried this but it returned #NAME? for all selected cells.

So it looks like you are using Excel 03:
Then
L1:
=SUMPRODUCT(--(Sheet1!$B$2:$B$9=$J$1),--(Sheet1!$C$2:$C$9=$K$1),--(Sheet1!$D$2:$D$9=$I$1))
A2 down and accross, Confirm Control+Shift+Enter:
=IF(ROWS($A$2:A2)>$L$1,"",INDEX(Sheet1!$A$2:$G$9,SMALL(IF(Sheet1!$B$2:$B$9=$J$1,IF(Sheet1!$C$2:$C$9=$K$1,IF(Sheet1!$D$2:$D$9=$I$1,ROW(D$2:$D$9)-ROW($A$2)+1))),ROWS(A$2:A2)),MATCH(A$1,Sheet1!$A$1:$G$1,0)))

 

[MissWorkaholic]

So it looks like you are using Excel 03:
Then
L1:
=SUMPRODUCT(--(Sheet1!$B$2:$B$9=$J$1),--(Sheet1!$C$2:$C$9=$K$1),--(Sheet1!$D$2:$D$9=$I$1))
A2 down and accross, Confirm Control+Shift+Enter:
=IF(ROWS($A$2:A2)>$L$1,"",INDEX(Sheet1!$A$2:$G$9,SMALL(IF(Sheet1!$B$2:$B$9=$J$1,IF(Sheet1!$C$2:$C$9=$K$1,IF(Sheet1!$D$2:$D$9=$I$1,ROW(D$2:$D$9)-ROW($A$2)+1))),ROWS(A$2:A2)),MATCH(A$1,Sheet1!$A$1:$G$1,0)))

Thanks... The SumProduct works however the 2nd formula now displays #N/A for all selected cells.

 

[Robert Mika]

Thanks... The SumProduct works however the 2nd formula now displays #N/A for all selected cells.

Confirm Control+Shift+enter

 

[MissWorkaholic]

Confirm Control+Shift+enter

Yes, I have tried that. I get #N/A for the first two rows; I assume it is trying to display the 2 results calculated by the sumproduct formula.

 

[Robert Mika]

Yes, I have tried that. I get #N/A for the first two rows; I assume it is trying to display the 2 results calculated by the sumproduct formula.

Example:
https://dl.dropbox.com/u/78583609/Multi.xls