CODEWITHSUNDEEP
excel
Akshay Hazari
Akshay Hazari

Reputation: 3267

Get comma separated column headers of all columns where value > 0 from a row

I want to get all column headers names where they are greater than zero for a row.

So far I have tried this =IF(COUNTA($A2:$E2)=0,"",INDEX($A$1:$E$1,MATCH(TRUE,INDEX($A2:$E2>0,0),0))

It gives me the name of the first column which is greater than zero.

a   b   c   d   e   
0   1   0   1   1   b
1   0   0   1   0   a

Whereas I need something like this

a   b   c   d   e   
0   1   0   1   1   b,d,e
1   0   0   1   0   a,d

I have never worked with excel before. Any help is appreciated

Upvotes: 1

Views: 1496

Answers (2)

Bjoern Stiel
Bjoern Stiel

Reputation: 4161

I realise the answer above does the job and mine is probably a bit complicated if you do no need anything more dynamic.

However, if you do need a more dynamic solution, you can do this: Assuming your header is in cells A1:E1 and your data underneath, the first step is to get a function return the values that match your criteria: =IF(A2:E2>0,A$1:E$1,"") You need to enter this as an array formula (Ctrl+Shift+Enter)

This returns an array which returns loads of blanks, so there are two more things we need to take care of: 1. Remove blanks from that array 2. Concatenate the array so that it returns a comma separated string

Unfortunately Excel does not have any of these functions (which is a shame really), so you need some VBA magic:

Public Function joinArray(arr As Variant)
   arr = arr
   Dim i As Integer
   Dim s As String: s = ""
   For i = LBound(arr, 1) To UBound(arr, 1)
      s = s & arr(i) & ","
   Next i
   joinArray = Left(s, Len(s) - 1)
End Function

Public Function Strip(arr As Variant)
   arr = arr
   Dim v() As Variant
   Dim k As Integer, i As Integer
   For i = LBound(arr, 1) To UBound(arr, 1)
      If arr(i) <> "" Then
         ReDim Preserve v(0 To k)
         v(k) = arr(i)
         k = k + 1
      End If
   Next i
   Strip = v
End Function

You can now call =joinarray(strip(IF(A2:E2>0,A$1:E$1,""))) as an array formula (Ctrl + Shift + Enter)

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 36880

Use this formula in F2 cell and fill down.

=SUBSTITUTE(TRIM(CONCATENATE(IF(A2>0,$A$1 & " ",""),IF(B2>0,$B$1& " ",""),IF(C2>0,$C$1& " ",""),IF(D2>0,$D$1& " ",""),IF(E2>0,$E$1,"")))," ",",")

enter image description here

Upvotes: 1

Related Questions