Thank you all for the reply's many of you suggestions helped so now I know where to go to get great Excel Formula advice!!! I ultimately went with a power query to get the results I needed. No need for more replies.  Thank you all again!!!

=LET(
  f, FILTER(Detail!$A:$BZ, 
    (COUNTIF($B$16:$B$25, Detail!$BP:$BP) > 0) * 
    (TRIM(SUBSTITUTE(Detail!$AW:$AW, CHAR(160), "")) <> "")),
  base, CHOOSECOLS(f, 1, 3, 4, 5, 68),
  
  split49, MAP(CHOOSECOLS(f, 49), LAMBDA(x,
    LET(
      cleaned, TRIM(SUBSTITUTE(x, CHAR(160), "")),
      IF(cleaned = "", {""}, TEXTSPLIT(cleaned, ","))
    ))),
  
  split53, MAP(CHOOSECOLS(f, 53), LAMBDA(x,
    LET(
      cleaned, TRIM(SUBSTITUTE(x, CHAR(160), "")),
      IF(cleaned = "", {""}, TEXTSPLIT(cleaned, ","))
    ))),
  
  SORT(UNIQUE(HSTACK(base, split49, split53)), 1, TRUE)
)

My answers are voluntary and without guarantee!

Hope this will help you.

For the record, a Power Query option as attachment

Hi tonyceccato​ 

• Why selecting range Detail!$A:$BZ when you actually use max. Detail!$A:$BP (col. 68)?
• As already suggested by VBasic2008​, Format your data as Table if possible. Range Detail!$A:$BP means 1 million rows * 68 cols

In the below option:

• Assumed column Detail!BP:BP has no blank/empty cell(s) between the 1st and last used row in that column. This to resize/shrink your data range (Detail!$A:$BZ) to what's actually required (base + columns to split only)
• Should they exist in columns to split, strings like: " , Peach ,Grapes", "Peach,Strawberry, ,Raspberry,,,"... are managed to prevent extra columns when splitting *

=LET(
  DataRange,            Detail!A1:BZ1000,
  CountifCriteriaRange, B16:B25,

  delim,          CHAR(44),
  nbspace,        CHAR(160),

  Normalize,      LAMBDA(str,
                    LET(
                      ReplacedNbs,    SUBSTITUTE(str, nbspace, "" ),
                      ReplacedEmpty,  REGEXREPLACE(ReplacedNbs, "(^\s*,\s*|,\s*(?=,|$))", "" ),
                      TRIM( REGEXREPLACE(ReplacedEmpty, "\s*,\s*", "," ) )
                    )
                  ),
  SplitToCols,    LAMBDA(array,[normalized],
    LET(
      Normalized,     IF( normalized = TRUE, array, Normalize(array) ),
      Thunk,          LAMBDA(x, LAMBDA(x) ),
      Split,          LAMBDA(array, IF( LEN(array), TEXTSPLIT(array, delim,, TRUE ), "" ) ),
      ThunkSplit,     LAMBDA(accum,array, Thunk( Split( array ) ) ),
      arrThunks,      SCAN(0, Normalized, ThunkSplit ),
      ExpandThunk,    LAMBDA(rw,cl, INDEX( INDEX( arrThunks, rw, 1 )(), 1, cl ) ),
      MaxOutCols,     MAX( LEN(Normalized) - LEN( SUBSTITUTE( Normalized, delim, "" ) ) ) +1,
      Splitted,       MAKEARRAY( ROWS(array), MaxOutCols, ExpandThunk ),
      IFERROR( Splitted, "" )
    )
  ),

  RowsBP,         COUNTA( Detail!BP:BP ),
  NormalizedBW,   Normalize( TAKE( CHOOSECOLS(DataRange, 49 ), RowsBP ) ),
  ShrinkedData,   HSTACK(
                    INDEX(DataRange, SEQUENCE( RowsBP ), {1,3,4,5,53,68} ),
                    NormalizedBW
                  ),
  FilteredData,   FILTER( ShrinkedData,
                      IFNA( XMATCH( CHOOSECOLS(ShrinkedData, 6), CountifCriteriaRange ), 0 )
                    * ( CHOOSECOLS(ShrinkedData, 7) <> "" )
                  ),
  Base,           CHOOSECOLS( FilteredData, {1,2,3,4,6} ),
  StackedArrays,  HSTACK( Base,
                          SplitToCols( CHOOSECOLS( FilteredData, 7 ), TRUE ),
                          SplitToCols( CHOOSECOLS( FilteredData, 5 ) )
                  ),
  SORT( UNIQUE( StackedArrays ), 1, TRUE )
)

* RegEx by Copilot

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

Split Column by Delimiter

Since you are trying to split 2 columns by delimiter, it's a good idea to create a Lambda function to do this:

=LAMBDA(c,d,LET(
    n,LEN(c)-LEN(SUBSTITUTE(c,d,)),
    m,MAX(n)+1,
    TEXTBEFORE(TEXTAFTER(d&c&REPT(d,m-n),d,
        SEQUENCE(,m)),d)))

