vlookup/match to find column name

[sailingHLA]

I'm a little stuck. I have a table with gene names as column headers and
patient samples as the row headers. In our lab testing, each patient sample
will have at least one (many times more) genes that have a numerical value
associated with it. For example, sample A987 may have a result in the table
under gene A, gene D and gene L. All other cells for which a patient does
not have a sepcific gene is blank. I need to find a function that can look
at the table in the row for sample A987 and tell me which genes the patient
has (i.e. the column headers of gene A, gene B, etc.). If it is possible to
have each gene listed as a value in one cell all in the same column, that
would be great. Thanks in advance for any help.

 

[daddylonglegs]

How many headers do you have? You could string together a series of IF
functions, one for each column, to do what you want, e.g. if headers are in
B1:F1 then use this formula in row 2 copied down

=TRIM(IF(B2<>"",B$1&" ","")&IF(C2<>"",C$1&" ","")&IF(D2<>"",D$1&"
","")&IF(E2<>"",E$1&" ","")&IF(F2<>"",F$1&" ",""))

Of course it will get a little long if you have many headers....

Excel doesn't have a native function to concatenate an array so, without
VBA, your only other option, for a shorter formula, would be to use MCONCAT
function from morefunc add-in and then you could use this formula

=TRIM(MCONCAT(IF(B2:F2<>"",B$1:F$1&" ","")))

confirmed with CTRL+SHIFT+ENTER

Of course, this can be more easily extended to cater for a large header range

Here's a link for morefunc;

http://xcell05.free.fr/english/

 

[Jim Cone]

Or add this code to a standard module.
Then use it by entering this function "=WhichOnes()" in the column immediately
to the right of your data. It assumes the column headings are in Row 1.

'-------- code starts here
Function WhichOnes() As String
'Jim Cone - Portland Oregon - May 2008
On Error GoTo NoOne
Dim N As Long
Dim C As Long
Dim rCell As Range
Dim rCaller As Range
Dim strList As String

Application.Volatile
Set rCaller = Application.Caller
N = rCaller.Row
C = rCaller.Column - 1
If C < 2 Then
WhichOnes = "Wrong Column"
Else
For Each rCell In Range(Cells(N, 2), Cells(N, C))
If Len(rCell) Then
strList = strList & " " & rCell.Offset(-N + 1, 0).Value
End If
Next
WhichOnes = strList
End If
Set rCell = Nothing
Set rCaller = Nothing
Exit Function

NoOne:
WhichOnes = "Error " & Err.Number
End Function
'-------- code ends here

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





"sailingHLA"
<[email protected]>
wrote in message
I'm a little stuck. I have a table with gene names as column headers and
patient samples as the row headers. In our lab testing, each patient sample
will have at least one (many times more) genes that have a numerical value
associated with it. For example, sample A987 may have a result in the table
under gene A, gene D and gene L. All other cells for which a patient does
not have a sepcific gene is blank. I need to find a function that can look
at the table in the row for sample A987 and tell me which genes the patient
has (i.e. the column headers of gene A, gene B, etc.). If it is possible to
have each gene listed as a value in one cell all in the same column, that
would be great. Thanks in advance for any help.