Match Data Based on the column value

[geeklarokcmie]

Hello Experts,

Currently my input data has the below four columns.

I'd like to ensure whenever there is NC and 7numeric values next to it in columns description and summary - it should result out in a new column with KEY values or say No Match.

Input Data :

Project	Key	Description	Summary
Railways	RL-1567	Priority tickets to be incorporated	NC5847865 tag and include
Engineering	EG-2564	Inclusion of NC8745624	process files within NC8745624
Science	SC-6598	Fix Data NC9875452	terminatedteamhasidentity
History	HS-0258	corporatedata into asssement incorporated	fix missing field NC0254563
English	EG-7896	process not working	prepare test for NC9965523
Math	MATH-4569	set up incidents of process	lake streaming with details in NC0245635

Expected Output:

Project	Key	Description	Summary	Results
Railways	RL-1567	Priority tickets to be incorporated	NC5847865 tag and include	RL-1567
Engineering	EG-2564	Inclusion of NC8745624	process files within NC8745624	EG-2564
Science	SC-6598	Fix Data incorporated	terminatedteamhasidentity	No Match
History	HS-0258	corporatedata into asssement incorporated	fix missing field NC0254563	HS-0258
English	EG-7896	process not working NC9965523	prepare test incline	EG-7896
Math	MATH-4569	set up incidents of process	lake streaming with details	No Match

[DataNath · Accepted solution]

Hey @geeklarokcmie, we can use RegEx to look for certain string patterns so I went down the route of checking if there's a match of NC followed by 7 numerics with the following approach. Only thing is the last record in your scenario above - according to the logic this should pull the [Key] through but you have 'No Match' in your expected output - is this an error?

[aatalai · Accepted solution]

Hi that could be done wiht the formula tool and regex match

If regex_match([description],".*NC\d{7}.*") or regex_match([Summary],".*NC\d{7}.*")  then [Key

else "No match" endif

[geeklarokcmie]

@aatalai & @DataNath - Thank you so much for prompt response. Both your forumla worked the way I wanted it to. ^_^

[AmitMiller]

Hi @geeklarokcmie, thank you for your question!

Do note -

1. your "expected output" is different from your "input data" in lines 3,5,6. This is why DataNath asked about an error
2. make sure you check both columns. You've written "in columns description and summary", but it looks like you meant or - since that's how you've implemented it. So that's how we refer to it =]
3. If you want to be pedantic, and make sure there are no more than 7 digits - do let us know, as it will require a bit more tinkering.

Now, important to notice - the two excellent answers above utilize different functions.

1. Regex_match - which needs to match the entire field. That's why padding the pattern with .* (any character, repeated unlimited times) is needed
2. Regex_countmatches - This counts the number of times the pattern was matched (=found) in the field. Hence, doesn't need the .* padding.
   1. This is also why a ">0" was added - as you need to make sure you've found at least one match

Having said all that - attached is a solution that takes both approaches into account:

notice that I didn't write ">0" in the countmatches option - that's due to it either being 0 or 1. A logical expression that equals 1 is evaluated as True. Should work for all values bigger than 0

Hope that helps!

[geeklarokcmie]

@DataNath I completely missed the part where the input data columns for summary and description were not as same as output data. I should have corrected that before posting. Appreciate your solution as it worked. 

@AmitMiller Appreciate the clarification and providing two different solutions along with the reasoning. I will run your workflows to understand  how it satisfies my data requirements.