Contains against an input list?

[jrobiso2]

I have an analysis which at this point has about 200,000 rows.  This list, due to merging, has 3 columns where an IP address can be found.

I have a separate text list of subnets.  I can't figure out how to:

Create a new column, labeled "ISOLATED_SUBNETS" and do what amounts to an 'If Contains([THIS_IP],"any address from the list of subnets") THEN 'TRUE' ELSE 'FALSE' ENDIF

It's a long list of subnets so to do this in a formula, with 3 columns to test, would essentially give me a formula box with THOUSANDS of "Contains" tests.

There's GOT to be a better way!

[DataBlender · Accepted solution]

Hi @jrobiso2

The Find Replace tool could be used to find 'any part of field' using a separate input as a list.

You would either have to do this three times against each column, or you could transpose the data first so that you're only doing the lookup against one column and then cross-tab it back into its original state after you've identified your matches