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 offoreach(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