Another Sum Question

[Erin Shellman]

I am using a rather large genomic data set in which I need to classif
certain gene functions as one of three things. Each gene (row) ha
several ID numbers associated with it. For instance one row could loo
like "GO:0005524,GO:0000074,GO:0005730." I need excel to find a match
and then add one to an adjacent column (basically tallying). Fo
instance, if I search for "GO:0005524" I need excel to search the 100
rows containing this information, and then when it finds a row wit
"GO:0005524" to add one to the adjacent column while staying within th
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 i
so for an adjacent cell to increase by one.

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

 

[anilsolipuram]

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

 

[Erin Shellman]

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

 

[Erin Shellman]

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

 

[anilsolipuram]

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

 

[Erin Shellman]

That's right. That happens sometimes

 

[anilsolipuram]

let me know if this macro solves the problem


Sub Macro1()
Dim INIT, I, t, incr 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
t = Split(ActiveCell.Value, "GO:0005524")
Range(INIT).Offset(0, 1).Value = Range(INIT).Offset(0, 1).Value +
UBound(t) 'incrementing adjacent column
While I = 0
Selection.FindNext(After:=ActiveCell).Activate
If ActiveCell.Address = INIT Then
I = 1
Else
t = Split(ActiveCell.Value, "GO:0005524")
Range(ActiveCell.Address).Offset(0, 1).Value =
Range(ActiveCell.Address).Offset(0, 1).Value + UBound(t)
End If
Wend
End Sub

 

[Erin Shellman]

It's still only counting one ID per cell

 

[anilsolipuram]

How do you know it's counting only 1 ID per cell,is adjacent incremente
by only 1 when there is multiple occurence of ID in a cel

 

[Erin Shellman]

Yes, that's how I can tell. I just look at the first cell with multipl
occurances of the ID it is searching for and it only writes 1 rathe
than the true number in the cell

 

[anilsolipuram]

Can you download this zip file with excel file, execute the macro an
see whether it works. It is working for me.

test it and let me kno

+-------------------------------------------------------------------
|Filename: test.zip
|Download: http://www.excelforum.com/attachment.php?postid=3511
+-------------------------------------------------------------------

 

[Erin Shellman]

Yeah, that worked for me too. When I was testing it on the real data
switched out the "GO:0005524" code with another number but I didn'
change anything else. Would that have anything to do with it

 

[anilsolipuram]

the below will prompt for ID, instead of hardcoding in the macro code.

try this and let me know

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

 

[Erin Shellman]

It works with no issues! Thanks a lot! What part of the macro would I
change if I needed the tallying column to be something besides B:B?

 

[anilsolipuram]

I added code that will prompt you with option to enter which column t
search like A OR B OR C, enter just the column name.


Sub Macro1()
Dim INIT,col, I, t, incr, id As Variant
I = 0
id = InputBox("Enter the id to find")
col = InputBox("Enter which column to search eg: A OR B OR C O
D.....")
Columns(col & ":" & col).Select 'column a for serching
on error goto a:
Selection.Find(What:=id, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate 'searching a id
INIT = ActiveCell.Address
t = Split(ActiveCell.Value, id)
Range(INIT).Offset(0, 1).Value = Range(INIT).Offset(0, 1).Value
UBound(t) 'incrementing adjacent column
While I = 0
Selection.FindNext(After:=ActiveCell).Activate
If ActiveCell.Address = INIT Then
I = 1
Else
t = Split(ActiveCell.Value, id)
Range(ActiveCell.Address).Offset(0, 1).Value
Range(ActiveCell.Address).Offset(0, 1).Value + UBound(t)
End If
Wend
a:
if err.description<>"" then
msgbox "no match found"
end if
End Su