Retriving column header for non empty cells in a row

[Sushil Patil]

Hello,

I have worksheet like one below

No Att1 Att2 Att3 Att4 Att5
A 1 1 1
B 1 1 1 1
C 1 1
D 1 1
E 1 1 1
F 1 1 1 1


I want to know a formula that will return column header for second non empty cell in a row.

fo ex. for A formula should return Att3

Also if you can tell about how to modify formula so as that it will return column header for nth non empty cell in row.

Thanks and Regards
SushiL


Submitted via EggHeadCafe - Software Developer Portal of Choice
CommandEventArgs (or- Which Button was Clicked?)
http://www.eggheadcafe.com/tutorial...67f-521cc319a78e/commandeventargs-or-whi.aspx

 

[Luke M]

Try using this UDF (Right click on Sheet Tab, view code. Insert - Module,
paste this in, then close the VBE)

'==================
Function HeaderReturn(HeaderRange As Range, CheckRange As Range, _
FindValue, Instance As Double)
xCount = 0
For Each c In CheckRange
'Look for the value you want found
If c.Value = FindValue Then
'If found, add to tally
xCount = xCount + 1
If xCount = Instance Then
'Once tally equals instance count, record column number
xColumn = c.Column
End If
End If
Next c

HeaderReturn = Cells(HeaderRange.Row, xColumn)

End Function
'=================

Back in XL, your first formula will be:
=HeaderReturn(A$1:G$1,A2:G2,1,2)

Syntax is (HeaderRange, SearchRange, Value_to_find, What_Count)

So, to find the second blank cell:
=HeaderReturn(A$1:G$1,A2:G2,"",2)

 

[Ashish Mathur]

Hi,

Try this array formula (Ctrl+Shift+Enter), This assumes that the data is in
range A1:F7 (headers are in row1). A,B,C etc are in column A. I1 has 2
I.e. the nth small value

=INDEX($A$1:$F$1,1,SMALL(IF(INDEX((B2:F2<>"")*(COLUMN(B1:F1)),,)=0,10000,INDEX((B2:F2<>"")*(COLUMN(B1:F1)),,)),I1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

in message news:[email protected]...