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

 

[RasGhul]

Hi Ben,

Could you use something like this, data validated the names into A10 so you can choose and update the report.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Speaker Full Name In	Congregagation In	Talk Number In	Speaker 1 Out Full Name	Congregagation 1 Out	Talk 1 Out	Reader	Hospitality	Chairman
2	25-11-18	Donald Williams	Long Eaton	85	Brian Wright	Long Eaton	51	David Hill	Brian Wright	Philip Newbold
3	02-12-18	Trevor Walker	Bolsover	156				Colin Barnes	Ben Elliott	Ben Elliott
4	09-12-18	Craig O'Connor	Bolsover	9				Ben Elliott	Stephen Hawkins	Colin Barnes
5	16-12-18	Colin Barnes	Bolsover	126				David Hill	Philip Newbold	Brian Wright
6	23-12-18	Mark Berry	Bolsover	134				Philip Newbold	Craig O'Connor	Stephen Hawkins
7	30-12-18	Philip Newbold	Bolsover	94				Ben Elliott	Brian Wright	Darren Saywell
8	06-01-19	Andrew Sheppard	Hucknall	84	Brian Wright	Hucknall	63	Darren Saywell	Ben Elliott	Trevor Walker
9
10	Philip Newbold		Talk Number	Congregation In
11	25-11-18	Chairman
12	02-12-18
13	09-12-18
14	16-12-18	Hospitality
15	23-12-18	Reader
16	30-12-18	Speaker Full Name In	94	Bolsover
17	06-01-19
Sheet1

Cell Formulas
Range	Formula
B11	=IF(SUMPRODUCT(($B$2:$J$8=$A$10)*($A$2:$A$8=$A11)*(COLUMN($B$1:$J$1)-COLUMN($A$1)+1))=0,"",INDEX($B$1:$J$1,,SUMPRODUCT(($B$2:$J$8=$A$10)*($A$2:$A$8=$A11)*(COLUMN($B$1:$J$1)-COLUMN($A$1)))))
C11	=IF(ROWS($C$11:C11)=MATCH($A$10,$B$2:$B$8,0),INDEX($D$2:$D$8,MATCH($A$10,$B$2:$B$8,0)),"")
D11	=IF(ROWS($D$11:D11)=MATCH($A$10,$B$2:$B$8,0),INDEX($C$2:$C$8,MATCH($A$10,$B$2:$B$8,0)),"")

 

[BenElliott]

That's an idea, Ras. Thanks.

A thought I have tried is to specify a date in say E10, find that in A11 but I do like your more elegant solution for columns C and D than my multiple nested 'IFS'. My efforts so far find the date, thus: =IF(OR(INDEX(LocalHistory[Speaker 1 Out Full Name],MATCH($B$1,LocalHistory[Date],0))=A3,INDEX(LocalHistory[Speaker Full Name In],MATCH($B$1,LocalHistory[Date],0))=A3,INDEX(LocalHistory[Speaker 2 Out Full Name],MATCH($B$1,LocalHistory[Date],0))=A3,INDEX(LocalHistory[Chairman],MATCH($B$1,LocalHistory[Date],0))=A3,INDEX(LocalHistory[Reader],MATCH($B$1,LocalHistory[Date],0))=A3),B1,"")

and then to fill in columns 'B' to 'E' it's something like this: =IF(INDEX(LocalHistory[Speaker Full Name In],MATCH(A6,LocalHistory[Date],0))=A3,"Bolsover","")

But, I'll give your idea a try.

Cheers.

Ben

 

[BenElliott]

Sorry, should have mentioned, that my date to search is in B1 and my name to index is A3.

Ben

 

[BenElliott]

Cracked it.
Here's the result:

25 November 2018				Chairman
23 December 2018			Reader
30 December 2018	Talk 94 at Bolsover
27 January 2019		Talk 160 at Hucknall
03 February 2019				Chairman
03 March 2019			Reader
24 March 2019		Talk 153 at Alfreton
07 April 2019				Chairman
12 May 2019			Reader
09 June 2019				Chairman
07 July 2019			Reader

<tbody>
</tbody>

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

The index other columns from looking up the date. There might be better ways of doing it. I'll keep looking.

Ben

 

[RasGhul]

Just for curiosity sake I wanted to get closer to your original post, have a look at this; I see we both found AGGREGATE good work

https://www.dropbox.com/s/rwu6rc26xhsgp03/new database help.xlsx?dl=0

 

[BenElliott]

Thanks, Ras.

I don't think there was a right or wrong metheod, just different and I was just stumpted as a way to go. Your post and suggestion just gave me a new way of looking at the problem.

Cheers.

Ben

 

[BenElliott]

A problem has now reared its head and I am not sure why.

Please see the first post of this thread to see the database table I use. The table, named LocalHistory has its columns correctly labeled. My problem is that if, in columns Reader and Chairman contain the same name then the result produces the wrong week.

For example, here is an extract of the database:

03/03/2019	Philip Newbold	Craig O'Connor	Stephen Hawkins
10/03/2019	Darren Saywell	Brian Wright	Darren Saywell
17/03/2019	Trevor Walker	Ben Elliott	Trevor Walker
24/03/2019	Stephen Hawkins	Stephen Hawkins	Craig O'Connor

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

and here is the output:

20 January 2019			Reader
10 March 2019

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

The output shows the wrong date (Please see a previous post showing the formula I use to extract the date) - The formula looks for a name in any of the columns. I think I am trying to obtain an OR criteria but I have slipped up somewhere.

What have I done wrong? If I remove one of the duplicate names from the database then the date correct itself.

Any help would be appreciated.

Many thanks,

Ben

 

[RasGhul]

I'm not sure how your array arguments are affected by your data, but you may need a countif condition by LocalHistory[Date]>$B$1)*(LocalHistory[Speaker Full Name In].

If(countif(speaker is > 1 than run formula excluding where name occurs again), otherwise run whole formula?

or you may need to shorten you aggregate array to ignore same name in assignments.

 

[BenElliott]

This is where I am getting out of my depth. It is possible for someone to fulfill two roles on some days. Therefore, two columns of the output table will have entries but I can't see why my formula to extract the date gives the wrong date if the multiple role condition exists.

At the moment, I am having to manually adjust the schedule to account for those occasional double "shifts".