Using "Colon" - Formula Over Multple Fields of Data

Question

I started building out this formulas in the Formula Tool and was thinking - there has to be a better and simpler way to do this.

This is my attempt of trying to simplify the formula using the colon:

IF [Significant Assets (Y/N)] : [CFO Tier 3 - Relies on affil. Bank funding]= "" Then "Y" Else "" Endif

There are 29 columns between Significant Assets (Y/N) and CFO Tier 3. Do I have to list each column out?

Thanks in advance..

Qiu · Answer

@lisamccullough

To prove the point of @jdunkerley79 , you can refer to my another answer to a similar question of this.

Hope it helps.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Identify-comparable-entries-in-two-datasets/m-p/669799#M156729

jdunkerley79 · Answer

A way of doing it would be to:

- Add record ID

- transpose the data to be row based

- count the blanks in the columns you care about using a filter and summarise

- Join back to original data so you have the count and then you have the Y

The formula tool doesn't have an easy way to operate on large numbers of columns.