Extracting data from a table with merged cells

Question

Dear Excel Community&nbsp;&nbsp;I'm trying to extract data from a table with merged cells into a new table (that doesn't include merged cells).&nbsp;&nbsp;This is an extract of the table I want to extract data from:&nbsp;And this is how it should look like:&nbsp;Is there a good way (ideally with formulas) to extract the data as explained? Ideally the data in the new table should also be updated automatically, in case of any changes made to the text in the original table.&nbsp;&nbsp;Really appreciate your tips 🙂&nbsp;Best,Thomas&nbsp;

joswoolley · Answer

Tom_Brasch&nbsp;&nbsp;Use FILTER, e.g.:=FILTER(A1:B20,A1:A20&lt;&gt;"")&nbsp;Regards

peterbartholomew1 · Answer

Tom_Brasch&nbsp;I had to go to something a little more elaborate because I did not have the cell merging in both columns.= LET(
    topics,   FILTER(topic, topic&lt;&gt;""),
    criteria, FILTER(criterion, criterion&lt;&gt;""),
    HSTACK(topics, criteria)
  )&nbsp;

patrick2788 · Answer

Tom_Brasch&nbsp;I propose a non-FILTER solution:&nbsp;=WRAPROWS(TOCOL(data,1),2)

Forum Discussion

Extracting data from a table with merged cells

3 Replies

Resources