I am using a rather large genomic data set in which I need to classify
certain gene functions as one of three things. Each gene (row) has
several ID numbers associated with it. For instance one row could look
like "GO:0005524,GO:0000074,GO:0005730." I need excel to find a match,
and then add one to an adjacent column (basically tallying). For
instance, if I search for "GO:0005524" I need excel to search the 1001
rows containing this information, and then when it finds a row with
"GO:0005524" to add one to the adjacent column while staying within the
same row. That is, I don't just want a sum of how often "GO:0005524"
occurs in the whole set, I need to know if it occurs in a cell, and if
so for an adjacent cell to increase by one.

I tried to write a formula for this but didn't have much luck because
all of the MATCH, LOOKUP, etc. functions weren't really appropriate. I
also tried to use a macro, but because I don't really know Visual Basic
I couldn't edit it properly. Any help on this problem would be greatly
appreciated.


--
Erin Shellman
------------------------------------------------------------------------
Erin Shellman's Profile: http://www.excelforum.com/member.php...o&userid=24417
View this thread: http://www.excelforum.com/showthread...hreadid=380164

I am assuming that column A as the id number COLUMN , which we need to
search and column b is the adjacent column which need to be incremented
when ever we find a match.


try this macro and let me know

Sub Macro1()
Dim INIT, I As Variant
I = 0
Columns("A:A").Select 'column a for serching
Selection.Find(What:="GO:0005524", After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate 'searching a id
INIT = ActiveCell.Address
Range(INIT).Offset(0, 1).Value = Range(INIT).Offset(0, 1).Value + 1
'incrementing adjacent column
While I = 0
Selection.FindNext(After:=ActiveCell).Activate
If ActiveCell.Address = INIT Then
I = 1
Else
Range(ActiveCell.Address).Offset(0, 1).Value =
Range(ActiveCell.Address).Offset(0, 1).Value + 1
End If
Wend
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380164

Awesome! It works! Thanks a lot. What would I have to change if I
wanted it also to add to other rows? I am classifying those IDs as one
of three things which fall into one three columns. I see how I change
what it is searching for, but how can I tell it to not tally in the
column directly adjacent, but rather in a column two or three away?


--
Erin Shellman
------------------------------------------------------------------------
Erin Shellman's Profile: http://www.excelforum.com/member.php...o&userid=24417
View this thread: http://www.excelforum.com/showthread...hreadid=380164

Oh, I found one problem with that macro. It doesn't count multiple
occurances of the ID. Some of the IDs have numbers that show up in a
cell more than once, and need to be counted more than once. Is that
easy to fix?


--
Erin Shellman
------------------------------------------------------------------------
Erin Shellman's Profile: http://www.excelforum.com/member.php...o&userid=24417
View this thread: http://www.excelforum.com/showthread...hreadid=380164

You mean in one cell you have value like "GO:0005524 GO:0005524
GO:0005524" ,is that what you mean.


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380164

That's right. That happens sometimes.


--
Erin Shellman
------------------------------------------------------------------------
Erin Shellman's Profile: http://www.excelforum.com/member.php...o&userid=24417
View this thread: http://www.excelforum.com/showthread...hreadid=380164