Create a second table with subset data

This can be done with MATCH, INDEX, and a formula that creates an index column numbering the 'open' days.

Table 1, on the left is based on your two column table showing Mondat to Friday dates in column A and empty cells on days the market is open and text on the weekday dates on which it is closed.in column B.

A third column has been added to hold a list of numbers indicating the 'open' dates. This 'index' list is created by the formula below, entered in C2, and filled down as far as needed.

Table 1::C2: IF(B2="",COUNTIF(B$1:B2,""),"")

As the formula is filled down the column, the B$1B2 range expands to include all rows from 1 to the row containing the current copy of the formula.

The formula shown below the table is entered in cell B2 of Table 2. Row()-1 sets a search value used by MATCH to locate the row with each date to be copied to this table. The location is handed to INDEX, which retrieves the value on the same row of column A as the index marker in column C.

Other columns on Table 2 would use the same INDEX MATCH formula with the same index column ans the column A reference changed to match the column of Table 1 containing the data to be transferred.

Regards,

Barry

Hi 'surfer,

You're welcome.

Thanks for the checkmark and the note regarding understanding. I usually try to provide an explanation of what's happening in a formula in the hope that it will give the reader a clearer understanding of how this and other formulas work, and help him/her solve a future issue.

Regards,

Barry