Cells that conditionally displays a concatenated list of other cells' contents

[pp57]

I didn't really know how to describe this problem in the title but what I want to do is as follows:

I have a table of with a number of rows and columns where the rows represent document numbers and the columns represent months. For each document number/month intersection the cell value can be red, green or left blank. I want to have a second table that has the colors as rows and months as columns and the intersecting cells list all of the document numbers from the original table where the color and month also intersect. It's a little confusing so I've attached an example worksheet showing the intended result. The top table shows where the results would be displayed and the bottom table shows where the data would be manually entered.

I've already come up with a way to accomplish this using VBA but was hoping to do it without using a macro. Named ranges, tables, etc. are acceptable.

[hrlngrv]

If you really mean it that you're using Office 2016, you have a function named TEXTJOIN which could do this.

C2: =TEXTJOIN(CHAR(10),1,LEFT($B$6:$B$11,255*(C$6:C$11=$B2)))

Fill C2 down into C3, then select C2:C3 and fill right into D2:H3.

[pp57]

hrlngrv's solution seems to be working. I'm not 100% on how it works though. What does LEFT($B$6:$B$11,255*(C$6:C$11=$B2)) do and more specifically the part of the function after 255? I believe 255 is a character limit so I don't quite understand its role here.

[hrlngrv]

You want to concatenate all the entries in B6:B11 for which the corresponding entries in the same rows in C6:C11 equal the value in B2. (C$6:C$11=$B2) returns an array of TRUE and FALSE depending on whether cells in C6:C11 equal B2. 255*(C$6:C$11=$B2) then returns an array of 255 or 0 because TRUE is converted to 1 and FALSE to 0 when used as arithmetic operands. LEFT($B$6:$B$11,255*(C$6:C$11=$B2)) then returns an array of values from B6:B11 or "" (empty string) depending on whether 255*(C$6:C$11=$B2) is 255 or 0, which in turn depends on whether cells in C6:C11 equal B2.

When TEXTJOIN's 2nd argument is 1 or TRUE, it skips blank/empty strings in its 3rd and subsequent arguments.

[wk9128]

C2 array formula

[Bo_Ry]

Textjoin only available in MS 365 and Excel 2019
https://support.microsoft.com/en-us/...3-0e8fc845691c

This also work without using a macro.
=TEXTJOIN(CHAR(10),,REPT($B$6:$B$11,C$6:C$11=$B2))

And without Textjoin but limit to 15 digits number result

=TEXT(NPV(999,IF(C$6:C$11=$B2,$B$6:$B$11))*10^(COUNTIFS(C$6:C$11,$B2)*3),REPT("000"&CHAR(10),COUNTIFS(C$6:C$11,$B2)))

[hrlngrv]

Why would anyone with a version of Excel recent enough to have the built-in TEXTJOIN function use a user-defined function to accomplish the same thing?

[hrlngrv]

OP mentioned the TEXTJOIN formula was working, so either they're [incorrect] about that, [incorrect] about the Office version they're actually using, or (gasp!) MSFT's documentation is inaccurate.

[pp57]

Thanks for the help everyone. I think I have what I need now.