Forum Discussion

anupambit1797's avatar
anupambit1797
Steel Contributor
Jul 16, 2024
Solved

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,

Thanks in Advance,

Br,

Anupam

BI & Data Analysis
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Jul 17, 2024

    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...

2 Replies

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Jul 17, 2024

     

    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;
cli_no_header;
select Band,group_concat(v2,'</td><td>') from bb group by grp;

    anupambit1797 

    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;
    cli_no_header;
    select Band,group_concat(v2,'</td><td>') from bb group by grp;

     

     

  • djclements's avatar
    djclements
    Silver Contributor
    Jul 17, 2024

    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...