Suggestions, please

[BenElliott]

I have been working on an Excel workbook for booking talks in various locations and a variety of talks. This is to replace a small database programme that only works on Windows systems (PC or laptop) and only allows me to look at 15 weeks worth of data at a time and doesn't allow me to easily access some of the data. The creator of this programme cannot help me to customise reports.

The Excel database I have created is as follows:

Donald Williams	Long Eaton	Brian Wright	Long Eaton				David Hill	Brian Wright	Philip Newbold
Trevor Walker	Bolsover						Colin Barnes	Ben Elliott	Ben Elliott
Craig O'Connor	Bolsover						Ben Elliott	Stephen Hawkins	Colin Barnes
Colin Barnes	Bolsover						David Hill	Philip Newbold	Brian Wright
Mark Berry	Bolsover						Philip Newbold	Craig O'Connor	Stephen Hawkins
Philip Newbold	Bolsover						Ben Elliott	Brian Wright	Darren Saywell
Andrew Sheppard	Hucknall	Brian Wright	Hucknall				Darren Saywell	Ben Elliott	Trevor Walker

<colgroup><col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:6473;width:133pt" width="177"> <col style="mso-width-source:userset;mso-width-alt:6619;width:136pt" width="181"> <col style="mso-width-source:userset;mso-width-alt:4315;width:89pt" width="118"> <col style="mso-width-source:userset;mso-width-alt:5412;width:111pt" width="148"> <col style="mso-width-source:userset;mso-width-alt:6144;width:126pt" width="168"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:6509;width:134pt" width="178"> <col style="mso-width-source:userset;mso-width-alt:5778;width:119pt" width="158"> <col style="mso-width-source:userset;mso-width-alt:2340;width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4205; width:86pt" width="115" span="3"> </colgroup><tbody>
[TD="class: xl68, width: 75"]Date[/TD]
[TD="class: xl68, width: 177"]Speaker Full Name In[/TD]
[TD="class: xl68, width: 181"]Congregagation In[/TD]
[TD="class: xl68, width: 118"]Talk Number In[/TD]
[TD="class: xl68, width: 148"]Speaker 1 Out Full Name[/TD]
[TD="class: xl68, width: 168"]Congregagation 1 Out[/TD]
[TD="class: xl68, width: 81"]Talk 1 Out[/TD]
[TD="class: xl68, width: 178"]Speaker 2 Out Full Name[/TD]
[TD="class: xl68, width: 158"]Congregagation 2 Out[/TD]
[TD="class: xl68, width: 64"]Talk 2 Out[/TD]
[TD="class: xl68, width: 115"]Reader[/TD]
[TD="class: xl68, width: 115"]Hospitality[/TD]
[TD="class: xl68, width: 115"]Chairman[/TD]

[TD="class: xl66, align: right"]25/11/2018[/TD]

[TD="class: xl67"]85[/TD]

[TD="class: xl67"]51[/TD]

[TD="class: xl66, align: right"]02/12/2018[/TD]

[TD="class: xl67"]156[/TD]

[TD="class: xl67"][/TD]

[TD="class: xl66, align: right"]09/12/2018[/TD]

[TD="class: xl67"]9[/TD]

[TD="class: xl67"][/TD]

[TD="class: xl66, align: right"]16/12/2018[/TD]

[TD="class: xl67"]126[/TD]

[TD="class: xl67"][/TD]

[TD="class: xl66, align: right"]23/12/2018[/TD]

[TD="class: xl67"]134[/TD]

[TD="class: xl67"][/TD]

[TD="class: xl66, align: right"]30/12/2018[/TD]

[TD="class: xl67"]94[/TD]

[TD="class: xl67"][/TD]

[TD="class: xl66, align: right"]06/01/2019[/TD]

[TD="class: xl67"]84[/TD]

[TD="class: xl67"]63[/TD]

</tbody>

I would like to interogate this database to provide a report as this:

<colgroup><col style="mso-width-source:userset;mso-width-alt:4693;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:3882;width:68pt" width="91"> <col style="mso-width-source:userset;mso-width-alt:2944;width:52pt" width="69"> <col style="mso-width-source:userset;mso-width-alt:3200;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:2901;width:51pt" width="68"> </colgroup><tbody>
[TD="class: xl68, width: 201, colspan: 2"]Philip Newbold[/TD]
[TD="class: xl67, width: 212, colspan: 3"]Assignments[/TD]

[TD="class: xl63, width: 110"]25 Nov, 2018[/TD]
[TD="class: xl64, width: 91"]Chairman[/TD]
[TD="class: xl64, width: 69"][/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]

[TD="class: xl63, width: 110"]23 Dec, 2018[/TD]
[TD="class: xl65, width: 91"][/TD]
[TD="class: xl64, width: 69"]Reader[/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]

