perform lookup on two arrays and add element from second array to first array

Question

I have two arrays that I have looped over to match an element from the 1st array vals1 in the second onevals2. If the element(index [i][0]) from vals1 is present in vals2(index [j][0]), I need to grad the element (index[j][1]) from vals2 and push to vals1 (or create a new array with all elements of vals1 and the one from vals2)If it isn't present, I want to assign 'Unassigned' where I would have used element[j][1] from ```vals2``

So:
If vals1 = [[ 'item3', 3, 2, 6 ],[ 'item6', 4, 7, 28 ],[ 'item8', 1, 8, 8 ],[ 'item2', 6, 12, 72 ]]
and vals2 = [[ 'item1', 15 ],[ 'item2', 4 ],[ 'item3', 1 ],[ 'item4', 2 ]] 
I'm looking for the following result:
[[ 'item3', 3, 2, 6,1 ],[ 'item6', 4, 7, 28,'Unassigned' ],[ 'item8', 1, 8, 8,Unassigned' ],[ 'item2', 6, 12, 72,4 ]]

The script I have so far is:

function lookup () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet1 = ss.getSheetByName('Sheet11');
  var vals1 = sheet1.getRange(2,1,sheet1.getLastRow(),sheet1.getLastColumn()).getValues();

  var sheet2 = ss.getSheetByName('Sheet12');
  var vals2 = sheet2.getRange(2,1,sheet2.getLastRow(),sheet2.getLastColumn()).getValues();

  console.log(vals1);
  [ 
    [ 'item3', 3, 2, 6 ], 
    [ 'item6', 4, 7, 28 ],  
    [ 'item8', 1, 8, 8 ],
    [ 'item2', 6, 12, 72 ],
  ]
  console.log(vals2);
 [ 
  [ 'item1', 15 ],
  [ 'item2', 4 ],
  [ 'item3', 1 ],
  [ 'item4', 2 ]
 ] 

 var result =[]; 

 for(var i=0;i<vals1.length;i++){
   for(var j=0;j<vals2.length;j++){
     var desc = vals1[i][0]
     var qty = vals1[i][1]
     var selling = vals1[i][2]
     var total = vals1[i][3]
     var desc2 = vals2[j][0]
     var cost = vals2[j][1]
     
     
     if(desc === desc2){
       result.push([desc,qty,selling,total,cost]) 
     } else {
       result.push([desc,qty,selling,total,'Unassigned'])
     }

   }
 } 
 console.log(result)
}

And the result I'm getting is:

[ [ 'item3', 3, 2, 6, 'Unassigned' ],
  [ 'item3', 3, 2, 6, 'Unassigned' ],
  [ 'item3', 3, 2, 6, 1 ],
  [ 'item3', 3, 2, 6, 'Unassigned' ],
  [ 'item3', 3, 2, 6, 'Unassigned' ],
  [ 'item3', 3, 2, 6, 'Unassigned' ],
  [ 'item2', 4, 7, 28, 'Unassigned' ],
  [ 'item2', 4, 7, 28, 4 ],
  [ 'item2', 4, 7, 28, 'Unassigned' ],
  [ 'item2', 4, 7, 28, 'Unassigned' ],
  [ 'item2', 4, 7, 28, 'Unassigned' ],
  [ 'item2', 4, 7, 28, 'Unassigned' ],
  [ 'item4', 1, 8, 8, 'Unassigned' ],
  [ 'item4', 1, 8, 8, 'Unassigned' ],
  [ 'item4', 1, 8, 8, 'Unassigned' ],
  [ 'item4', 1, 8, 8, 2 ],
  [ 'item4', 1, 8, 8, 'Unassigned' ],
  [ 'item4', 1, 8, 8, 'Unassigned' ],
  [ 'item2', 6, 12, 72, 'Unassigned' ],
  [ 'item2', 6, 12, 72, 4 ],
  [ 'item2', 6, 12, 72, 'Unassigned' ],
  [ 'item2', 6, 12, 72, 'Unassigned' ],
  [ 'item2', 6, 12, 72, 'Unassigned' ],
  [ 'item2', 6, 12, 72, 'Unassigned' ],
  [ 'item4', 3, 34, 102, 'Unassigned' ],
  [ 'item4', 3, 34, 102, 'Unassigned' ],
  [ 'item4', 3, 34, 102, 'Unassigned' ],
  [ 'item4', 3, 34, 102, 2 ],
  [ 'item4', 3, 34, 102, 'Unassigned' ],
  [ 'item4', 3, 34, 102, 'Unassigned' ],
  [ 'item3', 2, 8, 16, 'Unassigned' ],
  [ 'item3', 2, 8, 16, 'Unassigned' ],
  [ 'item3', 2, 8, 16, 1 ],
  [ 'item3', 2, 8, 16, 'Unassigned' ],
  [ 'item3', 2, 8, 16, 'Unassigned' ],
  [ 'item3', 2, 8, 16, 'Unassigned' ],
  [ 'item1', 8, 32, 256, 15 ],
  [ 'item1', 8, 32, 256, 'Unassigned' ],
  [ 'item1', 8, 32, 256, 'Unassigned' ],
  [ 'item1', 8, 32, 256, 'Unassigned' ],
  [ 'item1', 8, 32, 256, 'Unassigned' ],
  [ 'item1', 8, 32, 256, 'Unassigned' ],
  [ 'item1', 21, 32, 672, 15 ],
  [ 'item1', 21, 32, 672, 'Unassigned' ],
  [ 'item1', 21, 32, 672, 'Unassigned' ],
  [ 'item1', 21, 32, 672, 'Unassigned' ],
  [ 'item1', 21, 32, 672, 'Unassigned' ],
  [ 'item1', 21, 32, 672, 'Unassigned' ],
  [ 'item2', 2, 7, 14, 'Unassigned' ],
  [ 'item2', 2, 7, 14, 4 ],
  [ 'item2', 2, 7, 14, 'Unassigned' ],
  [ 'item2', 2, 7, 14, 'Unassigned' ],
  [ 'item2', 2, 7, 14, 'Unassigned' ],
  [ 'item2', 2, 7, 14, 'Unassigned' ],
  [ 'item5', 4, 6, 24, 'Unassigned' ],
  [ 'item5', 4, 6, 24, 'Unassigned' ],
  [ 'item5', 4, 6, 24, 'Unassigned' ],
  [ 'item5', 4, 6, 24, 'Unassigned' ],
  [ 'item5', 4, 6, 24, 4 ],
  [ 'item5', 4, 6, 24, 'Unassigned' ],
  [ 'item8', 9, 8, 72, 'Unassigned' ],
  [ 'item8', 9, 8, 72, 'Unassigned' ],
  [ 'item8', 9, 8, 72, 'Unassigned' ],
  [ 'item8', 9, 8, 72, 'Unassigned' ],
  [ 'item8', 9, 8, 72, 'Unassigned' ],
  [ 'item8', 9, 8, 72, 'Unassigned' ],
  [ 'item3', 16, 2, 32, 'Unassigned' ],
  [ 'item3', 16, 2, 32, 'Unassigned' ],
  [ 'item3', 16, 2, 32, 1 ],
  [ 'item3', 16, 2, 32, 'Unassigned' ],
  [ 'item3', 16, 2, 32, 'Unassigned' ],
  [ 'item3', 16, 2, 32, 'Unassigned' ],
  [ 'item7', 32, 6, 192, 'Unassigned' ],
  [ 'item7', 32, 6, 192, 'Unassigned' ],
  [ 'item7', 32, 6, 192, 'Unassigned' ],
  [ 'item7', 32, 6, 192, 'Unassigned' ],
  [ 'item7', 32, 6, 192, 'Unassigned' ],
  [ 'item7', 32, 6, 192, 'Unassigned' ],
  [ 'item6', 1, 21, 21, 'Unassigned' ],
  [ 'item6', 1, 21, 21, 'Unassigned' ],
  [ 'item6', 1, 21, 21, 'Unassigned' ],
  [ 'item6', 1, 21, 21, 'Unassigned' ],
  [ 'item6', 1, 21, 21, 'Unassigned' ],
  [ 'item6', 1, 21, 21, 'Unassigned' ],

The data sets im working from has 1701 rows and 228 rows respectivly. When I originally ran the script it printed over 400 000 rows of data.

Any help would be greatly appreciated

Iamblichus · Accepted Answer · 2021-03-02 14:54:56Z

1

Iterate through all items in the first 2D array (vals1). You can do that with forEach, for example.
For each item in vals1, you want to look for the same item in vals2. You can use find for that. Since items are the first element in each inner array, you would have to check whether item1[0] === item2[0].
If the item is found, push the corresponding value item2[1] to the item's inner array in vals1.
If the item is not found (find returns undefined), push the string "Unassigned" instead.

Code snippet:

function lookup(vals1, vals2) {
  vals1.forEach(item1 => {
    const item2 = vals2.find(item2 => item1[0] === item2[0]);
    const newValue = item2 ? item2[1] : "Unassigned";
    item1.push(newValue);
  });
  return vals1;
}

Regular function syntax:

function lookup(vals1, vals2) {
  vals1.forEach(function(item1) {
    const item2 = vals2.find(function(item2) {
      return item1[0] === item2[0]
    });
    const newValue = item2 ? item2[1] : "Unassigned";
    item1.push(newValue);
  });
  return vals1;
}

edited Mar 2, 2021 at 14:54

answered Feb 9, 2021 at 9:07

Iamblichus

19.5k2 gold badges14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Chris Ward Over a year ago

Thanks for your responce @lamblichus. I'll definitly apply this solution. Will let you know how i got on, but this makes total sence too.

Chris Ward Over a year ago

@lamblichus. So I implemented your lookup method on a separate project I'm working with and it definitely does the job. If I'm honest, I'll probably be using this method going forward purely because I understand it better. Thanks again for your response.

Chris Ward Over a year ago

@lamblichus, would it be possible for you to write the above using the regular function syntax? Im not great at understanding or adapting arrow functions. Id like to use the method you suggested in a separate use case but am struggling a bit with the arrow function syntax.

Iamblichus Over a year ago

@ChrisWard I'm sorry I missed your last comment. I added a code snippet with classical function syntax, I hope this is helpful.

Chris Ward Over a year ago

No problem at all @lamblichus. Thanks for including the classical syntax snippet, it will definitly help. This lookup methodology has become really handy for me.

Cooper · Accepted Answer · 2021-02-09 08:13:12Z

0

Try this:

function myFunc( ) {
  let v1 = [[ 'item3', 3, 2, 6 ],[ 'item6', 4, 7, 28 ],[ 'item8', 1, 8, 8 ],[ 'item2', 6, 12, 72 ]];
  let v2 = [[ 'item1', 15 ],[ 'item2', 4 ],[ 'item3', 1 ],[ 'item4', 2 ]]; 
  let v0 =v2.map(r=>{return r[0]});//just getting all of the the first elements of v2 in a flatten array for  use with indexOf
  v1.forEach((r,i)=>{
    let idx=v0.indexOf(r[0]); 
    if(idx>-1) {//if idx is greater than -1 the matches first element v2
      v1[i].push(v2[idx][1]);
    }else{
      v1[i].push('UnAssigned')
    }
  });
  console.log(v1);
}

The output was:

[ [ 'item3', 3, 2, 6, 1 ],
  [ 'item6', 4, 7, 28, 'UnAssigned' ],
  [ 'item8', 1, 8, 8, 'UnAssigned' ],
  [ 'item2', 6, 12, 72, 4 ] ]

edited Feb 9, 2021 at 8:13

answered Feb 8, 2021 at 23:12

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

2 Comments

Chris Ward Over a year ago

Legend @Cooper. That works exactly as I wanted. Thanks for the help. I have two question for you if I may? Could you perhaps briefly explain how this works? I'm still pretty new at this. I'm familiar with the .map and .indexOf methods but have only used them with the older syntax's. Thanks again for your help, this is going to save me tons of time.

Chris Ward Over a year ago

My bad. I see your comments in your answer now. I got it. Makes sense.

Collectives™ on Stack Overflow

perform lookup on two arrays and add element from second array to first array

2 Answers 2

Code snippet:

Regular function syntax:

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Code snippet:

Regular function syntax:

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related