Forum Discussion

tonyceccato's avatar
tonyceccato
Icon for Microsoft rankMicrosoft
Jan 23, 2026

Help with a Formula

Below is a formula I created to return specified rows and columns for a sheet named Detail and in the case or column 49 detect if there are comma separated values and explode them into separate colum...
excel
Formulas and Functions
training
IlirU's avatar
IlirU
Iron Contributor
Jan 24, 2026

Hi,

You can try using the formula below, which is based on the same logic you used but avoids the full‑column ranges. In many cases it performs faster and prevents unnecessary recalculation.

=LET(
     data, Detail!A1:BZ20,
       rd, --REDUCE(data, CHAR(160), LAMBDA(a,b, SUBSTITUTE(a, b, ""))),
       ts, TEXTSPLIT(TEXTJOIN(";",, CHOOSECOLS(data, 49)), ",", ";"),
     FILTER(SORT(UNIQUE(HSTACK(CHOOSECOLS(rd, {1,3,4,5,68}),
     IFNA(IF(ISNUMBER(--ts), --ts, ts), ""), CHOOSECOLS(rd, 53)), TRUE)),
     COUNTIF(B16:B25, Detail!BP1:BP20) > 0)
)

Note: I have assumed that all the data within the range Detail!A1:BZ20 consists only of numeric values, and that there are no comma‑separated entries anywhere except in column 49.

IlirU

Resources