CODEWITHSUNDEEP
vbaexcel
hafis jamal
hafis jamal

Reputation: 3

get multiple column names (header) in table associated with particular value in to a cell

i need to get multiple column names (header) in table associated with particular value in to a cell

as i explained, i need to get the heading names corresponding to value "n" to column E.

enter image description here i used the formula

 =INDEX((A$1:D$1),MATCH("n",A2:D2,0))

here. but it only give one column name.

i am open to vba scripts also. but i think it doesn't need vba. just improve the the above formula, may be. i tried and failed. any help. thank you guys

Upvotes: 0

Views: 693

Answers (2)

Fabrizio
Fabrizio

Reputation: 662

if you are really "open" to vba, I'll use one simple UDF like:

Function HeatherNames(rg As Range, rf As String) As String
For Each cell In rg
    If cell = rf Then HeatherNames = HeatherNames & Cells(1, cell.Column).Value & "-"
Next cell
HeatherNames = Left(HeatherNames, Len(HeatherNames) - 1)
End Function

you can use it in the column E `=HeatherNames(A2:D2;"n") now you can select the arg.1 (range) and type (or referring to another cell) the arg.2

Upvotes: 1

XOR LX
XOR LX

Reputation: 7762

Assuming you have Excel 2010 or later, in E2:

=IF(COLUMNS($A:A)>COUNTIF($A2:$D2,"n"),"",INDEX($1:$1,AGGREGATE(15,6,COLUMN($A2:$D2)/($A2:$D2="n"),COLUMNS($A:A))))

Copy to the right and down as required.

It would actually be slightly more efficient (and certainly if your dataset in reality is quite large) to have the initial IF clause held within its own cell, such that it is calculated for each row only once, rather than for each instance of the formula within that row. So a better set-up would be, in E2:

=COUNTIF($A2:$D2,"n")

copied down. Then, in F2:

=IF(COLUMNS($A:A)>$E2,"",INDEX($1:$1,AGGREGATE(15,6,COLUMN($A2:$D2)/($A2:$D2="n"),COLUMNS($A:A))))

copied to the right and down again.

Regards

Upvotes: 0

Related Questions