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.
Appreciate all the assistance here.
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"))))