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?

Input data:

Table 1Header
Table 2Header
Table 3Sub-title
Table 4Header
Table 5Header
Table 6Sub-title
Table 7Sub-title
Table 8Header
Table 9Header
Table 10Sub-title

 

Expected result:

Table details1st record2nd Record3rd Record
Table 1   
Table 2Table 3  
Table 4   
Table 5Table 6Table 7 
Table 8   
Table 9Table 10  

 

I can filter and get list which tables are tagged as 'Header'. But I need to know the next table which is falling under it if it is tagged as 'sub-title'.

I'm trying to get the cells having text 'sub-title' populated next to my 'Header' table in subsequent column.

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