Suggestions, please

BenElliott

Board Regular
Joined
Jul 19, 2012
Messages
144
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 WilliamsLong EatonBrian WrightLong Eaton David HillBrian WrightPhilip Newbold
Trevor WalkerBolsover Colin BarnesBen ElliottBen Elliott
Craig O'ConnorBolsover Ben ElliottStephen HawkinsColin Barnes
Colin BarnesBolsover David HillPhilip NewboldBrian Wright
Mark BerryBolsover Philip NewboldCraig O'ConnorStephen Hawkins
Philip NewboldBolsover Ben ElliottBrian WrightDarren Saywell
Andrew SheppardHucknallBrian WrightHucknall Darren SaywellBen ElliottTrevor 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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi Ben,

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


Book1
ABCDEFGHIJ
1DateSpeaker Full Name InCongregagation InTalk Number InSpeaker 1 Out Full NameCongregagation 1 OutTalk 1 OutReaderHospitalityChairman
225-11-18Donald WilliamsLong Eaton85Brian WrightLong Eaton51David HillBrian WrightPhilip Newbold
302-12-18Trevor WalkerBolsover156Colin BarnesBen ElliottBen Elliott
409-12-18Craig O'ConnorBolsover9Ben ElliottStephen HawkinsColin Barnes
516-12-18Colin BarnesBolsover126David HillPhilip NewboldBrian Wright
623-12-18Mark BerryBolsover134Philip NewboldCraig O'ConnorStephen Hawkins
730-12-18Philip NewboldBolsover94Ben ElliottBrian WrightDarren Saywell
806-01-19Andrew SheppardHucknall84Brian WrightHucknall63Darren SaywellBen ElliottTrevor Walker
9
10Philip NewboldTalk NumberCongregation In
1125-11-18Chairman  
1202-12-18
1309-12-18
1416-12-18Hospitality
1523-12-18Reader
1630-12-18Speaker Full Name In94Bolsover
1706-01-19
Sheet1
Cell Formulas
RangeFormula
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)),"")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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".
 
Upvote 0

Similar threads

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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