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
 
Basically, using the formula:
Code:
=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.
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
You're welcome Ben,

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

Similar threads

Forum statistics

Threads
1,223,099
Messages
6,170,107
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