How to combine values in columns with AND

Hi,

I have 4 filter logic formulas. And now I want to combine them into a new column to say "this AND this"

Expected Output would be

If more then 2 results exist in a row, then use AND in a new column. Ie "Met Action Plan AND Met Current Resoultion" but if row is blank leave blank , or if row contains 1 value leave the one value.

Thanks.

Combine Values into 1, Use ANDyxmd.yxzp

Custom Tools

Accepted answers

SPetrie

If you use the suggestion by @Prometheus you can use a record ID, join, and union tool to get back to the original full data set with the new column.

You can also use a similar approach with a dynamic find and replace if you dont want a join/union.

Combine Values into 1, Use ANDyxmd.yxmd

CoG

Here is a hideous, but effective formula that I believe accomplishes what you requested!

Replace(
 Replace( 
  Replace(
   Trim([Filter 1] + "," + [Filter 2] + "," + [Filter 3] + "," + [Filter 4],","),
   ",,,",
   " AND "
  ),
  ",,",
  " AND "
 ),
 ",",
 " AND "
)

I think the macro would be the best way to go, but if you really want a formula. This should work for the problem you specified.

Combine Values into 1, Use AND.yxmd

All comments

rzdodson

@wonka1234 here is one way to do it with an iterative macro approach.

Solution.yxzp

Prometheus

@wonka1234 For this use case you can use a Summarize tool instead of a Formula tool. Select the fields you want to affect (in this case, all of them), then click on Add to get the drop down menu. From there, hover over String and you'll see Concatenate. Click on that. At the bottom of the Configuration Window, you'll see Concatenate Properties. Change the comma to " AND " without the quotes. Just make sure you have the space before AND and the space after it.

Concatenate all fields.PNG

Combine Values into 1, Use ANDyxmd.yxmd

wonka1234

@Prometheus but need to join it back to the other records! need the fulll output

SPetrie

If you use the suggestion by @Prometheus you can use a record ID, join, and union tool to get back to the original full data set with the new column.

You can also use a similar approach with a dynamic find and replace if you dont want a join/union.

Combine Values into 1, Use ANDyxmd.yxmd

CoG

Here is a hideous, but effective formula that I believe accomplishes what you requested!

Replace(
 Replace( 
  Replace(
   Trim([Filter 1] + "," + [Filter 2] + "," + [Filter 3] + "," + [Filter 4],","),
   ",,,",
   " AND "
  ),
  ",,",
  " AND "
 ),
 ",",
 " AND "
)

I think the macro would be the best way to go, but if you really want a formula. This should work for the problem you specified.

Combine Values into 1, Use AND.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703