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

 

[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:
[TABLE="width: 596"]
<tbody>[TR]
[TD="align: right"]25 November 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Chairman[/TD]
[/TR]
[TR]
[TD="align: right"]23 December 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]Reader[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30 December 2018[/TD]
[TD]Talk 94 at Bolsover[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27 January 2019[/TD]
[TD][/TD]
[TD]Talk 160 at Hucknall[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03 February 2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Chairman[/TD]
[/TR]
[TR]
[TD="align: right"]03 March 2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]Reader[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24 March 2019[/TD]
[TD][/TD]
[TD]Talk 153 at Alfreton[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07 April 2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Chairman[/TD]
[/TR]
[TR]
[TD="align: right"]12 May 2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]Reader[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09 June 2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Chairman[/TD]
[/TR]
[TR]
[TD="align: right"]07 July 2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]Reader[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="width: 420"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD="align: right"]03/03/2019[/TD]
[TD]Philip Newbold[/TD]
[TD]Craig O'Connor[/TD]
[TD]Stephen Hawkins[/TD]
[/TR]
[TR]
[TD="align: right"]10/03/2019[/TD]
[TD]Darren Saywell[/TD]
[TD]Brian Wright[/TD]
[TD]Darren Saywell[/TD]
[/TR]
[TR]
[TD="align: right"]17/03/2019
[/TD]
[TD]Trevor Walker[/TD]
[TD]Ben Elliott
[/TD]
[TD]Trevor Walker
[/TD]
[/TR]
[TR]
[TD="align: right"]24/03/2019[/TD]
[TD]Stephen Hawkins[/TD]
[TD]Stephen Hawkins[/TD]
[TD]Craig O'Connor[/TD]
[/TR]
</tbody>[/TABLE]

and here is the output:
[TABLE="width: 641"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]20 January 2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Reader[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10 March 2019
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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".