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:

Date

Speaker Full Name In

Congregagation In

Talk Number In

Speaker 1 Out Full Name

Congregagation 1 Out

Talk 1 Out

Speaker 2 Out Full Name

Congregagation 2 Out

Talk 2 Out

Reader

Hospitality

Chairman

25/11/2018

Donald Williams

Long Eaton

85

Brian Wright

Long Eaton

51

David Hill

Brian Wright

Philip Newbold

02/12/2018

Trevor Walker

Bolsover

156

Colin Barnes

Ben Elliott

Ben Elliott

09/12/2018

Craig O'Connor

Bolsover

9

Ben Elliott

Stephen Hawkins

Colin Barnes

16/12/2018

Colin Barnes

Bolsover

126

David Hill

Philip Newbold

Brian Wright

23/12/2018

Mark Berry

Bolsover

134

Philip Newbold

Craig O'Connor

Stephen Hawkins

30/12/2018

Philip Newbold

Bolsover

94

Ben Elliott

Brian Wright

Darren Saywell

06/01/2019

Andrew Sheppard

Hucknall

84

Brian Wright

Hucknall

63

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>
</tbody>

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

Philip Newbold	Assignments
25 Nov, 2018	Chairman
23 Dec, 2018		Reader
30 Dec, 2018			Talk 94	at Bolsover
27 Jan, 2019	Chairman
03 Mar, 2019		Reader
07 Apr, 2019	Chairman

<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>
</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.