Loop through multiple conditions

[Dandil28]

Hello,

I am trying to trying to loop through a table of rules to pull in the alignment of my data. The keys on the transactional data, which alignment needs to be mapped to, are Account code, Company code, Department code, Channel code, Product code and Region code. The keys on the rules data are Account number, Company, Department, Channel, Product and Region. The Rules have wild characters included. Is there a way to loop through these rules/conditions and pull in the last match?
Below is the original format with rules grouped together. 

Below is the same list but I have split out via text to column , split to rows. I replaced All with * and % with *. 

Thank you in advance. 
Daniel

[MatthewO]

@Dandil28 if I am understanding correctly from the screenshots, it looks like you could accomplish this using a Text to Columns tool. When configuring the tool, choose the column to split, specify the delimiter (comma in this case), and then choose Split to rows. You would need to repeat this step for each column that needs to be split but I believe this would give you the desired result.

[Dandil28]

Hey MatthewO,

Sorry for the confusion. I used the text to column to split into rows. I included both screenshots because I wasn't sure if it was easier to loop through screenshot 1 or 2. I am trying to compare my base data against these rules tables to pull in the segment. For example, if account number starts with #HC and Company is B068 and Department starts with 84 and any channel and any product and any region then align to segment A11. But the later in the iteration it could get updated because a rule further down the list could override it. Like if account number starts with #HC and Company is B068 and Department starts with 84 and any channel and product starts with 11 and any region then align to segment A22. This has to be done by each row in the base dataset. 

Does this make more sense?

[MatthewO]

@Dandil28 It sounds like this list would serve as a reference table to match another data set to in order to determine it's segment? It is possible that you could accomplish this using a macro but the logic could get complex. I'm curious if it would be possible to simplify the logic. For example, comparing only the first 2 characters of a department code. In that scenario, you could extract the first two characters from both data sets using a Formula tool, then join the necessary values.

[Dandil28]

Unfortunately I do not believe we can simplify the logic. If you see the first line and last line of the screenshot, it would provide the incorrect alignment. 

If there was a way to compare as it loops through, I think the formula would be something like:

IF
IF [Rules.AccountNumber] = "All" THEN -1 ELSE StartsWith([MainDataSet.AccountNumber], Length([Rules.AccountNumber])) = [Rules.AccountNumber] ENDIF
IF [Rules.Company] THEN -1 ELSE StartsWith([MainDataSet.Company], Length([Rules.Company])) = [Rules.Company] ENDIF AND
IF [Rules.Department] THEN -1 ELSE StartsWith([MainDataSet.Department], Length([Rules.Department])) = [Rules.Department] ENDIF AND
IF [Rules.Channel] THEN -1 ELSE StartsWith([MainDataSet.Channel], Length([Rules.Channel])) = [Rules.Channel] ENDIF AND
IF [Rules.Product] THEN -1 ELSE StartsWith([MainDataSet.Product], Length([Rules.Product])) = [Rules.Product] ENDIF AND
IF [Rules.Region] THEN -1 ELSE StartsWith([MainDataSet.Region], Length([Rules.Region])) = [Rules.Region] ENDIF AND
THEN [Rules.Segment] ELSE Null() ENDIF