Home:ALL Converter>Filter Array by Another Array Compare the Rows for Column A and C Matches, Keep only Non-Matches Google Apps Script [Updated]

Filter Array by Another Array Compare the Rows for Column A and C Matches, Keep only Non-Matches Google Apps Script [Updated]

Ask Time:2021-03-13T08:47:29         Author:cadmiumblue

Json Formatter

*Edit, I was able to return the opposite of what I wanted, code below. Updated with what else I've tried. I rewrote a self-contained version with the test data included so you can test/see exactly what the Logger results are yourself.

I want to create a function which compares two arrays row by row, bLFinal and matchFinal. If there is a row in bLFinal which has the same column A and column C values as a row in matchFinal, I want to delete it from bLfinal. So then all the ones with no match are the only ones left.

Here's an example of how I want the code to work. In the below example, the array to be filtered (bLFinal) is 11 rows long, and the resultant version of bLFinal should be length of 7 rows.

When any row from bLFinal matches any row in matchFinal, only comparing both arrays' A and C values (for example "penny" and "@FOX"), it removes the matched row from bLFinal.

In my actual use case, I will not necessarily know what exactly is in Col A or Col C. So I am not able to just add them to a list in the code to filter.

//bLFinal array before filtering
[
["penny", "Up", "@FOX", "45"],
["shanice", "Up", "@FOX", "45"],
["barbara", "Down", "@BEAR", "22"],
["darell", "Down", "@SHARK", "10"],
["penny", "Up", "@BUNNY", "20"],
["jules", "Up", "@FOX", "45"],
["macy", "Up", "@FOX", "45"],
["terry", "Down", "@BEAR", "22"],
["shanice", "Down", "@SHARK", "10"],
["shanice", "Up", "@BEAR", "22"],
["kelly", "Up", "@BUNNY", "20"]
]
//matchFinal array to use to check bLFinal against

[
["george", "Down", "@FOX", "45"],
["shanice", "Up", "@FOX", "45"],
["barbara", "Up", "@BEAR", "22"],
["darell", "Up", "@SHARK", "10"],
["penny", "Up", "@BUNNY", "20"],
["carol", "Down", "@LIZARD", "70"],
["bernard", "Up", "@TOAD", "85"],
["bobby", "Up", "@BUNNY", "20"]
]
//Then, the resultant version of bLFinal should look like this:

[
["penny", "Up", "@FOX", "45"],
["jules", "Up", "@FOX", "45"],
["macy", "Up", "@FOX", "45"],
["terry", "Down", "@BEAR", "22"],
["shanice", "Down", "@SHARK", "10"],
["shanice", "Up", "@BEAR", "22"],
["kelly", "Up", "@BUNNY", "20"]
]

In my above of how the arrays should work, the following lines are removed/not included in the final bLFinal, because they have the same A and C column as at least one of matchFinal's row's col A and C. (If col B or D differs, there is no problem, I only want to use for example "shanice" and "@FOX", col A and C as test criteria).

["shanice", "Up", "@FOX", "45"],
["barbara", "Down", "@BEAR", "22"],
["darell", "Down", "@SHARK", "10"],
["penny", "Up", "@BUNNY", "20"],

So namely, because rows with A col and C col values "shanice" and "@FOX", "barbara" and "@BEAR", "darell" and "@SHARK" , and finally "penny" and "@BUNNY" were found in matchFinal in its rows, they should be removed from bLFinal. I want to compare any/all rows in bLFinal to look for A and C matches in any row in matchFinal.


function bLow(){

var matchArray = [["george", "Down", "@FOX", "45"],
["shanice", "Up", "@FOX", "45"],
["barbara", "Up", "@BEAR", "22"],
["darell", "Up", "@SHARK", "10"],
["penny", "Up", "@BUNNY", "20"],
["carol", "Down", "@LIZARD", "70"],
["bernard", "Up", "@TOAD", "85"],
["bobby", "Up", "@BUNNY", "20"]];

var matchFinal = matchArray;
var matchLRow = matchArray.length;

Logger.log(matchArray.length);

var bLArray = [
["penny", "Up", "@FOX", "45"],
["shanice", "Up", "@FOX", "45"],
["barbara", "Down", "@BEAR", "22"],
["darell", "Down", "@SHARK", "10"],
["penny", "Up", "@BUNNY", "20"],
["jules", "Up", "@FOX", "45"],
["macy", "Up", "@FOX", "45"],
["terry", "Down", "@BEAR", "22"],
["shanice", "Down", "@SHARK", "10"],
["shanice", "Up", "@BEAR", "22"],
["kelly", "Up", "@BUNNY", "20"]
];

var bLFinal = bLArray;
var bLLrow = bLArray.length;

Logger.log(bLArray.length);

// This is where I'm having trouble. 
// It returns the same values for both before and after filtering of bLFinal which means it is not working.
// However, I want it to remove the 4 matching lines.

bLFinal.forEach(function(row, index){
 for (i=0 ; i<bLLrow ; i++){
   for (j=0 ; j<matchLRow ; j++){
  if (index !== 0){
    if(row[i][0] !== matchFinal[j][0] && row[i][2] !== matchFinal[j][2]){
      return;
    }
  }
   }
 } 
});
Logger.log(bLFinal.length); 
}

As an update, I was able to return the duplicates themselves using the following code

for (i=0 ; i<bLLrow ; i++){
   for (j=0 ; j<matchLRow ; j++){
     if(bLFinal[i][0] == matchFinal[j][0] && bLFinal[i][2] == matchFinal[j][2]){
      filteredBLArray.push(bLFinal[i]);
   } 
 }
 }

I'm now trying to return the opposite, the non-duplicates.

Author:cadmiumblue,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/66609018/filter-array-by-another-array-compare-the-rows-for-column-a-and-c-matches-keep
yy