Create & Populate new column based on values in other columns - using Mult-Field Formula

Question

I want to create a new column based on data in many other columns.  I have been using the formula tool and creating a new output column with the below conditional and it works:

IF  [Column 1]='Yes' AND [Column 2]='Yes' AND [Column 3]='Yes' AND [Column 4]='Yes' AND [Column 5]='Yes' AND [Column 6]='Yes' THEN 'Yes' ELSE 'No' ENDIF

I do this very often with many more columns and I'd like to use the Multi-Field tool for this to get the same result so I can just check the boxes and save the expression rather than manually adding in the column names in the formula tool.

I've been working with the same conditional as a start and with the boxed checked in the configuration box have gotten this far:

IF

([_CurrentField_] = '-')

but I can't bring it all together.   Ii'm sure it's simple but I can't figure it out, can anyone give me a hand with this?  Also if there's another tool I can use besides Multi-Field that I can use to acomplish this in one step, that would also be a welcome response.

DataNath · Accepted Answer

Hey @Rob48, I get the feeling I may gave made this a little long-winded but here's my approach:

1) Assign RecordIDs and then Transpose the data

2) Do a grouped count of RecordIDs to get a count of the number of columns you're evaluating

3) Filter for cells containing 'Yes' so we can do the same and get a count of 'Yes' for each RecordID

4) Join these counts and check whether or not they're equal i.e. is every column 'Yes' or not

5) Assign the Yes/No flag based on the above and then join back to the main data

For this approach, the only thing you'll need to change is which fields are ticked in the 'Data Columns' section of the Transpose configuration - just ensure the fields you want to evaluate are ticked here. Hope this helps!

philipmannering · Accepted Answer

If you're just trying to see if all select columns are "Yes", then you could use this custom macro..