Google Sheet IFS combine with FILTER Function

Question

I have the following Google Seet Table

Old New New2
W01
W02 W04
W03 W05 W06

I want to create a formular that transforms the table to this one

Old  New
W02  W04
W03  W05
W05  W06

So any time a switch from Old to New or New to New2 happens should be displayed.

I wrote the following formular but i always get an error:

= IFS(B1 = "";""; AND(NOT(B1 = ""); NICHT(C1 = ""));FILTER({A1\ B1}; NICHT(A1=""));NICHT(B1 = "");FILTER({B1\ C1}; NICHT(B1="")))

Has anybody an idea?

score 1 · Answer 1 · answered Jan 24 '22 at 16:25

Concatenate the results of two Query calls:

={
  QUERY(A1:B4,
    "select A,B where B<>''");
  QUERY(B1:C4,
    "select B,C where C<>'' label B '', C ''", 1)
}

or in German locale:

={
  QUERY(A1:B4;
    "select A,B where B<>''");
  QUERY(B1:C4;
    "select B,C where C<>'' label B '', C ''"; 1)
}

The label statements in the second query are necessary to suppress the column labels since you want to treat certain columns in New1 as Old.

1 Answers1