How to conditionally concatenate in Excel 2016

Hi all,

I am using Excel 2016 and I want to concatenate values based on their value.

Basically, imagine a column which contains all the values in different rows. Each row of that column has one value. Some values are positive and some are negative.
I want to concatenate two values and drag this formula down. However, if one of these two values is negative then I dont want the concatenation. In other words, I want to find the next positive value to apply the formula.

Can anyone please help me?

I have attached an example. Column A includes the values and Column B includes what I want.

Thanks in advance,
Marios

I forgot to mention that consecutive rows can have negative values. Otherwise, it would be easy to just apply a condition if formula.

Using a helper column (C), put this formula in C2:

=IF(A2<=0,"",COUNTIF(A:A,">0")-COUNT(C$1:C1))

and copy this down to the bottom of your data. Then in B2 you can use this formula:

=IF(OR(C2=MAX(C:C),C2=""),"",A2&"-"&INDEX(A:A,MATCH(C2+1,C:C,0)))

and copy this down.

Hope this helps.

Pete

Hi ChemistB,

Thanks for this. Actually, I made a mistake. Instead of concatenating Node B to Node A I want the opposite(instead of concatenating row 3 to row 2, I want row 2 to row 3). Please see the example_corrected spreadsheet.

How would this work now?

If you take my suggested approach, you can have the same formula in C2 (copied down) and this one in B2:

=IF(OR(C2=MAX(C:C),C2=""),"",INDEX(A:A,MATCH(C2+1,C:C,0))&"-"&A2)

which just reverses the order.

Hope this helps.

Pete