can a sublist be extracted using an array formula?
Posted by Mark on October 20, 2001 1:58 PM
The idea is like an appointment list. Table contains 2 columns, $A$2:$A$99 contains dates and $B$2:$B$99 contains planned events. Can an array formula be used to show the events on a particular day?
I tried on another sheet adding a date to cell A1 and selecting A2 to A17 and entering =IF((Events!$A$2:$A$10=$A$1),Events!$B$2:$B$10,"") as an array formula.
The problem is that onle events occurring in the first 13 rows of the events table are showing up and they always occur in the same relative position from the top of the table. I want each event found to be displayed one per line starting with the first line of the array formula and the rest blank. IOW, if there are 2 events scheduled on a day, then I want the top two cells in the array to show those events and the other 14 cells to show blanks.
Is this possible?
Posted by IML on October 22, 2001 9:27 AM
One way would be if you can afford a hidden column in your table. You could create a unique date identifier by the formula
=A2&"-"&COUNTIF($A$2:A2,A2)
and copying it down.
You can then use index and match. (i assumed the hidden column to be C on sheet 2.)
=IF(COUNTIF(Sheet2!$A$2:$A$10,$A$1)>=ROW()-1,INDEX(Sheet2!$A$2:$C$99,MATCH($A$1&"-"&ROW()-1,Sheet2!$C$2:$C$99,0),1),"")
for date and
=IF(COUNTIF(Sheet2!$A$2:$A$10,$A$1)>=ROW()-1,INDEX(Sheet2!$A$2:$C$99,MATCH($A$1&"-"&ROW()-1,Sheet2!$C$2:$C$99,0),2),"")
for description.
Posted by Mark on October 24, 2001 8:03 AM
Thanks. That will work.
