Get the Dates

[anneb_87]

Hi Experts!

I need to get the deadlines:

Client	Deadline
A	21st - 25th of the Month
B
C
D

Here is the source file:

Date Range	10th - 15th of the Month	16th - 20th of the Month	21st - 25th of the Month	26th - 31st of the Month	1st - 5th of the Following Month
# of Clients	9	14	22	15	10
Client List
Monthly
	B	J	E	A	F
		D		K	L
				G
Quarterly
	I	H

Is this possible?

 

[RasGhul]

Maybe this? Drag the formula down;

Book1
	A	B	C	D	E	F
1	Client	Deadline
2	A	26th - 31st of the Month
3	B	10th - 15th of the Month
4	C
5	D	16th - 20th of the Month
6	e	21st - 25th of the Month
7	f	1st - 5th of the Following Month
8	l	1st - 5th of the Following Month
9	h	16th - 20th of the Month
10	i	10th - 15th of the Month
11
12
13
14	Date Range	10th - 15th of the Month	16th - 20th of the Month	21st - 25th of the Month	26th - 31st of the Month	1st - 5th of the Following Month
15	# of Clients	9	14	22	15	10
16	Client List
17	Monthly
18		B	J	E	A	F
19			D		K	L
20					G
21
22
23	Quarterly
24		I	H
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(SUMPRODUCT(--($B$15:$F$24=$A2))=0,"",INDEX($B$14:$F$14,SUMPRODUCT(($B$15:$F$24=$A2)*(COLUMN($B$14:$F$14)-COLUMN($B$14)+1))))

 

[anneb_87]

This works. Thank You so much!!!! I'm saved

 

[RasGhul]

You're welcome

 

[Peter_SSs]

This works.

In that case this slightly shorter one may also work for you.

Book1
	A	B	C	D	E	F
1	Client	Deadline
2	A	26th - 31st of the Month
3	B	10th - 15th of the Month
4	C
5	D	16th - 20th of the Month
6	e	21st - 25th of the Month
7	f	1st - 5th of the Following Month
8	l	1st - 5th of the Following Month
9	h	16th - 20th of the Month
10	i	10th - 15th of the Month
11
12
13
14	Date Range	10th - 15th of the Month	16th - 20th of the Month	21st - 25th of the Month	26th - 31st of the Month	1st - 5th of the Following Month
15	# of Clients	9	14	22	15	10
16	Client List
17	Monthly
18		B	J	E	A	F
19			D		K	L
20					G
21
22
23	Quarterly
24		I	H
Get Deadline

Cell Formulas
Range	Formula
B2:B10	B2	=IF(COUNTIF(B$15:F$24,A2),INDEX($14:$14,SUMPRODUCT((B$15:F$24=A2)*(COLUMN(B$15:F$24)))),"")

 

[Peter_SSs]

.. or even this if you have the CONCAT function in your version of Excel. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down. This one also does not need the initial check to see if the Client actually appears in the lower range.

Book1
	A	B	C	D	E	F
1	Client	Deadline
2	A	26th - 31st of the Month
3	B	10th - 15th of the Month
4	C
5	D	16th - 20th of the Month
6	e	21st - 25th of the Month
7	f	1st - 5th of the Following Month
8	l	1st - 5th of the Following Month
9	h	16th - 20th of the Month
10	i	10th - 15th of the Month
11
12
13
14	Date Range	10th - 15th of the Month	16th - 20th of the Month	21st - 25th of the Month	26th - 31st of the Month	1st - 5th of the Following Month
15	# of Clients	9	14	22	15	10
16	Client List
17	Monthly
18		B	J	E	A	F
19			D		K	L
20					G
21
22
23	Quarterly
24		I	H
Get Deadline

Cell Formulas
Range	Formula
B2:B10	B2	{=CONCAT(IF(B$15:F$24=A2,B$14:F$14,""))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[anneb_87]

I have additional question of this. I need to populate the count of funding

Date Range	10th - 15th of the Month	16th - 20th of the Month	21st - 25th of the Month	26th - 31st of the Month	1st - 5th of the Following Month
Funding
Fully Insured	???
Self Insured	??
Client List
	B	J	E	A	F
		D		K	L
				G

Here is the Funding list

Client	Funding
A	Fully Insured
B	Fully Insured
C	Fully Insured
D	Self Insured

 

[RasGhul]

I had to count by client 1st then sumif on that;

Book1
	A	B	C	D	E	F
1	Date Range	10th - 15th of the Month	16th - 20th of the Month	21st - 25th of the Month	26th - 31st of the Month	1st - 5th of the Following Month
2	Funding
3	Fully Insured	2
4	Self Insured	1
5
6	Client List
7		B	J	E	A	F
8			D		K	L
9					G
10
11	Client	Funding	Client Count
12	A	Fully Insured	1
13	B	Fully Insured	1
14	C	Fully Insured	0
15	D	Self Insured	1
Sheet1

Cell Formulas
Range	Formula
B3	B3	=SUMIFS($C$12:$C$15,$B$12:$B$15,A3)
C12	C12	=SUMPRODUCT(--($B$7:$F$9&B12=A12&B12))

 

[Peter_SSs]

I have additional question of this.

That doesn't indicate which suggestion you used for the first part or whether you have the CONCAT function.
If you do have the CONCAT function then you would also have the TEXTJOIN function so for the follow-up question you could try this. Again an array formula so entered as described previously.

Book1
	A	B	C	D	E	F
14	Date Range	10th - 15th of the Month	16th - 20th of the Month	21st - 25th of the Month	26th - 31st of the Month	1st - 5th of the Following Month
15	# of Clients	9	14	22	15	10
16	Client List
17	Monthly
18		B	J	E	A	F
19			D		K	L
20					G
21
22
23	Quarterly
24		I	H
25
26	Funding
27	Fully Insured	2
28	Self Insured	1
29
30	Client	Funding
31	A	Fully Insured
32	B	Fully Insured
33	C	Fully Insured
34	D	Self Insured
Get Deadline (2)

Cell Formulas
Range	Formula
B27:B28	B27	{=COUNT(SEARCH("|"&B$18:F$20&"|"&A27&"|","|"&TEXTJOIN("|",1,A$31:B$34)&"|"))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[anneb_87]

hmmmm.. I did try your formula

That doesn't indicate which suggestion you used for the first part or whether you have the CONCAT function.
If you do have the CONCAT function then you would also have the TEXTJOIN function so for the follow-up question you could try this. Again an array formula so entered as described previously.

Book1
	A	B	C	D	E	F
14	Date Range	10th - 15th of the Month	16th - 20th of the Month	21st - 25th of the Month	26th - 31st of the Month	1st - 5th of the Following Month
15	# of Clients	9	14	22	15	10
16	Client List
17	Monthly
18		B	J	E	A	F
19			D		K	L
20					G
21
22
23	Quarterly
24		I	H
25
26	Funding
27	Fully Insured	2
28	Self Insured	1
29
30	Client	Funding
31	A	Fully Insured
32	B	Fully Insured
33	C	Fully Insured
34	D	Self Insured
Get Deadline (2)

Cell Formulas
Range	Formula
B27:B28	B27	{=COUNT(SEARCH("|"&B$18:F$20&"|"&A27&"|","|"&TEXTJOIN("|",1,A$31:B$34)&"|"))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

hmmm I did try your formula but I was not able to get tha value..

{=COUNT(SEARCH("|"&$C$21:$G$45&"|"&$B$16&"|","|"&TEXTJOIN("|",1,'Account Listing'!$A:$G&"|")))}

in the Account Listing tab, column A had the account name then column G had the Funding type