Compare multiple rows in one column, create new column with results

Question

Hello Community -

I am trying to compare multiple rows in a table. Using the below as an Example, Customer A's Order has two matching line items. I want the New Column to write what that is. For reference, in my real file, there are 4 possible "Products" a customer may have. If they are the same, I need the output to say what that is. If they are different, I need it to say "Mix". The Customer order can be one line, two, or more, and are not necessarily together.  I can presort so they are on top of each other if that makes a difference. In the below example, they are sorted to be together. There are also many more columns I need to retain so keep that in mind as well. These are simply the important columns. I was thinking that I need to I filter out into separate files using the Product column, then joining them all so all the lines for one customer are in one row, then do a formula to compare Input_1_Product, Input_2_Product, etc but then I'd have un-join and put them all back into the original file format in the end with the new column.

Thank you for your suggestions.

CustomerOrder IDProductNew ColumnA5678ApplesApplesA5678ApplesApplesB9123OrangesOrangesB9123OrangesOrangesC4567ApplesMixC4567OrangesMixD7891ApplesMixD7891ApplesMixD7891BananasMix

rfoster7 · Accepted Answer

Just use the Group by tool with a Count Distinct Product to separate the unique orders from the non unique orders, then write "mix" for the non unique orders and the original product for the unique orders, then join it back to the original data.

Probably a more elegant solution, using multirow formulas, but this will get you there.

acotta17 · Accepted Answer

Hi @moemerryman,

I agree with @rfoster7, utilizing the Summarize tool to count along with the Formula Tool is an effective way to tag the values in the New Column.

If you need to add more columns, consider creating a primary key. You can then follow the same steps to achieve your desired output.

[Screenshot attached below]