Conditional Values (Formula)

[Yeonsujen]

Hello All,

I need some help with changing values depending on the given condition below.

I've made a new "Code" column where Name A = Code 1, Name B = Code 2, etc. with the following formula:

IF [Name] = "A" then "1"
elseif [Name] = "B" then "2"
elseif [Name] = "C" then "3"
elseif [Name] = "D" then "4"
elseif [Name] = "E" then "5"
elseif [Name] = "F" then "6"
elseif [Name] = "G" then "7"
else "0" endif

This repeats on a monthly basis:

Date	Name	Amount	Code
2021-07-31 00:00:00	A	100	1
2021-07-31 00:00:00	B	100	2
2021-07-31 00:00:00	C	0	3
2021-07-31 00:00:00	D	100	4
2021-07-31 00:00:00	E	100	5
2021-07-31 00:00:00	F	100	6
2021-07-31 00:00:00	G	100	7
2021-08-31 00:00:00	A	100	1
2021-08-31 00:00:00	B	100	2
2021-08-31 00:00:00	C	100	3
2021-08-31 00:00:00	D	100	4
2021-08-31 00:00:00	E	100	5
2021-08-31 00:00:00	F	100	6
2021-08-31 00:00:00	G	100	7

However, now I have a new condition where if Name "C" has Amount "0" then the Code for Name "A" needs to be "11". This also needs to take the dates into consideration as some monthly dates may have Name "C" with an Amount greater than 0 and can remain with the same Code "1".

Essentially, I need to get the following output:

Date	Name	Amount	Code
2021-07-31 00:00:00	A	100	11
2021-07-31 00:00:00	B	100	2
2021-07-31 00:00:00	C	0	3
2021-07-31 00:00:00	D	100	4
2021-07-31 00:00:00	E	100	5
2021-07-31 00:00:00	F	100	6
2021-07-31 00:00:00	G	100	7
2021-08-31 00:00:00	A	100	1
2021-08-31 00:00:00	B	100	2
2021-08-31 00:00:00	C	100	3
2021-08-31 00:00:00	D	100	4
2021-08-31 00:00:00	E	100	5
2021-08-31 00:00:00	F	100	6
2021-08-31 00:00:00	G	100	7

Please let me know how I can solve this!

[Christina_H · Accepted solution]

See attached.  I've used a mapping table rather than an if formula to assign the original codes but the rest of the process will still apply if you prefer a formula.

[JoeHerbert]

Hi @Yeonsujen , I've used some summarises & a formula to create the New_Code, column you are after, enjoy, Happy solving 😄 

Joe 

[JoeHerbert]

Much smoother from @Christina_H 

[Yeonsujen]

@Christina_H Thank you for your response, it worked perfectly! 🙂

@JoeHerbert Thank you as well for your response! It also worked and agree with the above that Christina's is smoother. Always interesting to see different methods regardless!

[atcodedog05]

_

[Christina_H]

@Yeonsujen I think this is a simple fix - you need to change the join tool to join by both date and employee ID

[Yeonsujen]

@Christina_H Sorry I edited my comment before! Yes this helped to fix it in the end, thank you again! 🙂