trying to display results by date

[mugwump]

I have a workbook that looks at the data page matches the date on the event page and posts the matching date information to the event page from the data page.

(see example below) data list below example. The problem I have is that if the data in the date range is more than 2 events the data does not enter on the events page. As a newbie I have gone as far as I can go. Could some one help me please?

Formula used
=VLOOKUP(B3,root_Event_Search.xlsx!rootevent,2,0)
[TABLE="width: 700"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Event page output
Week commencing [/TD]
[TD]Week ending[/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD]4/02/2019[/TD]
[TD]10/02/2019[/TD]
[TD]​​Pacific Jewel (T) Station Pier, Port Melbourne [/TD]
[TD]HAL Rd 18 - Melbourne Victory v Perth Glory AAMI Park [/TD]
[/TR]
[TR]
[TD]11/02/2019[/TD]
[TD]17/02/2019[/TD]
[TD]University exams Caulfield Racecourse [/TD]
[TD]BBL Final - TBC v TBC MCG [/TD]
[/TR]
</tbody>[/TABLE]
Data page information
[TABLE="width: 1098"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Column1[/TD]
[/TR]
[TR]
[TD="align: right"]15/11/2020[/TD]
[TD]ICC Men's World Twenty20 Final MCG [/TD]
[/TR]
[TR]
[TD="align: right"]8/03/2020[/TD]
[TD]ICC Women's World Twenty20 Final MCG [/TD]
[/TR]
[TR]
[TD="align: right"]24/11/2019[/TD]
[TD]International Convention of Jehovah's Witnesses Marvel Stadium [/TD]
[/TR]
[TR]
[TD="align: right"]23/11/2019[/TD]
[TD]International Convention of Jehovah's Witnesses Marvel Stadium [/TD]
[/TR]
[TR]
[TD="align: right"]22/11/2019[/TD]
[TD]International Convention of Jehovah's Witnesses Marvel Stadium [/TD]
[/TR]
[TR]
[TD="align: right"]22/11/2019[/TD]
[TD]Kiss - End of the Road World Tour Rod Laver Arena [/TD]
[/TR]
[TR]
[TD="align: right"]21/11/2019[/TD]
[TD]Kiss - End of the Road World Tour Rod Laver Arena [/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2019[/TD]
[TD]Shawn Mendes in concert Rod Laver Arena [/TD]
[/TR]
[TR]
[TD="align: right"]30/10/2019[/TD]
[TD]Shawn Mendes in concert Rod Laver Arena [/TD]
[/TR]
[TR]
[TD="align: right"]29/10/2019[/TD]
[TD]Shawn Mendes in concert Rod Laver Arena [/TD]
[/TR]
[TR]
[TD="align: right"]29/10/2019[/TD]
[TD]Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre [/TD]
[/TR]
[TR]
[TD="align: right"]28/10/2019[/TD]
[TD]Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre [/TD]
[/TR]
[TR]
[TD="align: right"]27/10/2019[/TD]
[TD]Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre [/TD]
[/TR]
[TR]
[TD="align: right"]26/10/2019[/TD]
[TD]Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre [/TD]
[/TR]
[TR]
[TD="align: right"]25/10/2019[/TD]
[TD]Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre [/TD]
[/TR]
</tbody>[/TABLE]

 

[mugwump]

alternative to vlookup

I have a spreadsheet of information by date that I need a formula on another sheet to look at and if the date matches post the text in the page on the corresponding date. I have tried vlookup with success with column a and b but it will not continue onto column c,d,e etc...
any suggestions please? bear in mind I am basic at this

working formula
=VLOOKUP(B3,root_Event_Search.xlsx!rootevent,2,0)
b3 being the date (changes), rootevent being the data source....

sample of data

[TABLE="width: 563"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"][TABLE="width: 563"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]4/03/2019[/TD]
[TD]Sheffield Shield Victoria v Tasmania Day 2[/TD]
[/TR]
[TR]
[TD="align: right"]4/03/2019[/TD]
[TD]​​Pacific Jewel (T)[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]2019 JLT Community Series - Week 1 - Richmond v Melbourne[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]HAL Rd 21 - Melbourne City v Perth Glory[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]Carnival Legend (T)[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]Glenferrie Festival 2019[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]2019 Australian International Airshow & Aerospace & Defence Expo[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]Sheffield Shield Victoria v Tasmania Day 1[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]Commonwealth Bank Women's ODI Series -Australia v New Zealand[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]Mordialloc Food, Wine & Music Festival[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]2019 JLT Community Series Week 1 - Nth Melbourne v St Kilda[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]Ulysses Club National Rally - Grand Parade[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]2019 NAB AFLW Round 5 - Carlton v Collingwood[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]HAL Rd 21 - Melbourne Victory v Newcastle Jets[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]2019 Australian International Airshow & Aerospace & Defence Expo[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]Australian Guineas Day presented by 3AW[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]Beer Incider 2019[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]Mordialloc Food, Wine & Music Festival[/TD]
[/TR]
[TR]
[TD="align: right"]1/03/2019[/TD]
[TD]Mordialloc Food, Wine & Music Festival[/TD]
[/TR]
[TR]
[TD="align: right"]1/03/2019[/TD]
[TD]SANZAAR Super Rugby Union Round 3 - Melbourne Rebels v Highlanders (New Zealand)[/TD]
[/TR]
[TR]
[TD="align: right"]1/03/2019[/TD]
[TD]​​Pacific Jewel (T)[/TD]
[/TR]
[TR]
[TD="align: right"]1/03/2019[/TD]
[TD]2019 Australian International Airshow & Aerospace & Defence Expo[/TD]
[/TR]
[TR]
[TD="align: right"]1/03/2019[/TD]
[TD]Night Gallops [/TD]
[/TR]
[TR]
[TD="align: right"]1/03/2019[/TD]
[TD]University exams[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]2019 JLT Community Series Week 1 - Carlton v Essendon[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]SOuth Melbourne Night Market[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]Europa[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]Europa (T)[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]​Crystal Serenity[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]​Pacific Eden[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]2019 Australian International Airshow & Aerospace & Defence Expo[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]University exams[/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2019[/TD]
[TD]Summer Night Market [/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2019[/TD]
[TD]Arctic Monkeys - Live 2019[/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2019[/TD]
[TD]Europa[/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2019[/TD]
[TD]​​Carnival Legend (T)[/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2019[/TD]
[TD]2019 Australian International Airshow & Aerospace & Defence Expo[/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2019[/TD]
[TD]University exams[/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2019[/TD]
[TD]University exams[/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2019[/TD]
[TD]Arctic Monkeys - Live 2019[/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2019[/TD]
[TD]Bryan Ferry in concert[/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2019[/TD]
[TD]​Noordam[/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2019[/TD]
[TD]2019 Australian International Airshow & Aerospace & Defence Expo[/TD]
[/TR]
[TR]
[TD="align: right"]25/02/2019[/TD]
[TD]University exams[/TD]
[/TR]
[TR]
[TD="align: right"]25/02/2019[/TD]
[TD]​Golden Princess (T)[/TD]
[/TR]
[TR]
[TD="align: right"]25/02/2019[/TD]
[TD]Caravan & Camping Super Show 2019[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 563"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]4/03/2019[/TD]
[TD]Sheffield Shield Victoria v Tasmania Day 2[/TD]
[/TR]
[TR]
[TD="align: right"]4/03/2019[/TD]
[TD]​​Pacific Jewel (T)[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]2019 JLT Community Series - Week 1 - Richmond v Melbourne[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]HAL Rd 21 - Melbourne City v Perth Glory[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]Carnival Legend (T)[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]Glenferrie Festival 2019[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]2019 Australian International Airshow & Aerospace & Defence Expo[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]Sheffield Shield Victoria v Tasmania Day 1[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]Commonwealth Bank Women's ODI Series -Australia v New Zealand[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2019[/TD]
[TD]Mordialloc Food, Wine & Music Festival[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]2019 JLT Community Series Week 1 - Nth Melbourne v St Kilda[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]Ulysses Club National Rally - Grand Parade[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]2019 NAB AFLW Round 5 - Carlton v Collingwood[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]HAL Rd 21 - Melbourne Victory v Newcastle Jets[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]2019 Australian International Airshow & Aerospace & Defence Expo[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]Australian Guineas Day presented by 3AW[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]Beer Incider 2019[/TD]
[/TR]
[TR]
[TD="align: right"]2/03/2019[/TD]
[TD]Mordialloc Food, Wine & Music Festival[/TD]
[/TR]
[TR]
[TD="align: right"]1/03/2019[/TD]
[TD]Mordialloc Food, Wine & Music Festival[/TD]
[/TR]
[TR]
[TD="align: right"]1/03/2019[/TD]
[TD]SANZAAR Super Rugby Union Round 3 - Melbourne Rebels v Highlanders (New Zealand)[/TD]
[/TR]
[TR]
[TD="align: right"]1/03/2019[/TD]
[TD]​​Pacific Jewel (T)[/TD]
[/TR]
[TR]
[TD="align: right"]1/03/2019[/TD]
[TD]2019 Australian International Airshow & Aerospace & Defence Expo[/TD]
[/TR]
[TR]
[TD="align: right"]1/03/2019[/TD]
[TD]Night Gallops [/TD]
[/TR]
[TR]
[TD="align: right"]1/03/2019[/TD]
[TD]University exams[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]2019 JLT Community Series Week 1 - Carlton v Essendon[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]SOuth Melbourne Night Market[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]Europa[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]Europa (T)[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]​Crystal Serenity[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]​Pacific Eden[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]2019 Australian International Airshow & Aerospace & Defence Expo[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]University exams[/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2019[/TD]
[TD]Summer Night Market [/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2019[/TD]
[TD]Arctic Monkeys - Live 2019[/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2019[/TD]
[TD]Europa[/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2019[/TD]
[TD]​​Carnival Legend (T)[/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2019[/TD]
[TD]2019 Australian International Airshow & Aerospace & Defence Expo[/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2019[/TD]
[TD]University exams[/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2019[/TD]
[TD]University exams[/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2019[/TD]
[TD]Arctic Monkeys - Live 2019[/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2019[/TD]
[TD]Bryan Ferry in concert[/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2019[/TD]
[TD]​Noordam[/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2019[/TD]
[TD]2019 Australian International Airshow & Aerospace & Defence Expo[/TD]
[/TR]
[TR]
[TD="align: right"]25/02/2019[/TD]
[TD]University exams[/TD]
[/TR]
[TR]
[TD="align: right"]25/02/2019[/TD]
[TD]​Golden Princess (T)[/TD]
[/TR]
[TR]
[TD="align: right"]25/02/2019[/TD]
[TD]Caravan & Camping Super Show 2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

sample of output

week start date week end date 1st event 2nd event 3rd event 4th event

[TABLE="width: 1136"]
<colgroup><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]25/02/2019[/TD]
[TD]3/03/2019[/TD]
[TD]University exams Caulfield Racecourse [/TD]
[TD]2019 JLT Community Series - Week 1 - Richmond v Melbourne [/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]4/03/2019[/TD]
[TD]10/03/2019[/TD]
[TD]Sheffield Shield Victoria v Tasmania Day 2 MCG [/TD]
[TD]2019 JLT Community Series - Week 2 - Western Bulldogs v St Kilda MARS Stadium (Ballarat) [/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]11/03/2019[/TD]
[TD]17/03/2019[/TD]
[TD]2019 JLT Community Series - Week 2 - Collingwood v Carlton [/TD]
[TD]2019 NAB AFLW - Round 7 - Collingwood v Brisbane Lions [/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]18/03/2019[/TD]
[TD]24/03/2019[/TD]
[TD]​Queen Elizabeth (T) Station Pier, Port Melbourne [/TD]
[TD]AFL Round 1 - St Kilda v Gold Coast SUNS Marvel Stadium [/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[bobsan42]

Re: alternative to vlookup

First of: be careful with cell references: if you are filling the formula to the right it is a good idea to make it like this:

=VLOOKUP($B3,root_Event_Search.xlsx!rootevent,2,0)

Second: probably the cause of your problem is the area defined for rootvent - it probably only has two columns.
One way to sort the problem is to redefine the named range.
The second way to is to use shee and column names like:
=VLOOKUP($B3,[root_Event_Search.xlsx]Sheet1!$A:$F,2,0)

 

[Col Delane]

Hi Mugwump

If I understand you correctly:

1. you have a list of events by date on the Data page/sheet, sometimes with more than one event occurring on the same date (but listed separately).
2. on the Events page you wish to list all the events on the Data page that fall within selected weekly date ranges (e.g. 04/02/19 to 10/02/19)
3. VLOOKUP doesn't do a complete job because it only finds the first instance of the matching date (whichever that is?)

Questions:

1. Do you only need to list events for two weekly date ranges as shown for your Event Page Output, or is this just a sample?
2. Given the variable nature of the event data, are you open to re-arranging what your Event Page looks like?
3. Can "helper" cells/columns be added to the Data page?
4. Would you prefer a formulaic solution or are you open to using VBA if necessary?
5. What version of Excel are you using (as there is new functionality/tools in newer versions that may assist)?

 

[Fluff]

@mugwump
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. (rule 12 here: Forum Rules).

I have merged your two threads

 

[mugwump]

Re: alternative to vlookup

thankyou for your help but unfortunately I need the formula to look further right than the first 2 colums and display the data ifrom all the columns relevant to the date.

in otherwords if in the week of 10/2/19 to17/2/2019 there were 7 events in that week it should diplay them all. the first event in column b the second event in column c 3rd event in column d etc etc

 

[mugwump]

thankyou it was a genuine error due to lag my end I posted didn't see anything so posted again. Apologies it will not hppen again.

 

[mugwump]

1. the events scroll up I need to list all events in that weekly date range
2. Happy to rearrange if the solution allows to display in seperate cells all the events for that week
3. not sure on what you mean but if it contributes to it most welcome
4.very very receptive to any solution even more so if VBA does the job
5.2016

 

[Col Delane]

There appears to be something wrong with the dates in post #2 - as there are 88 events between 25/02/19 & 03/03/19 and 4 between 04/03/19 & 10/03/19

"Helper" cells/columns are cells appended to the data table that contain a formula that returns a value that can assist in subsequent summarisations, lookups, etc.

 

[Col Delane]

Mugwump: I have a solution for you (subject to validating the data as per my post #9 ) much like your request that uses Excel [Structured] Tables (with some added "helper" columns) and formulas.

Question: What is the maximum No. of events per week that you expect? (Hopefully not more than 10 otherwise this design is probably not the best approach )