Another Sum Question
 

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

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


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

It's still only counting one ID per cell.


--
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

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


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

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


--
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

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

test it and let me know


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

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

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


--
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

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


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

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?


--
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 added code that will prompt you with option to enter which column to
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 OR
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 Sub


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