Using the Name Manager, you could define a name, e.g., SplitD and copy the formula above into the RefersTo box.

Similarly, since you are doing your 'non-breaking space business' several times, you could create the following Lambda function named, e.g. Nbs:

=LAMBDA(a,TRIM(SUBSTITUTE(a,CHAR(160),"")))

I kept the functions inside the formula so you need not bother at the moment.

=LET(a,Detail!A2:BZ21,b,$B$16:$B$25,
    Nbs,LAMBDA(a,TRIM(SUBSTITUTE(a,CHAR(160),""))),
    SplitD,LAMBDA(c,d,LET(
        n,LEN(c)-LEN(SUBSTITUTE(c,d,)),
        m,MAX(n)+1,
        TEXTBEFORE(TEXTAFTER(d&c&REPT(d,m-n),d,
            SEQUENCE(,m)),d))),
    ia,Nbs(CHOOSECOLS(a,49))<>"",
    ib,COUNTIF(b,CHOOSECOLS(a,68))>0,
    f,FILTER(a,ia*ib),
    base,CHOOSECOLS(f,1,3,4,5,68),
    cAW,TRIM(SplitD(Nbs(CHOOSECOLS(f,49)),",")),
    cBA,TRIM(SplitD(Nbs(CHOOSECOLS(f,53)),",")),
    r,SORT(UNIQUE(HSTACK(base,cAW,cBA)),1,1),
    r)

I would strongly suggest you put your data into a table and use structured references instead of utilizing column indices.

Your error is coming from TEXTSPLIT() when the input is blank / not text (or when the filter returns no rows). Make the split ranges “safe” by coercing to text and providing a fallback.

Try this pattern (key change: IFERROR(TEXTSPLIT(...), value) and &"" to coerce to text):

=LET( f, FILTER(Detail!$A:$BZ, (COUNTIF($B$16:$B$25,Detail!$BP:$BP)>0) * (TRIM(SUBSTITUTE(Detail!$AW:$AW,CHAR(160),""))<>"") ), base, CHOOSECOLS(f,1,3,4,5,68), c49, SUBSTITUTE(CHOOSECOLS(f,49)&"",CHAR(160),""), split49, IFERROR(TRIM(TEXTSPLIT(c49,",")), TRIM(c49)), c53, SUBSTITUTE(CHOOSECOLS(f,53)&"",CHAR(160),""), split53, IFERROR(TRIM(TEXTSPLIT(c53,",")), TRIM(c53)), SORT(UNIQUE(HSTACK(base,split49,split53)),1,TRUE) )

If you also want to avoid errors when FILTER returns nothing, wrap f:

f, IFERROR(FILTER(...), "")

That should stop the #VALUE! and still “explode” comma-separated values when present.

Let me know, if it works or not...

Here’s a clearer explanation of why your formula breaks — and a corrected version that will work even when there are no commas in columns 49 or 53.

💡 Why your formula returns #VALUE!

TEXTSPLIT() throws a #VALUE! error when the input is a vertical array (multiple rows) and the delimiter is not found in some rows.
In your formula:

TEXTSPLIT(CHOOSECOLS(f,49), ",")

CHOOSECOLS(f,49) returns multiple rows, but TEXTSPLIT() expects a single text string, not an array of many rows.

So when a row has no comma, Excel can’t split it consistently across rows → #VALUE!.

✅ The fix: apply TEXTSPLIT() row‑by‑row using MAP()

MAP() lets you apply a function to each row independently, which prevents the #VALUE! error.

Here is the corrected formula:

=LET( f, FILTER( Detail!$A:$BZ, (COUNTIF($B$16:$B$25, Detail!$BP:$BP) > 0) * (TRIM(SUBSTITUTE(Detail!$AW:$AW, CHAR(160), "")) <> "") ), base, CHOOSECOLS(f, 1,3,4,5,68), split49, MAP( CHOOSECOLS(f,49), LAMBDA(x, TEXTSPLIT(TRIM(SUBSTITUTE(x,CHAR(160),"")), ",")) ), split53, MAP( CHOOSECOLS(f,53), LAMBDA(x, TEXTSPLIT(TRIM(SUBSTITUTE(x,CHAR(160),"")), ",")) ), SORT(UNIQUE(HSTACK(base, split49, split53)), 1, TRUE) )

🧠 What this version does differently

✔ MAP() processes each row individually

So if one row has "A,B,C" and another has "X" (no comma), both are handled safely.

✔ TEXTSPLIT() always receives a single text value

No more array‑wide splitting → no more #VALUE!.

✔ Output columns expand correctly

Rows with more comma‑separated values simply spill into more columns.

If you want, I can also:

• Modify it to pad missing columns
• Combine split49 and split53 into a single dynamic block
• Return results in a vertical exploded list instead of horizontal columns
• Add error‑handling or blank‑value suppression

Just tell me how you want the output to behave.

Hey tonyceccato​, if you'd like for me to keep trying lmk! I hope this helps. 

StephanieBowie​