How to write a condition if columns numbers are not fixed

Question

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 :

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

Can anyone assist on this.

Sample data 1.xlsx

Saravanan13 · Answer

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.

Sample data 1.xlsx

Sample data 2.xlsx

JamesCharnley · Answer

@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.

transposeconcatenate.yxzp

Emmanuel_G · Answer

@Saravanan13

I don't get the point sorry.

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