Return values from data set B based on range of dates in data set A

[Marklarbear]

HI all - Im using Microsoft Office 365 ProPlus.

I have 2 data sets (set A & set B). In set A i have a date range - start of range in cells E & end of date range in cells F. In data set B, I have a range of dates of special days (E26:E100) with date description in column F (F26:F100). What I need to do is in data set A in column G, is to return any special days that fall within the range along with its applicable date (as in the screen shot G12 needs to return 3 special days of data into the cell, where as G11 only has 1 special day).

Data set A will only ever have 13 cycles in it - however the dates will change from year to year. Data set B can have multiple years of data in it. some of those days will repeat (ie Mothers Day), however the date themselves will be unique to that year.

Any help with a formula to go into cells G7 to G19 in set A would be greatly appreciated....

 

[Peter_SSs]

Im using Microsoft Office 365 ProPlus.

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using.

Also suggest posting sample data with XL2BB as helpers generally are not interested in typing it.

See if this does what you want.

20 02 28.xlsm
	E	F	G
7	15/01/2021
8	12/02/2021
9	12/03/2021
10	9/04/2021		Special 1 (03/05)
11	7/05/2021		Special 2 (09/05)
12	4/06/2021		Special 3 (07/06), Special 4 (14/06), Special 5 (28/06)
13	2/07/2021
14	30/07/2021		Special 6 (02/08), Special 7 (11/08)
15	.
16
17
18
19
20
21
22
23
24
25
26	3/05/2021	Special 1
27	9/05/2021	Special 2
28	7/06/2021	Special 3
29	14/06/2021	Special 4
30	28/06/2021	Special 5
31	2/08/2021	Special 6
32	11/08/2021	Special 7
Special Days

Cell Formulas
Range	Formula
G7:G14	G7	=TEXTJOIN(", ",TRUE,IF(LOOKUP(E$26:E$32,E$7:E$14)=E7,F$26:F$32 & TEXT(E$26:E$32," (dd/mm)"),""))

 

[Marklarbear]

Thankyou for replying .... and the advise to use XL2BB

I have tried your formula but i seem to be doing something wrong..... The below is the range where im placing the formula:

WBC SBG Rec Allocations - build.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
26	Rec Leave Cycles			Special Days
27	Cycle NO	Cycle Start	Cycle Stop
28	1	15/01/2021	11/02/2021	#N/A
29	2	12/02/2021	11/03/2021	#N/A
30	3	12/03/2021	8/04/2021	#N/A
31	4	9/04/2021	6/05/2021	#N/A
32	5	7/05/2021	3/06/2021	#N/A
33	6	4/06/2021	1/07/2021	#N/A
34	7	2/07/2021	29/07/2021	#N/A
35	8	30/07/2021	26/08/2021	#N/A
36	9	27/08/2021	23/09/2021	#N/A
37	10	24/09/2021	21/10/2021	#N/A
38	11	22/10/2021	18/11/2021	#N/A
39	12	19/11/2021	16/12/2021	#N/A
40	13	17/12/2021	13/01/2022	#N/A
SBG Summary (2)

Cell Formulas
Range	Formula
C28	C28	=Inputs!B6
D28:D40	D28	=C28+27
E28:E40	E28	=TEXTJOIN(", ",TRUE,IF(LOOKUP('Special Days'!$D$2:$D$100,$C$28:$C$40)=C28,'Special Days'!$E$2:$E$100 & TEXT('Special Days'!$D$2:$D$100," (dd/mm)"),""))
C29:C40	C29	=D28+1

The range where im placing the special days is on another tab:

WBC SBG Rec Allocations - build.xlsx
	D	E
