Wednesday, 15 September 2010

Merge multiple rows based on column and sum values (Excel, Google Refine, Google spreadsheet) -



Merge multiple rows based on column and sum values (Excel, Google Refine, Google spreadsheet) -

i have big amount of rows, like:

| name | value | |--------|-------| | name 1 | 12 | | name 1 | 10 | | name 1 | 1 | | name 2 | 55 | | name 3 | 1 | | name 3 | 8 |

i need merge rows 1 row based on column "name" , sum "value" in relevant rows. result should be:

| name | value | |--------|-------| | name 1 | 23 | | name 2 | 55 | | name 3 | 9 |

rows same "name" 0-n.

how can in google refine or in excel/google spreadsheet?

i thinking of it, no solution.

thank lot!

in openrefine or google refine:

sort name column (if not sorted) , create sort permanent blank downwards on name column remove duplicate values on value column, edit cells -> merge multi-valued cells on same column, edit cells -> transform grel look of foreach(value.split(','),v,v.tonumber()).sum() facet blank on name column, , select true (ie blank rows) use -> edit rows -> remove matching rows delete redundant rows

excel spreadsheet openrefine

No comments:

Post a Comment