Retrieve multiple field Names into a comma separated list for a matching value

[rkirans]

Hi,
I am a beginner at excel programming and trying to find a way to retrieve multiple field names into a comma separated list for a matching value based on multiple criteria.
Here is how my data will look like. (Actual data will have entire month value. The lay out of field names in ).

NAME DAY-1 DAY-2 DAY-3 DAY-4 DAY-5 DAY-6 DAY-7 DAY-8 DAY-9
Resource 1 ;N; H; N; N; Y; Y; Y; L; Y
Resource 2 ;N; H; N; N; Y; Y; Y; Y; Y


If I give "Resource1" and "N" in 2 different cells , Then I should search in the table and retrieve field names with value N for Resource1. Here the expectation is to get DAY-1,DAY-3,DAY-4,

Thank you for help in advance

[natefarm]

Someone else may know a better way, but the attached will give the desired results for a 3-day sample. Note formulas in row 6, and in cells B8 and B9.

NameList.xlsx

[rkirans]

Thank you Natefarm for the help. I would need to use the 2-step approach here , but I have a method to start with.
Also incase if there is a another field along with ResourceName and N, what would be the method?
I need to try on those lines, if not I will concatenate, the 2 string fields into 1 and I will use it

[natefarm]

I would need to see an example of what you are talking about.

[rkirans]

Hi, I was working on the below form of data
Group Name Day-1 Day-2 Day-3 Day-4
G1 R1 H H N N
G2 R2 N H H H
G1 R3 H H N N
G2 R4 N H H H
G1 R5 N N N N
G2 R6 N N H H
G1 R7 N N N N
G2 R8 N H N H

For the time being I combined the data in first 2 columns into a third column and working on the formula you shared.
KEY Day-1 Day-2 Day-3 Day-4
G1R1 H H N N
G2R2 N H H H
G1R3 H H N N
G2R4 N H H H
G1R5 N N N N
G2R6 N N H H
G1R7 N N N N
G2R8 N H N H

[natefarm]

You're on the right track. Here's an adjusted version.

NameList.xlsx

Hiding column C and rows 11 and 12 would provide cleaner results.

[newdoverman]

Using natefarm's original workbook, here is an expanded version up to 31 days and R10.

I modified the formula to count the columns in the VLOOKUP and added IFERROR to take care of blank cells which would cause an error.

Please Login or Register  to view this content.

The concatenation is accomplished with this formula (real ugly)

Please Login or Register  to view this content.

The other formulae were left alone.

[Tony Valko]

Here's another way that uses a VBA function.

Copy the code at this link and paste it into a general module:

https://www.excelforum.com/showthread.php?p=3096647

Data Range

	A	B	C	D	E	F	G	H	I	J
1	NAME	DAY-1	DAY-2	DAY-3	DAY-4	DAY-5	------	Name	Status	------
2	Resource 1	N	H	N	N	Y		Resource 1	N	DAY-1, DAY-3, DAY-4
3	Resource 2	N	H	N	N	Y

This array formula** entered in J2:

=concatall(IF(INDEX(B2:F3,MATCH(H2,A2:A3,0),0)=I2,B1:F1,""),", ")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[rkirans]

Thank you all for the different options.

[Tony Valko]

You're welcome. We appreciate the feedback!