Grouping a Array by the values of a specific column-CodePudding

i have a problem regarding grouping a Array by the values of a specific column which i get from a Google Sheet with .getRange().getValues().

Example Array:

   var Arr = [
    [Mat. Group 1, xyz, name xyz, -31.0, -610],
    [Mat. Group 2, abc, name abc, -12.0, -434], 
    [Mat. Group 1, gew, name gew, -44.0, -234], 
    [Mat. Group 1, zrw, name zrw, -22.0, -214], 
    [Mat. Group 2, dbh, name dbh, -91.0, -193], 
    [Mat. Group 3, dse, name dse, -282.0, -176], 
    [Mat. Group 4, dwq, name dwq, -235.0, -151], 
    [Mat. Group 3, dge, name dge, -45.0, -150]
    ]

I want to group the values in column 4 (Net Price) by the Material Group in column 0. (col1 = Reference-Number, col 2 = Name of Reference, col 3 = Quantity)

So the result should look like this:

[
[Mat. Group 1, -1058],
[Mat. Group 2, -627],
[Mat. Group 3, -326],
[Mat. Group 4, -151],
]

There are a few solutions how to do this with an array of objects (Like these post: https://stackoverflow.com/a/57477448/15361059 ) by using array formulars.

I am new in array formulars. I tried to modify the solutions, but always crashed. So maybe you could support me with this issue.

Thanks in advance.

CodePudding user response：

A reduce would work well here

var Arr = [
  ["Mat. Group 1", "xyz", "name xyz", -31.0, -610],
  ["Mat. Group 2", "abc", "name abc", -12.0, -434],
  ["Mat. Group 1", "gew", "name gew", -44.0, -234],
  ["Mat. Group 1", "zrw", "name zrw", -22.0, -214],
  ["Mat. Group 2", "dbh", "name dbh", -91.0, -193],
  ["Mat. Group 3", "dse", "name dse", -282.0, -176],
  ["Mat. Group 4", "dwq", "name dwq", -235.0, -151],
  ["Mat. Group 3", "dge", "name dge", -45.0, -150]
]

const newArr = Arr.reduce((acc, cur) => {
  const idx = acc.findIndex(arr => arr[0] === cur[0]);
  if (idx != -1) acc[idx][1]  = cur[4];
  else acc.push([cur[0], cur[4]]);

  return acc
}, []);
console.log(newArr)

CodePudding user response：

You can get the expected result with grouping by key approach

const data = [["Mat. Group 1", "xyz", "name xyz", -31.0, -610],["Mat. Group 2", "abc", "name abc", -12.0, -434],["Mat. Group 1", "gew", "name gew", -44.0, -234],["Mat. Group 1", "zrw", "name zrw", -22.0, -214],["Mat. Group 2", "dbh", "name dbh", -91.0, -193],["Mat. Group 3", "dse", "name dse", -282.0, -176],["Mat. Group 4", "dwq", "name dwq", -235.0, -151],["Mat. Group 3", "dge", "name dge", -45.0, -150]]

const result = Object.values(data.reduce((acc, [group,,,, value]) => 
  ({ ...acc, [group]: [group, (acc[group] ?? [0, 0])[1]   value] }), {}));

console.log(result)

.as-console-wrapper{min-height: 100%!important; top: 0}