How to write a condition if columns numbers are not fixed

[Saravanan13]

Hi,

I have a complicated scenario where the Cust ID column is not fixed and it depends on the input file. Cust ID column can be either one, two or three.(always less than three)

Sheet 2 is a complete example if there are two Cust IDs. I can easily write a formula for result column if number of Cust ID column is fixed. Since the Cust ID column is not fixed, Is there a way to write one formula for all the three scenarios.

Note :

1. Based on the combinations in Join column, the result column will code it as PASS or FAIL
2. Combinations will differ according to the number of  Cust ID column.

Can anyone assist on this.

[Emmanuel_G]

@Saravanan13 

In this specific case, the solution is generally to use transpose tool. It allows to your workflow to be dynamic. So even if there is a new field to evaluate for concluding FAIL or PASS, it will be added automatically.

Find in attachement the way of doing that.

[Saravanan13]

Thank you so much for the reply. But have another issue.

The attached file is not the input file and I have created a workflow to derive that output.

Also formula for Join column = Cust ID1 + Cust ID2 + Cust ID3

There are some fixed set of rules and based on the combination, Results will be decided as PASS or FAIL

For example

If the Cust ID is one then the Combination will be  LJ,LS,NJ................

If the Cust ID is two then the Combination will be  LJLS,LSLJ,NSNJ,PSNJ.................

If the Cust ID is three then the Combination will be  LJLSLS,LJNSNJ,PSNJLS.................

Rules examples -

Combination LJ = Pass, LS = Pass, NJ = Fail

Combination LJLS = Pass, LSLJ = Pass, NSNJ = Fail, PSNJ = Fail

Combination LJLSLS = Pass, LJNSNJ = Fail, PSNJLS = Fail

I can take care of adding formulas for the above rules. Just need the Join column to be dynamic, which means the combinations should be accurate even if the cust ID columns change

[Emmanuel_G]

@Saravanan13 

I don't get the point sorry.

Can you please provide another data sample with input and desired output please ?

[JamesCharnley]

@Saravanan13  I could be misinterpreting but an alternative to the formula in this position would be to use the 'Concatenate' function in the summarize tool with no separator after it's been transposed, which should achieve the same result but isn't 'hard-coded' as such to a certain number of fields.

[Saravanan13]

Apologies, I have attached a new sample sheet.

You can forget about the result column.

In my original workflow 

1ST INPUT (SAMPLE DATA 1)- account list and other columns to derive power details.(LU,LS)

2ND Input (SAMPLE DATA 1) - Cust ID details(cust ID can be 1 or 2 or 3)

Then I used a append tool to create rows for each account and cust ID. After that my dataset will look like the input file(Sample data1) attached

Since I am sure about the customer ID details, I need concatenated values  in Join column.