1	Date	Special Day
2	1/01/2020	New Years Day
3	26/01/2020	Australia Day (National)
4	27/01/2020	Australia Day (National)
5	26/02/2020	Launceston Cup Day
6	2/03/2020	Labour Day (WA)
7	9/03/2020	Canberra Day(ACT), Labour Day (VIC) ,Eight Hour Day (TAS) ,Adelaide Cup Day (SA)
8	10/04/2020	Good Friday(National)
9	11/04/2020	Easter Saturday (Local - as WA or Tasmania not declaring it)
10	12/04/2020	Easter Sunday
11	13/04/2020	Easter Monday (National)
12	14/04/2020	Easter Tuesday Tas only
13	25/04/2020	Anzac Day(National)
14	27/04/2020	Anzac Day PH WA only
15	4/05/2020	QLD Labour Day
16	10/05/2020	Mother’s Day – 2nd Sunday in May
17	1/06/2020	Western Australia Day (WA)
18	8/06/2020	Queen's Birthday (NSW ACT VIC TAS SA NT QLD)
19	30/06/2020	EOFY - where applicable review last week of June to 2nd week of July.
20	3/08/2020	NSW Bank Holiday
21	12/08/2020	QLD Show Day
22	28/08/2020	Gold Coast Show Day
23	6/09/2020	Father’s Day – 1st Sunday in September
24	25/09/2020	VIC - AFL Grand Final Friday
25	26/09/2020	AFL Grand Final – Last Saturday in September
26	28/09/2020	Queens Birthday (WA)
27	4/10/2020	NRL Grand Final – First Sunday in October
28	5/10/2020	Labour Day(NSW ACT QLD SA)
29	8/10/2020	Royal Launceston Show Day (TAS)
30	11/10/2020	Bathurst (car race) – Second Sunday in October
31	2/11/2020	Recreation Day
32	3/11/2020	Melb Cup day
33	25/12/2020	Christmas Day(National)
34	26/12/2020	Boxing Day (National)
35	1/01/2021	New Years Day
36	26/01/2021	Australia Day (National)
37	24/02/2021	Launceston Cup Day
38	1/03/2021	Labour Day (WA)
39	8/03/2021	Canberra Day(ACT), Labour Day (VIC) ,Eight Hour Day (TAS) ,Adelaide Cup Day (SA)
40	2/04/2021	Good Friday(National)
41	3/04/2021	Easter Saturday (Local - as WA or Tasmania not declaring it)
42	4/04/2021	Easter Sunday
43	5/04/2021	Easter Monday (National)
44	6/04/2021	Easter Tuesday Tas only
45	25/04/2021	Anzac Day(National)
46	26/04/2021	Anzac Day PH WA only
47	3/05/2021	QLD Labour Day
48	9/05/2021	Mother’s Day – 2nd Sunday in May
49	7/06/2021	Western Australia Day (WA)
50	14/06/2021	Queen's Birthday (NSW ACT VIC TAS SA NT QLD)
51	28/06/2021	EOFY - where applicable review last week of June to 2nd week of July.
52	2/08/2021	NSW Bank Holiday
53	11/08/2021	QLD Show Day
54	27/08/2021	Gold Coast Show Day
55	5/09/2021	Father’s Day – 1st Sunday in September
56	24/09/2021	VIC - AFL Grand Final Friday
57	25/09/2021	AFL Grand Final – Last Saturday in September
58	27/09/2021	Queens Birthday (WA)
59	3/10/2021	NRL Grand Final – First Sunday in October
60	4/10/2021	Labour Day(NSW ACT QLD SA)
61	7/10/2021	Royal Launceston Show Day (TAS)
62	10/10/2021	Bathurst (car race) – Second Sunday in October
63	1/11/2021	Recreation Day
64	2/11/2021	Melb Cup day
65	25/12/2021	Christmas Day(National)
66	26/12/2021	Boxing Day (National)
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Special Days

I seem to get #N/A .... not sure where im going wrong? (on the special days tab ive gone down to row 100 to allow for any additional special events to be included if needed in the future)

 

[Peter_SSs]

I have tried your formula but i seem to be doing something wrong..

The problem is that in your original sample data, no dates in the 'Special' range (E26:E32) were before the first date in your 'main' range (E7).
In you latest data there are dates in the 'Special' range before the first date in your 'main' range.

Try this instead.

Marklarbear 2020-03-10 1.xlsm
	B	C	D	E
27	Cycle NO	Cycle Start	Cycle Stop
28	1	15/01/2021	11/02/2021	Australia Day (National) (26/01)
29	2	12/02/2021	11/03/2021	Launceston Cup Day (24/02), Labour Day (WA) (01/03), Canberra Day(ACT), Labour Day (VIC) ,Eight Hour Day (TAS) ,Adelaide Cup Day (SA) (08/03)
30	3	12/03/2021	8/04/2021	Good Friday(National) (02/04), Easter Saturday (Local - as WA or Tasmania not declaring it) (03/04), Easter Sunday (04/04), Easter Monday (National) (05/04), Easter Tuesday Tas only (06/04)
31	4	9/04/2021	6/05/2021	Anzac Day(National) (25/04), Anzac Day PH WA only (26/04), QLD Labour Day (03/05)
32	5	7/05/2021	3/06/2021	Mother’s Day – 2nd Sunday in May (09/05)
33	6	4/06/2021	1/07/2021	Western Australia Day (WA) (07/06), Queen's Birthday (NSW ACT VIC TAS SA NT QLD) (14/06), EOFY - where applicable review last week of June to 2nd week of July. (28/06)
34	7	2/07/2021	29/07/2021
35	8	30/07/2021	26/08/2021	NSW Bank Holiday (02/08), QLD Show Day (11/08)
36	9	27/08/2021	23/09/2021	Gold Coast Show Day (27/08), Father’s Day – 1st Sunday in September (05/09)
37	10	24/09/2021	21/10/2021	VIC - AFL Grand Final Friday (24/09), AFL Grand Final – Last Saturday in September (25/09), Queens Birthday (WA) (27/09), NRL Grand Final – First Sunday in October (03/10), Labour Day(NSW ACT QLD SA) (04/10), Royal Launceston Show Day (TAS) (07/10), Bathurst (car race) – Second Sunday in October (10/10)
38	11	22/10/2021	18/11/2021	Recreation Day (01/11), Melb Cup day (02/11)
39	12	19/11/2021	16/12/2021
40	13	17/12/2021	13/01/2022	Christmas Day(National) (25/12), Boxing Day (National) (26/12)
SBG Summary (2)

Cell Formulas
Range	Formula
D28:D40	D28	=C28+27
E28:E40	E28	=TEXTJOIN(", ",TRUE,IF(IFERROR(LOOKUP('Special Days'!D$2:D$100,C28:C40)=C28,),'Special Days'!$E$2:$E$100 & TEXT('Special Days'!$D$2:$D$100," (dd/mm)"),""))
C29:C40	C29	=D28+1

 

[Marklarbear]

Brilliant - that worked perfectly! thank you for your help and time

 

[Peter_SSs]

Brilliant - that worked perfectly! thank you for your help and time

You're welcome. Glad it worked for you.