Get all of names in range

[Luis Nic]

hi. I need help with this. I have a title row with names. And de next row bellow each name word like “Ok” or “n/a” or “No”

I need to now the names that match one of the criteria “No”


Example
Ana jhon Rebeca max Result
ok No n/a no Jhon, max

 

[GraH - Guido]

Can you upload a sample file, and a manual example of what the result should be. It does not sound too difficult, but I fail to understand from your posted example.

 

[Luis Nic]

thanks i uploaded a file

 

[GraH - Guido]

It turns out to be less easy (for me).
Seems you would need a VBA written function (User Defined Function).
Like here
http://www.eileenslounge.com/viewtopic.php?f=27&t=12298#
both CONCATENATEIF and CONCATENATEIFS avaiable.
or...
If you have Excel 2016 with the 365 subscription, you could use TEXTJOIN
=TEXTJOIN(",", TRUE, IF(A2:F2 = G2,A$1:F$1, ""))
Enter with CONTROL + SHIFT + ENTER.
or...
wait for some true ninja wit...

 

[Luis Nic]

Thanks for your reply. Unfortunately i have excel 2013 and don’t have that fiction yet.

 

[bosco_yip]

Try,

H2 :

=SUBSTITUTE(TRIM(IF(A2=G2,A$1,"")&" "&IF(B2=G2,B$1,"")&" "&IF(C2=G2,C$1,"")&" "&IF(D2=G2,D$1,"")&" "&IF(E2=G2,E$1,"")&" "&IF(F2=G2,F$1,""))," ",",")

Regards
Bosco

 

[GraH - Guido]

Hi Bosco, no miracle solution for this one? If there would be new names in new columns, the formula must be updated. Very pleased to see you do write down-to-earth-formulas too.

 

[Luis Nic]

Rite now i would have 13 names but it would be different every week. I apriciate your help.

 

[GraH - Guido]

Hi Luis, I guess your best choice is using one of the UDF-macros. And then making it variable with a helper column, or a nested function.
I did something with Power Query (also available in Excel 2013, if activated) that also worked for the uploaded sample. But I would need to make it variable, so it can work with more names and more rows and more criteria?
If you could elaborate a bit on the "variables", then perhaps some-one here can propose a working solution for you.