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)

Event page output Week commencing	Week ending
4/02/2019	10/02/2019	​​Pacific Jewel (T) Station Pier, Port Melbourne	HAL Rd 18 - Melbourne Victory v Perth Glory AAMI Park
11/02/2019	17/02/2019	University exams Caulfield Racecourse	BBL Final - TBC v TBC MCG

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

Data page information

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

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

 

[mugwump]

expectation is maximum 10 events per week. Thank you so much for your help

 

[sandy666]

is that what you want? (with ALL distinct events)

exaple based on your example

Min

Max

Event.1

Event.2

Event.3

Event.4

Event.5

Event.6

Event.7

Event.8

Event.9

Event.10

Event.11

Event.12

Event.13

Event.14

Event.15

Event.16

Event.17

Event.18

Event.19

Event.20

Event.21

Event.22

Event.23

Event.24

Event.25

Event.26

Event.27

Event.28

Event.29

Event.30

Event.31

25/02/2019​

03/03/2019​

?Golden Princess (T)

University exams

Caravan & Camping Super Show 2019

Bryan Ferry in concert

Arctic Monkeys - Live 2019

2019 Australian International Airshow & Aerospace & Defence Expo

?Noordam

Europa

??Carnival Legend (T)

Summer Night Market

Europa (T)

?Crystal Serenity

2019 JLT Community Series Week 1 - Carlton v Essendon

SOuth Melbourne Night Market

?Pacific Eden

Night Gallops

Mordialloc Food, Wine & Music Festival

SANZAAR Super Rugby Union Round 3 - Melbourne Rebels v Highlanders (New Zealand)

??Pacific Jewel (T)

Beer Incider 2019

Australian Guineas Day presented by 3AW

HAL Rd 21 - Melbourne Victory v Newcastle Jets

2019 NAB AFLW Round 5 - Carlton v Collingwood

Ulysses Club National Rally - Grand Parade

2019 JLT Community Series Week 1 - Nth Melbourne v St Kilda

2019 JLT Community Series - Week 1 - Richmond v Melbourne

HAL Rd 21 - Melbourne City v Perth Glory

Glenferrie Festival 2019

Commonwealth Bank Women's ODI Series -Australia v New Zealand

Sheffield Shield Victoria v Tasmania Day 1

Carnival Legend (T)

04/03/2019​

10/03/2019​

Sheffield Shield Victoria v Tasmania Day 2

??Pacific Jewel (T)

 

[sandy666]

Min

Max

Event.1

Event.2

Event.3

Event.4

Event.5

Event.6

Event.7

Event.8

Event.9

Event.10

Event.11

Event.12

Event.13

Event.14

Event.15

Event.16

Event.17

Event.18

Event.19

Event.20

Event.21

Event.22

Event.23

Event.24

Event.25

Event.26

Event.27

Event.28

Event.29

Event.30

Event.31

25/02/2019​

03/03/2019​

Golden Princess (T)

University exams

Caravan & Camping Super Show 2019

Bryan Ferry in concert

Arctic Monkeys - Live 2019

2019 Australian International Airshow & Aerospace & Defence Expo

Noordam

Europa

Carnival Legend (T)

Summer Night Market

Europa (T)

Crystal Serenity

2019 JLT Community Series Week 1 - Carlton v Essendon

SOuth Melbourne Night Market

Pacific Eden

Night Gallops

Mordialloc Food, Wine & Music Festival

SANZAAR Super Rugby Union Round 3 - Melbourne Rebels v Highlanders (New Zealand)

Pacific Jewel (T)

Beer Incider 2019

Australian Guineas Day presented by 3AW

HAL Rd 21 - Melbourne Victory v Newcastle Jets

2019 NAB AFLW Round 5 - Carlton v Collingwood

Ulysses Club National Rally - Grand Parade

2019 JLT Community Series Week 1 - Nth Melbourne v St Kilda

2019 JLT Community Series - Week 1 - Richmond v Melbourne

HAL Rd 21 - Melbourne City v Perth Glory

Glenferrie Festival 2019

Commonwealth Bank Women's ODI Series -Australia v New Zealand

Sheffield Shield Victoria v Tasmania Day 1

04/03/2019​

10/03/2019​

Sheffield Shield Victoria v Tasmania Day 2

Pacific Jewel (T)

here is version without question marks

in your source you have hidden characters code 63 or unicode 8203 so clean your data first

 

[Col Delane]

A1	B1	C1	D1	E1	F1
Data sheet
Table Name:	tblData
Data	Data	Formula	Formula	Formula	Formula
Date	Event	Week Beginning	Week Ending	Event Index	Lookup Key
Mon 25-02-19	University exams	25-02-19	03-03-19	1	435211
Mon 25-02-19	​Golden Princess (T)	25-02-19	03-03-19	2	435212

<tbody>
</tbody>

NOTE: Cell references (A1, B1, etc.) represent the actual position of those values but are not included on the actual worksheet - only shown here for demonstration purposes to indicate the posiion of rows/columns.

The values in A6, C6 & D6 (and corresponding rows below) are date numbers formatted as dates.
The values in E6 & F6 (and corresponding rows below) are integers.

Formulas: (copy down as required)
C6 = tblData[[#This Row],[Date]] - WEEKDAY( tblData[[#This Row],[Date]], 2) + 1
D6 = tblData[[#This Row],[Week Beginning]] + 6
E6 = COUNTIFS( $C$5:tblData[[#This Row],[Week Beginning]], tblData[[#This Row],[Week Beginning]] )
F6 = ( tblData[[#This Row],[Week Beginning]] & tblData[[#This Row],[Event Index]] ) *1

Event Summary sheet

A1	B1	C1	D1	E1	F1	G1	H1	I1	J1	K1	L1
A2
Start Date:	25-02-19
A4
Table Name:	tblEvents
					Events scheduled for each week
Week No.	Week Beginning	Week Ending	No. of Events	Check Calc.	1	2	3	4	5	6	7
1	25-02-19	03-03-19	88	Error!	University exams	​Golden Princess (T)	Caravan & Camping Super Show 2019	University exams	​Golden Princess (T)	Caravan & Camping Super Show 2019	University exams
2	04-03-19	10-03-19	4	Ok	Sheffield Shield Victoria v Tasmania Day 2	​​Pacific Jewel (T)	Sheffield Shield Victoria v Tasmania Day 2	​​Pacific Jewel (T)	-	-	-

<tbody>
</tbody>

Formulas: (copy down & across as required)
A8 = ROW( tblEvents[[#This Row],[Week Beginning]]) - ROW( tblEvents[[#Headers],[Week No.]])
B8 = IF( tblEvents[[#This Row],[Week No.]] = 1, $B$3, OFFSET( tblEvents[[#This Row],[Week Beginning]], -1,0) + 7 )
C8 = tblEvents[[#This Row],[Week Beginning]] + 6
D8 = COUNTIFS( tblData[Week Beginning], tblEvents[[#This Row],[Week Beginning]] )
E8 = IF( COUNTIFS( tblEvents[[#This Row],[1 ]:[10 ]], "<>" & "-") = tblEvents[[#This Row],[No. of Events]], "Ok", "Error!" )
F8 = IFERROR( INDEX( tblData[Event], MATCH( ( tblEvents[[#This Row],[Week Beginning]] & tblEvents[[#Headers],[1 ]] )*1, tblData[Lookup Key], 0) ), "-" )

The values in F7:L7 are text.
The formulas in F8:L?? (expand as required) need to refer relatively to the field header in row 7 (so be careful when copying within the table)