SOLVED

Iron Contributor

# FILTER or any other solution function

Dear Experts,

I have a Data like below (attached file) :-

From the Sheet1, I need to generate a report as in the Desired Output sheet, first 2 rows populated for the reference,

Br,

Anupam

2 Replies
best response confirmed by anupambit1797 (Iron Contributor)
Solution

# Re: FILTER or any other solution function

@anupambit1797 One possible dynamic array formula, tailored specifically to work with the layout and contents of the file provided:

``````=LET(
data, TOCOL(Sheet1!A:A, 1),
EXCL, LAMBDA(x,y, ISERR(SEARCH(x,y))),
clean, REDUCE(FILTER(data, EXCL(":", data)), {"comb";"band"},
LAMBDA(a,v,
LET(
arr, TAKE(a,, 1),
FILTER(HSTACK(a, SCAN("", arr, LAMBDA(p,c, IF(EXCL(v, c), p, c)))), EXCL(v, arr))
)
)
),
vals_keys, DROP(clean,, -1),
bands, CHOOSECOLS(clean, 3),
keyId, UNIQUE(CHOOSECOLS(clean, 2)),
rowId, SEQUENCE(ROWS(keyId)),
REDUCE(VSTACK("Band Combination", keyId), UNIQUE(bands),
LAMBDA(a,v,
LET(
arr, FILTER(vals_keys, bands = v),
rId, XMATCH(CHOOSECOLS(arr, 2), keyId),
idCount, MAP(rowId, LAMBDA(id, SUM(--(id = rId)))),
maxCount, MAX(idCount) + 1,
newRows, maxCount - idCount,
cols, SEQUENCE(, maxCount),
resize, SORTBY(
EXPAND(CHOOSECOLS(arr, 1), ROWS(arr) + SUM(newRows),, ""),
VSTACK(rId, TOCOL(IFS(newRows >= cols, rowId), 2))
),
HSTACK(a, VSTACK(EXPAND(v,, maxCount, ""), WRAPROWS(resize, maxCount)))
)
)
)
)``````

See attached...

# Re: FILTER or any other solution function

``````create temp table aa as
select rowid old_rowid,udf_fillna_m(iif(f03 like "Band Combination%",rowid,''),'fa') grp,udf_fillna_m(iif(lag(f03,2) over() like 'Band Combination%',f03,''),'fb') Band,udf_fillna_m(iif(f03 like "EUTRA Band%" or f03 like "NR Band%",rowid,''),'fc') sub_Band,regexp2('\d+A',f03) v from consolidateSheet;
create temp table bb as
select grp, Band,sub_Band,group_concat(v) v2 from aa where v<>'' group by grp, sub_Band;
select Band,group_concat(v2,'</td><td>') from bb group by grp;``````

online SQL:

create temp table aa as
select rowid old_rowid,udf_fillna_m(iif(f03 like "Band Combination%",rowid,''),'fa') grp,udf_fillna_m(iif(lag(f03,2) over() like 'Band Combination%',f03,''),'fb') Band,udf_fillna_m(iif(f03 like "EUTRA Band%" or f03 like "NR Band%",rowid,''),'fc') sub_Band,regexp2('\d+A',f03) v from consolidateSheet;
create temp table bb as
select grp, Band,sub_Band,group_concat(v) v2 from aa where v<>'' group by grp, sub_Band;
select Band,group_concat(v2,'</td><td>') from bb group by grp;

1 best response

Accepted Solutions
best response confirmed by anupambit1797 (Iron Contributor)
Solution

# Re: FILTER or any other solution function

@anupambit1797 One possible dynamic array formula, tailored specifically to work with the layout and contents of the file provided:

``````=LET(
data, TOCOL(Sheet1!A:A, 1),
EXCL, LAMBDA(x,y, ISERR(SEARCH(x,y))),
clean, REDUCE(FILTER(data, EXCL(":", data)), {"comb";"band"},
LAMBDA(a,v,
LET(
arr, TAKE(a,, 1),
FILTER(HSTACK(a, SCAN("", arr, LAMBDA(p,c, IF(EXCL(v, c), p, c)))), EXCL(v, arr))
)
)
),
vals_keys, DROP(clean,, -1),
bands, CHOOSECOLS(clean, 3),
keyId, UNIQUE(CHOOSECOLS(clean, 2)),
rowId, SEQUENCE(ROWS(keyId)),
REDUCE(VSTACK("Band Combination", keyId), UNIQUE(bands),
LAMBDA(a,v,
LET(
arr, FILTER(vals_keys, bands = v),
rId, XMATCH(CHOOSECOLS(arr, 2), keyId),
idCount, MAP(rowId, LAMBDA(id, SUM(--(id = rId)))),
maxCount, MAX(idCount) + 1,
newRows, maxCount - idCount,
cols, SEQUENCE(, maxCount),
resize, SORTBY(
EXPAND(CHOOSECOLS(arr, 1), ROWS(arr) + SUM(newRows),, ""),
VSTACK(rId, TOCOL(IFS(newRows >= cols, rowId), 2))
),
HSTACK(a, VSTACK(EXPAND(v,, maxCount, ""), WRAPROWS(resize, maxCount)))
)
)
)
)``````

See attached...