[TD="class: xl63, width: 110"]30 Dec, 2018[/TD]
[TD="class: xl65, width: 91"][/TD]
[TD="class: xl64, width: 69"][/TD]
[TD="class: xl64, width: 75"]Talk 94[/TD]
[TD="class: xl66"] at Bolsover[/TD]

[TD="class: xl63, width: 110"]27 Jan, 2019[/TD]
[TD="class: xl64, width: 91"]Chairman[/TD]
[TD="class: xl64, width: 69"][/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]

[TD="class: xl63, width: 110"]03 Mar, 2019[/TD]
[TD="class: xl65, width: 91"][/TD]
[TD="class: xl64, width: 69"]Reader[/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]

[TD="class: xl63, width: 110"]07 Apr, 2019[/TD]
[TD="class: xl64, width: 91"]Chairman[/TD]
[TD="class: xl64, width: 69"][/TD]
[TD="class: xl64, width: 75"][/TD]
[TD="class: xl64, width: 68"][/TD]

</tbody>

The trouble is, I can't see how. I can use advance filter but if I filter for name in Chairman it will not allow me to see the names in other columns. I can use array formulae to extract chairman, reader or speaker and give the date of each but I can't see how to give the above result.

I would be very grateful if anyone can see a way around the problem. I am using Office365 but haven't yet received the update to allow Sequence, Filter, etc.

Thanks for your help in anticipation.

Ben

 

[BenElliott]

Basically, using the formula:

=INDEX(LocalHistory[Date],AGGREGATE(15,6,ROW(LocalHistory[Date])-ROW($A$2)+1/(((LocalHistory[Date]>$G$1)*(LocalHistory[Speaker Full Name In]=$A$3))+((LocalHistory[Date]>$G$1)*(LocalHistory[Speaker 1 Out Full Name]=$A$3))+((LocalHistory[Date]>$G$1)*(LocalHistory[Speaker 2 Out Full Name]=$A$3))+((LocalHistory[Date]>$G$1)*(LocalHistory[Reader]=$A$3))+((LocalHistory[Date]>$G$1)*(LocalHistory[Chairman]=$A$3))),ROWS($G$5:G5)))

I am trying to see if any row in the Excel table LocalHistory has a specified name (Cell B3) after the date specified in G1 in one or more of the table columns [Speaker Full Name In], [Speaker 1 Out Full Name], [Speaker 2 Out Full Name], [Reader] or [Chairman]. If the specified name occurs in a row then extract that week's date.

As I mentioned, a name can appear in one or two columns but I am now out of my depth because I can't see why the date extract picks the next row up. For example, a name appears on the row for 14 April 2019. This formula returns the date one row above, 7 April 2019 if the specified name appears in two columns but it returns the correct week if the name is in only one column.

It's very frustrating. I try to evaluate the formula but with five criteria, I can't see what is happening.

 

[BenElliott]

OK, so I'm think out loud here. I think I have found a work-around in this amendment to the formula taking your suggestion, Ras.

{=INDEX(LocalHistory[Date],AGGREGATE(15,6,ROW(LocalHistory[Date])-ROW($A$2)+1/IF(((LocalHistory[Date]>$G$1)*(LocalHistory[Speaker Full Name In]=$A$3))+((LocalHistory[Date]>$G$1)*(LocalHistory[Speaker 1 Out Full Name]=$A$3))+((LocalHistory[Date]>$G$1)*(LocalHistory[Speaker 2 Out Full Name]=$A$3))+((LocalHistory[Date]>$G$1)*(LocalHistory[Reader]=$A$3))+((LocalHistory[Date]>$G$1)*(LocalHistory[Chairman]=$A$3))>1,1,(((LocalHistory[Date]>$G$1)*(LocalHistory[Speaker Full Name In]=$A$3))+((LocalHistory[Date]>$G$1)*(LocalHistory[Speaker 1 Out Full Name]=$A$3))+((LocalHistory[Date]>$G$1)*(LocalHistory[Speaker 2 Out Full Name]=$A$3))+((LocalHistory[Date]>$G$1)*(LocalHistory[Reader]=$A$3))+((LocalHistory[Date]>$G$1)*(LocalHistory[Chairman]=$A$3)))),ROWS($G$5:G5)))}

This is looking at the sum of the criteria, taking Logical test * Logical test = either 0 or 1. If multiple criteria can apply then the sum of the logical tests can be greater than 1. Hence I test whether the criteria is greater than 1. If it is then use 1 as the result otherwise just accept the result. This is now giving the correct output result and showing the correct date plus the correct output in columns "Reader" and "Chairman"

I am grateful for your feedback because it has made me go back to first principles, creating a test workbook and testing each individual aspect of the formula, looking at the intermittent results.

I still don't see why it was giving the wrong row number but at least the result justifies the method.

Ben

 

[RasGhul]

You're welcome Ben,

It's always tricky when using formulas to transform your data, I'm glad your getting closer to a solution.