trying to display results by date

mugwump

New Member
Joined
Feb 3, 2019
Messages
6
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)
Event page output
Week commencing
Week ending
4/02/201910/02/2019​​Pacific Jewel (T) Station Pier, Port Melbourne HAL Rd 18 - Melbourne Victory v Perth Glory AAMI Park
11/02/201917/02/2019University exams Caulfield Racecourse BBL Final - TBC v TBC MCG

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
Data page information
DateColumn1
15/11/2020ICC Men's World Twenty20 Final MCG
8/03/2020ICC Women's World Twenty20 Final MCG
24/11/2019International Convention of Jehovah's Witnesses Marvel Stadium
23/11/2019International Convention of Jehovah's Witnesses Marvel Stadium
22/11/2019International Convention of Jehovah's Witnesses Marvel Stadium
22/11/2019Kiss - End of the Road World Tour Rod Laver Arena
21/11/2019Kiss - End of the Road World Tour Rod Laver Arena
31/10/2019Shawn Mendes in concert Rod Laver Arena
30/10/2019Shawn Mendes in concert Rod Laver Arena
29/10/2019Shawn Mendes in concert Rod Laver Arena
29/10/2019Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre
28/10/2019Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre
27/10/2019Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre
26/10/2019Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre
25/10/2019Australian Masters Athletics National Track and Field Championships Melbourne Sports & Aquatic Centre

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

mugwump

New Member
Joined
Feb 3, 2019
Messages
6
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 :ROFLMAO: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

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

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

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

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

sample of output

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

25/02/20193/03/2019University exams Caulfield Racecourse 2019 JLT Community Series - Week 1 - Richmond v Melbourne #N/A#N/A
4/03/201910/03/2019Sheffield Shield Victoria v Tasmania Day 2 MCG 2019 JLT Community Series - Week 2 - Western Bulldogs v St Kilda MARS Stadium (Ballarat) #N/A#N/A
11/03/201917/03/20192019 JLT Community Series - Week 2 - Collingwood v Carlton 2019 NAB AFLW - Round 7 - Collingwood v Brisbane Lions #N/A#N/A
18/03/201924/03/2019​Queen Elizabeth (T) Station Pier, Port Melbourne AFL Round 1 - St Kilda v Gold Coast SUNS Marvel Stadium #N/A#N/A

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

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,283
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

Active Member
Joined
Jan 14, 2014
Messages
303
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,078
Office Version
365
Platform
Windows
@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

New Member
Joined
Feb 3, 2019
Messages
6
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

New Member
Joined
Feb 3, 2019
Messages
6
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

New Member
Joined
Feb 3, 2019
Messages
6
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

Active Member
Joined
Jan 14, 2014
Messages
303
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:confused:

"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

Active Member
Joined
Jan 14, 2014
Messages
303
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 :eek:)
 

Forum statistics

Threads
1,081,702
Messages
5,360,743
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top