Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default vlookup/match to find column name

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 287
Default vlookup/match to find column name

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/



"sailingHLA" wrote:

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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,549
Default vlookup/match to find column name

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"

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return alternate value if VLookup can't find match SueJB Excel Worksheet Functions 7 January 5th 06 09:30 AM
find all cells that match and use in an index/vlookup Adam Bell Excel Discussion (Misc queries) 1 October 21st 05 06:41 PM
Find a not exact match using vlookup Russ B Excel Discussion (Misc queries) 1 July 27th 05 08:49 PM
using vlookup to find exact match Janice Lee via OfficeKB.com Excel Discussion (Misc queries) 3 March 25th 05 02:03 AM
find LAST match in column Pantryman Excel Worksheet Functions 1 November 5th 04 04:05 PM


All times are GMT +1. The time now is 12:38 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"