Return a Comma-Separated List from an Array Formul

Question

I could probably puzzle this one out in VBA, but would rather not have to use that approach if possible, and it seems like this is something Excel's built-in functions should be able to manage.

I have a spreadsheet we recently stopped printing that we would use to verify data came in from our various sites, and for which days. Column headers correspond to site numbers ($D$1:$AU$1 for this spreadsheet) and rows are for dates ($A$3:$A$24 for the month of December -- we don't do this data validation on weekends). The "B" column contains the date whose information SHOULD have come in, for example, on 12/4, we should receive the information from 12/2 at each site.

In the past, we used column "C" to write which sites were behind. If Site 3 only sent in information from 12/1 on the 4th, we would write a "3" in that column. I'd like to continue this convention, since it's what the office understands. Change Is Bad and all that.

So far I've muddled through on my own and wrote an Array Formula that returns {0,0,1,0,...,0} if only site 3 is behind. That formula is =IFERROR(SEARCH(B3,D3:AU3)-SEARCH(B3,D3:AU3),1)

From here it's trivial to do =IF(ISBLANK(D4),"",INDEX(D1:AU1,1,MATCH(1,IFERROR(SEARCH(B3,D3:AU3)-SEARCH(B3,D3:AU3),1)),0)) which works great, so long as there's only one site that's behind. If we have more than one site behind, it returns the first value (as expected).

If both sites 3 and 6 are behind, we want to see "3, 6" (or any other human-readable format) but the only solutions I found are to write a custom VBA script to concat an array. I'd rather stay away from custom VBA if humanly possible.

Thanks,
Adam

Answer 1

Try this "array formula" in BA3

=IFERROR(INDEX($D1:$AU1,SMALL(IF(ISERROR(SEARCH($B3,$D3:$AU3)),COLUMN($D3:$AU3)-COLUMN($D3)+1),COLUMNS($BA3:BA3))),"")

confirm with CTRL+SHIFT+ENTER and copy across to get all matches

As you say you can then concatenate those back to C3