Combine unique text cells, if the number next to it is greater than 0

[2990]

Hello, I'm posting to the VBA forum as I've been unable to solve this issue on my own using only formulas. I'm a VBA novice, so I'm hoping I'll be able to understand the insights you all have.

I have a list of unique words, and next to each unique word are multiple columns with numbers. I'd like to combine all of the unique words, where the numbers next to it are greater than 0, into a single cell, with each word separated by a + sign.

For each column of numbers, I want a different result.

For example:

Apple / 1 / 0 / 1
Orange / 3 / 1 / 1
Pear / 0 / 0 / 2

In the above example

Result 1:
Apple+Orange

Result 2:
Orange

Result 3:
Apple+Orange+Pear

Above each column of numbers is a sequential identifier, such as "001", "002", "003". On another sheet, where I want the unique words combined, I will have this sequence. So, where 001 appears, it looks at 001, scans down, finds the numbers greater than 0, pulls those words, and combines them with a + in between.

Is there a way for me to accomplish this?

[quekbc]

This could be done by formulas with the use of helper cells which basically goes:
IF (value = 0, prior_text , prior_text & "+" & current_text)
Then in the next sheet, something that selects the end results.

Without seeing the actual file, I can only whip up a quick example to approach. May or may not be easily transferable to your file. See attach for reference.

[MarvinP]

Hi 2990 and welcome to the forum,

In Excel 2016 there is a new function called TextJoin() that makes your problem a little simpler. If you had 2016 then you could replace the numbers with the words, if they weren't zero. Then use TextJoin to build your answer. I'll attach my answer and method here for others who have the newer version of Excel.

TextJoin Answer.xlsx

[2990]

This could be done by formulas with the use of helper cells which basically goes:
IF (value = 0, prior_text , prior_text & "+" & current_text)
Then in the next sheet, something that selects the end results.

Without seeing the actual file, I can only whip up a quick example to approach. May or may not be easily transferable to your file. See attach for reference.

Hi Quekbc, thank you - this worked for me. Took me a little bit to wrap my head around the double if statement, but your explanation and attachment helped a lot!

Hi 2990 and welcome to the forum,

In Excel 2016 there is a new function called TextJoin() that makes your problem a little simpler. If you had 2016 then you could replace the numbers with the words, if they weren't zero. Then use TextJoin to build your answer. I'll attach my answer and method here for others who have the newer version of Excel.

Attachment 530380

It looks like I need to upgrade then, as that is just what I was looking for! Thank you for that insight.