Concatenating values based on corresponding cell values

[Jstrazz]

Hello,

I have a row (4) of factors (e.g. "Taller than 5'6", "older than 25", etc) and a row (7) of corresponding values (e.g. 40, 6, 0, 0, 8) which is populated if the particular person meets that criteria, so that a score can be produced. Once the score has been summed up, I want to have a string of text that takes only the values from row (4) where there is a value in row (7) that is greater than 0. That way, I can see which factors are contributing to the score (there are about 200 factors).

I tried something like this:

=concatenate(if(G4:P4>0,G7:P7))

but it didn't work. I've done something similar with the sum function and it worked, but I'm trying to do the same thing with concatenate. Any help would be greatly appreciated.

[sandy666]

CONCATENATE() doesn't work with ranges

any example file (.xlsx) with what you have and what you want (result)?

but you can do it half-manually:
=CONCATENATE(TRANSPOSE(TRANSPOSE(IF(G4:P4>0,G7:P7,"")))
Enter
you will get error
go to formula bar, highlight TRANSPOSE(TRANSPOSE(IF(G4:P4>0,G7:P7,"")), press F9, delete curly brackets { } (only two), and hit enter

edit:
if you want any delimiter, e.g. space ( " " ) or comma ( , ) you can do it like this:
=CONCATENATE(TRANSPOSE(TRANSPOSE(IF(G4:P4>0,G7:P7,""))&",")
and the rest is the same as above

[FDibbins]

Hi, welcome to the forum

Perhaps something like this?
=IF(A4>0,A4,"")

If that was not what you wanted, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Jstrazz]

Dummy File.xlsx

How about this?

[Jstrazz]

Has anyone been able to open this document and tried to figure out a solution? Thanks

[FDibbins]

This is probably way too simplistic, but try,,,
=SUBSTITUTE(TRIM(IF(H9>0,H8,"")&" "&IF(I9>0,I8,"")&" "&IF(J9>0,J8,"")&" "&IF(K9>0,K8,"")&" "&IF(L9>0,L8,""))," ",",")

Also, you should avoid using merged cells like you have done there. If you want a "big" cell for the entry, make the row wider. Merged cells cause problems with formulas

[JohnTopley]

VBA function ...

Please Login or Register  to view this content.

in E9

=rtnVar(H9:L9,$H$8:$L$8)


H9:L9 is the numeric data: H8:lL8 are the headings

Change ranges as required