I'm facing the following problem:

I have eleven columns of that represent product origins, sometimes the product is taken from one origin and sometimes it is taken from two origins. The columns have values in them that correspond to a fraction of how much is used of that origin. Example (only using 4 origins for ease of example):


{1   0   0   0;

0.5  0   0.5 0; 

0    1   0   0;}

The results that I want are then for the first row NTH, for the second row NTH-MSV and for the third row LGL.

I tried using the following formula, but I can't get excel to concatenate the empty cells or errors.


AQ1 to BA1 contain the product origins and the columns below contain the values (ranging from 0 to 1).

  • Are you trying to concatenate only the cells in a row that have non zero value as a final output? Some sort of Conditional Concatenate?
    – patkim
    Feb 16, 2016 at 8:01
  • What should the output in the cell be? The numbers added together, or written out (eg, "0.5, 0.5")?
    – Jonno
    Feb 16, 2016 at 8:03

1 Answer 1


Following clarification, try this custom function:

Function HEADERFOROVERZERO(rng As Range)
Dim rngCell, returnString: returnString = ""
For Each rngCell In rng
    If (IsNumeric(rngCell.Value)) Then
        If (rngCell.Value > 0) Then
            returnString = returnString & Cells(1, rngCell.Column).Value & "-"
        End If
    End If
If (Len(returnString) > 1) Then
    returnString = Left(returnString, Len(returnString) - 1)
End If
End Function

enter image description here

  • I would like the result to be the origin of the product. So in your example for the first row NTH.
    – Michthan
    Feb 16, 2016 at 10:48
  • @Michthan Ahh, so the column header?
    – Jonno
    Feb 16, 2016 at 10:50
  • @Michthan Amended my answer - is this what you wanted?
    – Jonno
    Feb 16, 2016 at 10:53
  • This is perfect! Thanks. I'm going to implement it in a few hours because it's busy here now, so i'll come back to you if it works!
    – Michthan
    Feb 16, 2016 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.