How to summarize group by advanced

[Dittau]

If i have 4 columns of data and i want 1 column to be summed only if the other 3 columns match- how do i do that in the summarie tool or do i need another to to help?

ex: column a, b, see are all the same so just need 1 row returned with those items and column d summed

column a     Column b    Column c     column d

ham               cake.             Soda           4

ham.              Cake.            Soda.          8

[alexnajm]

You’ll need a Data Cleansing tool first to make all of the values into the same case (for example, lowercase), and then the Summarize tool to group by the first three fields and sum by the fourth field!

[alexnajm]

They would end up on different rows if the case isn’t dealt with because the Summarize is case sensitive. 

[Dittau]

Lets say i had 10 columns, wanted to group by 3 and sum 1 but still want the other 6 columns to show- how would i accomplish that?

[alexnajm]

They would also need to be grouped by (or some sort of aggregation like a concatenate) in order to come through the Summarize tool. Otherwise, they could be joined in somehow, appended in, etc but it'll highly depend on the use case

[Dittau]

Do you have a screenshot of how that may look. If you do 3 columns with (zipcode, city, state) and 6 columns with food item and 1 column with price…group by  zipcode city and state and sum the price but also show the other 6 columns as well

[alexnajm · Accepted solution]

I dont - if you provide the data and expected output then I can try to help but otherwise based on the explanation it is group by the 9 columns and sum by the price