Flagging Grouped Records which have more than one reason

[Masond3]

Team, 

I am drawing a blank and struggling of a way to solve this 

AIM : To create a helper column that flags groups which have different reasons associated to them 

Input 

Group	Reason
839	Exact First Name
839	Cant Identify
839	Exact First Name
2	Exact First Name, Exact Last Name, Exact Email
2	Exact First Name, Exact Last Name, Exact Email

OutPut 

Group	Reason	Helper
839	Exact First Name	Y
839	Cant Identify	Y
839	Exact First Name	Y
2	Exact First Name, Exact Last Name, Exact Email	N
2	Exact First Name, Exact Last Name, Exact Email	N

Regards
Masond3

[Felipe_Ribeir0]

Hi @Masond3 

Try this formula, it is basically counting "," and then assigning the label (the contains formula would work for your case too):

IF REGEX_CountMatches([Reason], ',') = 0 
THEN 'Y' 
ELSE 'N' 
ENDIF

[Masond3]

Hi, 

I dont think that would work as some of my reasons do have "commas" associated. 

Any other potential solutions ? 

[Felipe_Ribeir0]

Hi @Masond3 

Can you share some of the reasons that contains comma? Or even all possible reasons. 

[Masond3]

These are the current values i have for reason

Exact First Name, Exact Last Name, Exact Email

Exact L4, Different l5, Name with Nickname Reveresed First Name or Reveresed Last Name, Exact Email

Cant Identify

Name with Nickname Reveresed First Name or Reveresed Last Name, Exact Email

Exact L4, Different l5, Exact First Name,  Exact Last Name, Exact Email

Exact L4, Exact l5,Exact Last Name, Exact Email

Exact L4, Exact l5, Exact First Name, Exact Last Name, Exact Email

Exact L4, Exact l5,Exact First Name, Exact Email

Exact L4,  Different l5, First Name with NickName,  Exact Last Name, Exact Email

Dummy

First Name with Nicknames, Exact Last Name

Exact L4, Exact l5, Name with Nickname Reveresed First Name or Reveresed Last Name, Exact Email

Name with Nickname First Name or Name with Nickname last Name, Exact Email

Exact L4, Exact l5,  First Name with NickName,  Exact Last Name, Exact Email

Exact L4, Exact l5, Exact Reveresed First Name or Reveresed Last Name, Exact Email

Exact Reveresed First Name or Reveresed Last Name, Exact Email

Exact L4, Different l5, Exact Reveresed First Name or Reveresed Last Name, Exact Email

[Masond3]

Hey  

I think i may of done it by using the Sumrasiation tool

• Group by "Group"
• Count Distinct by "Reason"

Then i can apply the following formula "if [CountDistinct_Reason] >1 THEN 'Y' ELSE 'N' ENDIF"

[PhilipMannering]

It doesn't look like any of these reasons have a comma in them. Think @Felipe_Ribeir0 's solution should work.

[Felipe_Ribeir0 · Accepted solution]

Hi @Masond3 

Maybe now i understood your problem, try this version:

[Masond3]

Thats the one.

Took me ages to work through it, but you gave me some ideas in your formula. 

Huge thank you :)

[Felipe_Ribeir0]

@Masond3 

My first impression was that you had different reasons per row separated by comma:

But you have different reasons with the same group sepparared by row.

I am glad it worked.