Merging a formula that ranks results with anothe formula that ignores non blank cells

TerryE123

New Member
Joined
Oct 29, 2016
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Good afternoon,

I have learnt what I know to date by using this site and is an excellent source of advice and guidance. I am however, stuck, on how to resolve the issue I am facing with two differing formulas.

I manage a small Nursery that has two sites depending on children's age. I have attached a very small example for reference.
Basically, I will have al children data on one sheet which contains name, DOB , their attendance pattern, whether they attend Out of School or Nursery and their start date and end date. We cannot "delete" children's details when they leave for various reasons so, on that basis, the main sheet will have a mix of current children and those who have left.

Each week, I want to have 2 sheets to create the register for both sites where I add a date (Monday) that populates the current children in both sites. This is used to mark attendance and calculate staffing required.

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD] H [/TD]
[TD] I[/TD]
[TD] J [/TD]
[TD]K[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: transparent"]Start Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NAme[/TD]
[TD]Dept[/TD]
[TD]DOB[/TD]
[TD]Ranked[/TD]
[TD]Leave Date[/TD]
[TD][/TD]
[TD]OOS[/TD]
[TD][/TD]
[TD]Current Children[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: #F2F2F2, align: right"]01/10/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]John
[/TD]
[TD]OOS[/TD]
[TD][TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]01/10/2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD]John[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: #F2F2F2, align: right"]02/10/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Zeb[/TD]
[TD]Nursery[/TD]
[TD][TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]25/07/2012[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD][/TD]
[TD]Zeb[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: #F2F2F2, align: right"]03/10/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]A N[/TD]
[TD]OOS[/TD]
[TD][TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]05/10/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[TD]18/10/16[/TD]
[TD][/TD]
[TD]A N[/TD]
[TD][/TD]
[TD]Jane[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: #F2F2F2, align: right"]04/10/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Jane[/TD]
[TD]OOS[/TD]
[TD][TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]05/08/2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


My table above is to show where I would like some help (the original sheet will be across more than one tab). In column I, , I can look up those children who are due to be in the OOS club. (and a similar formula for Nursery but not shown here)

IFERROR(INDEX($C$3:$C$6, MATCH(SMALL(IF($I$2=$D$3:$D$6, $F$3:$F$6, ""), ROW(A1)), IF($I$2=$D$3:$D$6, $F$3:$F$6, ""),0)),"")
where I2 is called OOS and finds those children who have OOS in column D. It also sorts the results by the helper column as the children are listed on the register in age order. This formula works.... but I am sure it could be much simpler if I could do so.

The second formula in column K allows me to return those children who are still at the Nursery using this formula
IFERROR(INDEX($C$3:$C$6,SMALL(IF(NOT(ISBLANK($G$3:$G$6)),"",ROW($G$3:$G$6)-ROW($K$3)+1),ROW(K3)-ROW($K$3)+1)),"")

What I really need help with is combining them both so that I can:
Search for current children (second formula) and also sort those current children by age and what site (OOS or Nursery) -first formula.

Sorry for the long winded explanation and I hope this makes sense but please let me know if it doesn't.
Regards
Terry
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top