I am just wondering if there is a formula in which I can combine multiple columns into single cell with sublist on each. I have already attached a sample sheet with sample output below
https://docs.google.com/spreadsheets/d/1MB4aGpwvz_okI1A_yjqPahK1gqwk_01euV7KPAdYuBM/edit?usp=sharing
CodePudding user response：
Answer
The following formula should produce the result you desire:
=JOIN(CHAR(10),ARRAYFORMULA(SUBSTITUTE(CONCAT(FILTER(A2:A500,A2:A500<>""),CHAR(10)&CHAR(9)&SUBSTITUTE(FILTER(B2:B500,A2:A500<>""),CHAR(10),CHAR(10)&CHAR(9))),CHAR(10)&CHAR(9)&"no sublist","")))
Explanation
The formula's innermost SUBSTITUTE replaces each line break in column B with a line break and a tab. CONCAT then combines that with each result in column A. Note that due to the FILTER functions, for both column A and column B, only rows where there is a value in column A will be included.
The outermost SUBSTITUTE gets rid of any entries which are no sublist. Everything so far is wrapped in ARRAYFORMULA so that CONCAT and SUBSTITUTE work properly with ranges instead of single cells.
Finally, each row is joined together with a line break using the outermost JOIN.
Functions used: