30
Jul
Problem description & analysis:An Excel worksheet has multiple columns of source data that contains empty cells, as the following range C3:D19 shows. It also contains data items, which are separated into two parts by “-”, used for comparisons, as range F3:F7 shows. Task: Compare each column of the source data with the first part of the data item, and after each column of data, list all matching data items, as shown below: Solution:Use SPL XLL to do this: =spl("=d=transpose@n(?1),transpose@n(d.(E@1(?2).select(d.~.pos(substr@l(~,$[-])))))",C3:D19,F3:F7) Enter fullscreen mode Exit fullscreen mode As shown in the picture below: Explanation:transpose@n function transposes a sequence. E@1 convers a multilayer…