Formula help needed

[Brad1]

I'm trying to do and if/then statement with two conditions.  Can this be done?  The first condition is the field length.  It has to be 10 digits to be a valid code.

Second, it can't be the same code as the code in the calculated field to the left from this field I'm working on.

First field with no issues

Field Name:  First_Code_Found

If Length ([Tax_Code1]) >9 then [Tax_Code1] elseif Length ([Tax_Code2] >9 then [Tax_Code2] (etc., etc.) else '' endif

Second field needs to be the correct length in characters AND also not match the field above - First_Code_Found.

Field Name:  Second_Tax_Code_Found

If Length ([Tax_Code1]) >9 and [Tax_Code1] != [First_Code_Found] then [Tax_Code1] elseif Length ([Tax_Code2] >9 and [Tax_Code2] != [First_Code_Found] then [Tax_Code2] (etc., etc.) else '' endif

This second field isn't working - any advice?  Thanks for your help in advance.

[ivoller]

It looks valid. What is the error message (if any)? Can you share with sample data?

[harikakummara]

As @ivoller said condition looks valid. But "Second_Tax_Code_Found" :works only when the last ELSEIF statement column has some values left whose length is more than 9 digits.

for example:

Tax_Code1	Tax_Code2
12345678910	1234
12345678910	12345678911

Result:

Tax_Code1	Tax_Code2	First_Code_Found	Second_Code_Found
12345678910	1234	12345678910	0
12345678910	12345678911	12345678910	12345678911

As you see for the first line the "Secnod_Code_Found" column is 0 as the Tax_Code1 > 9 and is Equal to First_Code_Found column value. But for the second row, data populated as the conditions are satisfied.

Let me know if this clear your question. For more clarification please provide the entire formula to have a look at it.

Thanks,

Harika

[Brad1]

Ok, here is formula 1 (higher up in the formula tool)

if Length([TAXONOMY_CODE_00]) > 9 then [TAXONOMY_CODE_00] elseif Length([TAXONOMY_CODE_01]) > 9 then [TAXONOMY_CODE_01] elseif Length([TAXONOMY_CODE_02]) > 9 then [TAXONOMY_CODE_02] elseif Length([TAXONOMY_CODE_03]) > 9 then [TAXONOMY_CODE_03] elseif Length([TAXONOMY_CODE_04]) > 9 then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_05]) > 9 then [TAXONOMY_CODE_05] elseif Length([TAXONOMY_CODE_06]) > 9 then [TAXONOMY_CODE_06] elseif Length([TAXONOMY_CODE_07]) > 9 then [TAXONOMY_CODE_07] elseif Length([TAXONOMY_CODE_08]) > 9 then [TAXONOMY_CODE_08] elseif Length([TAXONOMY_CODE_09]) > 9 then [TAXONOMY_CODE_09] elseif Length([TAXONOMY_CODE_10]) > 9 then [TAXONOMY_CODE_10] elseif Length([TAXONOMY_CODE_11]) > 9 then [TAXONOMY_CODE_11] elseif Length([TAXONOMY_CODE_12]) > 9 then [TAXONOMY_CODE_12] elseif Length([TAXONOMY_CODE_13]) > 9 then [TAXONOMY_CODE_13] elseif Length([TAXONOMY_CODE_14]) > 9 then [TAXONOMY_CODE_14] elseif Length([TAXONOMY_CODE_15]) > 9 then [TAXONOMY_CODE_15] elseif Length([TAXONOMY_CODE_16]) > 9 then [TAXONOMY_CODE_16] elseif Length([TAXONOMY_CODE_17]) > 9 then [TAXONOMY_CODE_17] elseif Length([TAXONOMY_CODE_18]) > 9 then [TAXONOMY_CODE_18] elseif Length([TAXONOMY_CODE_19]) > 9 then [TAXONOMY_CODE_19] elseif Length([TAXONOMY_CODE_20]) > 9 then [TAXONOMY_CODE_20] elseif Length([TAXONOMY_CODE_21]) > 9 then [TAXONOMY_CODE_21] elseif Length([TAXONOMY_CODE_22]) > 9 then [TAXONOMY_CODE_22] elseif Length([TAXONOMY_CODE_23]) > 9 then [TAXONOMY_CODE_23] elseif Length([TAXONOMY_CODE_24]) > 9 then [TAXONOMY_CODE_24] elseif Length([TAXONOMY_CODE_25]) > 9 then [TAXONOMY_CODE_25] else '' endif

and here is Formula 2 based on the result of above.

IF Length([TAXONOMY_CODE_00]) > 9 and [TAXONOMY_CODE_00] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_00] elseif Length([TAXONOMY_CODE_01]) > 9 and [TAXONOMY_CODE_01] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_01] elseif Length([TAXONOMY_CODE_02]) > 9 and [TAXONOMY_CODE_02] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_02] elseif Length([TAXONOMY_CODE_03]) > 9 and [TAXONOMY_CODE_03] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_03] elseif Length([TAXONOMY_CODE_04]) > 9 and [TAXONOMY_CODE_04] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_05]) > 9 and [TAXONOMY_CODE_05] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_06]) > 9 and [TAXONOMY_CODE_06] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_07]) > 9 and [TAXONOMY_CODE_07] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_08]) > 9 and [TAXONOMY_CODE_08] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_09]) > 9 and [TAXONOMY_CODE_08] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_10]) > 9 and [TAXONOMY_CODE_10] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_11]) > 9 and [TAXONOMY_CODE_11] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_12]) > 9 and [TAXONOMY_CODE_12] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_13]) > 9 and [TAXONOMY_CODE_13] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_14]) > 9 and [TAXONOMY_CODE_24] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_15]) > 9 and [TAXONOMY_CODE_15] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_16]) > 9 and [TAXONOMY_CODE_16] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_17]) > 9 and [TAXONOMY_CODE_17] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_18]) > 9 and [TAXONOMY_CODE_18] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_19]) > 9 and [TAXONOMY_CODE_19] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_20]) > 9 and [TAXONOMY_CODE_20] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_21]) > 9 and [TAXONOMY_CODE_21] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_22]) > 9 and [TAXONOMY_CODE_22] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_23]) > 9 and [TAXONOMY_CODE_23] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_24]) > 9 and [TAXONOMY_CODE_24] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] elseif Length([TAXONOMY_CODE_25]) > 9 and [TAXONOMY_CODE_25] != [Taxonomy Code for primary Specialty] then [TAXONOMY_CODE_04] else '' endif

What seems to be happening is it is giving me the first Tax Code in the second field - even though it's not suppose to.  Spot 00 isn't always filled and neither are any of the given spots.  So I need it to find the first Code and then the second Code in all these fields.

[Brad1]

There are at least 3 valid codes across all these fields for any given record and many have as many as 10 valid codes.

[AdamR_AYX]

That formula is rather complex :) I wonder if you would be better to transpose the data and achieve what you want using filter and summarise tools?

Are you able to share any sample data that we can play with?

[Brad1 · Accepted solution]

Oh, looking at the formula all spread out - I see it's wrong.  All the "4"s need to be different numbers.