Extracting data from a table with merged cells

Copper Contributor

Dear Excel Community 


I'm trying to extract data from a table with merged cells into a new table (that doesn't include merged cells). 


This is an extract of the table I want to extract data from:



And this is how it should look like:



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. 


Really appreciate your tips :)





3 Replies



Use FILTER, e.g.:




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<>""),
    criteria, FILTER(criterion, criterion<>""),
    HSTACK(topics, criteria)



I propose a non-FILTER solution: