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

[moemerryman]

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.

Customer	Order ID	Product	New Column
A	5678	Apples	Apples
A	5678	Apples	Apples
B	9123	Oranges	Oranges
B	9123	Oranges	Oranges
C	4567	Apples	Mix
C	4567	Oranges	Mix
D	7891	Apples	Mix
D	7891	Apples	Mix
D	7891	Bananas	Mix

[rfoster7]

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]

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]