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

[yogi_new]

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 1	Header
Table 2	Header
Table 3	Sub-title
Table 4	Header
Table 5	Header
Table 6	Sub-title
Table 7	Sub-title
Table 8	Header
Table 9	Header
Table 10	Sub-title

Expected result:

Table details	1st record	2nd Record	3rd Record
Table 1
Table 2	Table 3
Table 4
Table 5	Table 6	Table 7
Table 8
Table 9	Table 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.

[#2 · Accepted solution]

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), ",",";",,,"")
)