Macro to enter value based on start date's day of the week

[Jibroni]

Hello,

I have the following table for which I need a macro that enters a value into column G, SESSION_TEMPLATE, based on the values found in columns B, CLASS_NO and F, START_DATE:

ID	CLASS_NO	STATUS	COURSE	DOMAIN	START_DATE	SESSION_TEMPLATE
06052022_GST_402	06052022_GST_402	100​	11202V	Shared	6/5/2022​
07042022_GMT_401	07042022_GMT_401	100​	21201V	Shared	7/4/2022​
07062022_GMT_330	07062022_GMT_330	100​	3267​	Shared	7/6/2022​
07072022_MT_305	07072022_MT_305	100​	3237​	Shared	7/7/2022​
07112022_SGT_301	07112022_SGT_301	100​	6541​	Shared	7/11/2022​
07142022_SGT_302	07142022_SGT_302	100​	6545​	Shared	7/14/2022​

The value I need to enter into column G, SESSION_TEMPLATE, is specific to the day of the week found in column F and the last three numbers of the string of characters found in column B. For example, for row 1, I want the value needs to be "1-day Sun" because the 402 (from 06052022_GST_402) = 1-day and the date in the START_DATE column for this row is a Sunday (6/5/2022). Another example, in row 2, I want the value to be "2-day Mon" because 401 = 2-day and the date in START_DATE column for this row is a Monday (7/4/2022).

The rest of the key is 330 = 3-day, 305 = 1-day, 301 = 3-day, and 302 = 1-day.

If you have a formula that does the trick, that works as well.

 

[Jibroni]

Bumping this in hopes someone out there might know

 

[Peter_SSs]

If you have a formula that does the trick, that works as well.

Use a lookup table (even the same one?) like I suggested in your other thread, then a formula like this.

Jibroni.xlsm
	A					F	G	H	I	J
1	ID					START_DATE	SESSION_TEMPLATE		Code	Days
2	06052022_GST_402					5/06/2022	1-Sunday		301	3
3	07042022_GMT_401					4/07/2022	2-Monday		401	2
4	07062022_GMT_330					6/07/2022	3-Wednesday		402	1
5	07072022_MT_305					7/07/2022	1-Thursday		302	1
6	07112022_SGT_301					11/07/2022	3-Monday		305	1
7	07142022_SGT_302					14/07/2022	1-Thursday		330	3
Session Template

Cell Formulas
Range	Formula
G2:G7	G2	=VLOOKUP(RIGHT(A2,3)+0,I$2:J$10,2,0)&TEXT(F2,"-dddd")

 

[Jibroni]

Thank you @Peter_SSs. This gives me the result I want. Awesome!

For any cell in column A that has "_GST_", I want the value in column G to say "META" before the "Days-Start Day". Using the first row as an example, the formula would result in the value being "META 1-Sunday". Is there any way to update the formula to do this? Would I add additional codes to the lookup table, that could be something like, "GST_402" = "META 1-Sunday". If so, I need to understand how the formula you shared finds the code from column I. I really appreciate your help.

 

[Peter_SSs]

Try this

Jibroni.xlsm
	A					F	G	H	I	J
1	ID					START_DATE	SESSION_TEMPLATE		Code	Days
2	06052022_GST_402					5/06/2022	META 1-Sunday		301	3
3	07042022_GMT_401					4/07/2022	2-Monday		401	2
4	07062022_GMT_330					6/07/2022	3-Wednesday		402	1
5	07072022_MT_305					7/07/2022	1-Thursday		302	1
6	07112022_SGT_301					11/07/2022	3-Monday		305	1
7	07142022_SGT_302					14/07/2022	1-Thursday		330	3
Session Template (2)

Cell Formulas
Range	Formula
G2:G7	G2	=IF(ISNUMBER(FIND("_GST_",A2)),"META ","")&VLOOKUP(RIGHT(A2,3)+0,I$2:J$10,2,0)&TEXT(F2,"-dddd")

 

[Jibroni]

Magic! Thank you so much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[bebo021999]

Alternative, without using helper columns

=IF(ISNUMBER(FIND("_GST_",A2)),"META ","")&LOOKUP(RIGHT(A2,3)+0,{301;302;305;330;401;402},{3;1;1;3;2;1})&TEXT(F2,"-dddd")

 

[Jibroni]

Hi @bebo021999 , I am going to give your suggestion a try and follow up. Thank you!