Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of credit card transactions showing the name of the card holder, their Branch and the amount. I want to produce a report for each Branch, so what I want is to extract only those people who [...]

In this post we are going to extract all “not empty” dates in a simple calendar. In a previous blog post we extracted only date ranges and names. Here is a picture of the calendar. Here is a picture of all extracted dates and corresponding “names”. Array formula in cell A2: =SMALL(IF(Sheet1!$C$5:$AG$16<>”", DATE(Sheet1!$F$2, ROW(Sheet1!$C$5:$AG$16) -MIN(ROW(Sheet1!$C$5:$AG$16))+1, [...]

The obvious question from yesterdays blog post is: How to customize formula to extract dates for all months in calendar? (I have added more data to the calendar since yesterday) Answer: Array formula in A2: =SMALL(IF((Sheet1!$C$5:$AG$16<>Sheet1!$B$5:$AF$16)*(Sheet1!$C$5:$AG$16<>”"), DATE(Sheet1!$F$2, ROW(Sheet1!$C$5:$AG$16)-MIN(ROW(Sheet1!$C$5:$AG$16))+1, Sheet1!$C$4:$AG$4), “”), ROW(A1)) + CTRL + SHIFT + ENTER. Copy cell A2 and  paste it down as [...]

Sam asks: One more question for the Calendar that you have set up above can we have a excel formula which will give us a below table StarWk EndWk Name 1 2 G 4 6 G 7 15 R … and so on Question found here. Answer: Array Formula in cell A2: =SMALL(IF((Sheet1!$C$3:$AG$3<>Sheet1!$B$3:$AF$3)*(Sheet1!$C$3:$AG$3<>”"), Sheet1!$C$2:$AG$2, “”), [...]

Arielle asks: i have a formula in row a from A1:Z1 that displays “” if there is an error. I need to then take that row and combine or concatenate all the cells into one cell and have each cell separated by ” / “. I only want the ” / ” to display if [...]