Forum Discussion

yogi_new's avatar
yogi_new
Copper Contributor
Dec 28, 2023
Solved

How to filter and get data from multiple rows for matching criteria

Hello experts,   I've a scenario where, I'm trying to get list of records in subsequent column. Below is the scenario. Can you please help in giving any ideas, suggestions how this can be handled?...
excel
Excel for web
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Dec 28, 2023

    Hi yogi_new 

     

    With Excel on Web you should have MAP, SCAN... functions

     

    (there might be something easier...):

    =LET(
  m,  MAP(Table[TableName],Table[Content],
        LAMBDA(table,content, IF(content <> "Header", "", table))
  ),
  s,  SCAN("",m, LAMBDA(seed,v, IF(v = "", seed, v))),
  h,  FILTER(Table[TableName], Table[Content] = "Header"),
  r,  DROP(
        REDUCE("", h,
          LAMBDA(seed,v, VSTACK(seed, TEXTJOIN(",",,FILTER(Table[TableName],s=v))))
        ),
      1
  ),
  TEXTSPLIT( TEXTJOIN(";",,r), ",",";",,,"")
)

     

Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 28, 2023

Hi yogi_new 

 

With Excel on Web you should have MAP, SCAN... functions

 

(there might be something easier...):

=LET(
  m,  MAP(Table[TableName],Table[Content],
        LAMBDA(table,content, IF(content <> "Header", "", table))
  ),
  s,  SCAN("",m, LAMBDA(seed,v, IF(v = "", seed, v))),
  h,  FILTER(Table[TableName], Table[Content] = "Header"),
  r,  DROP(
        REDUCE("", h,
          LAMBDA(seed,v, VSTACK(seed, TEXTJOIN(",",,FILTER(Table[TableName],s=v))))
        ),
      1
  ),
  TEXTSPLIT( TEXTJOIN(";",,r), ",",";",,,"")
)

 

Resources