google-apps-scriptgoogle-sheetsmatching

Match/Sort values on two different columns on the same sheet


I'm trying to solve out a recurring task that I do every week. What I do is compare sets of ID's in a column (Column A) to another column (Column E) and vice versa. If value in first column is found in second column, the value will move to where the values is matching. If there are no matching data in either of the columns, it will just be blank. All of the matching values will be moved down and all that has no match will be move upwards for checking.

This is the sample data below which is found in "sample" tab:

Column A Column B
399381935 375634552
407932943 399381935
409046041 407932943
448734453 409046041
452821913 431446526
459673358 448734453
463178020 448879624
485888087 452821913
498826033 458359366
498826520 459673358
503189964 463178020
509536058 485888087
509542349 497540933
510595029 498826033
510595505 498826520
510601532 502416642
511701201 502433066
512763252 503189964
515003633 506534952
515008102 509536058
520097636 509542349
510307762
510312887
510320772
510595029
510601532
511701201
512763252
515003633

And hopefully, after running the script, the finished data should have the result shown below where all the matched data are group below and those unique values are pushed above so it'll be easier to check:

Column A Column B
375634552
431446526
448879624
458359366
497540933
502416642
502433066
506534952
510307762
510312887
510320772
510595505
515008102
520097636
399381935 399381935
407932943 407932943
409046041 409046041
448734453 448734453
452821913 452821913
459673358 459673358
463178020 463178020
485888087 485888087
498826033 498826033
498826520 498826520
503189964 503189964
509536058 509536058
509542349 509542349
510595029 510595029
510601532 510601532
511701201 511701201
512763252 512763252
515003633 515003633

Below is the script I used which was suggested by @Cooper. The script works fine if there are just a few data but it doesn't perform a 100% if there are large sets of data being compared.

function sortof() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("sample");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  vs.sort((a, b) => {
    if (a[0] > b[0]) return 1;
    if (b[0] > a[0]) return -1;
    if (a[0] == b[0]) return 0;
  })
  sh.getRange(2, 1, vs.length, vs[0].length).setValues(vs)
  let ob = vs.reduce((a, r, i, A) => {
    let idx = a["col1"].indexOf(r[4]);
    if (~idx) {
      let t = A[idx][4]
      a["col5"][idx] = [r[4]];
      a["col5"][i] = [t];
    } else {
      if (!a["col5"][i]) {
        a["col5"][i] = [r[4]];
      }
    }
    return a;
  }, { col1: vs.map(r => r[0]), col5: [] })
  sh.getRange(2, 5, ob["col5"].length).setValues(ob["col5"]);
  Logger.log(JSON.stringify(ob));
}

@TheMaster, here's the screenshot result of the recommendation change you had on the vs part. I've marked the ones in red the ones the doesn't match and orange and blue the ones that has match values but still pushed upwards, resulting in a duplicate value.

enter image description here

Appreciate all the assistance here.


Solution

  • It's not difficult to accomplish this with a formula so I recommend going this route:

    =ARRAYFORMULA(
       LET(a_,TOCOL(A2:A,3),
           b_,TOCOL(B2:B,3),
           LABEL,LAMBDA(x,LET(s,SEQUENCE(ROWS(x)),TOCOL(x,3)&"ζ"&COUNTIFS(x,x,s,"<="&s))),
           a,LABEL(a_),
           b,LABEL(b_),
           matching,TOCOL(VLOOKUP(a,b,1,0),3),
           unique1,FILTER(a,a<>"",0=COUNTIF(matching,a)),
           unique2,FILTER(b,b<>"",0=COUNTIF(matching,b)),
           s_unique,SORT({unique1;unique2}),
           aligned,VSTACK(
                     {VLOOKUP(s_unique,unique1,1,0),VLOOKUP(s_unique,unique2,1,0)},
                     {matching,matching}),
           IFNA(QUERY(
                  IFERROR(REGEXREPLACE(aligned,"ζ.*",)),
                  "where Col1 is not null or Col2 is not null"))))
    

    enter image description here