Combining all rows in two columns using Join and skipping the empty rows

Hi Martin! This works well! I've tried using filter but ending up in errors, my filter syntax apparently wrong that time, Anyway do u have information on where I could learn more about advance Filter function in Google Sheets? Many tutorials / documentation online not provide me as much, even I did not know I can use Filter function syntax as you stated above. Thanks martin, nice day to you!

I'm glad it was useful! Honestly, I've learned everything here, watching how others apply FILTER. The main principles you need to know are two: 1) you can work only one direction at a time: filter by rows or filter by columns. If you need to do it with both, nest two FILTER. 2) Every condition you need to set is basically a bunch of 1s and 0s (or TRUE and FALSE). So what you always need is to get there: to a matching size array of 1s and 0s. For example: =FILTER(A1:E10,{1,0,1,0,1}) will filter columns A, C and E

With these concepts, you can use BYROW, MAP, or even other functions with the help of INDEX or ARRAYFORMULA. Easy case: =FILTER(A2:E,A2:A<>""), even with other column not included in range: =FILTER(A2:E,Z2:Z<>""). With BYROW: filtering rows that sum more than 20 between columns C and F: =FILTER(A2:M,BYROW(C2:F,LAMBDA(r,SUM(r)>=20))). Other option with MATCH: You want to filter depending on a name in B being found in I2;I20: =FILTER(A2:C,INDEX(IFNA(MATCH(B2:B,I2:I20,0 ))>0)) and so on. Hope it's helpful as lenses to read or find other true tutorials (not like this, he)

Hi Harun! Tahnks for yout answers! Buat apparently I try martin answer below and it works well. I will learn more about BYROW Function and LAMBDA, Thanks!

@Idad Yes, Martin's answer is good enough but array formulas take more time to process. Rather than FILTER() function QUERY() will be faster.

Agree with @Harun24hr - I used FILTER because I was not sure if A column would have consecutive values, or if there would be only one type of data per column (which is the main limitation with QUERY)