can this formula be coded?
 

I'm using Excel 2007 and s/s is 325501 rows deep. It consists of series
of ranges between 4 and 30 rows deep.

What I want to do is locate the next appearance of a name and copy its
accompanying number.
Doing this manully is not feasible, given the large size of the s/s .

I have the following formula but have had it running for 5 hours now
and it is only down to row 22300:


Code:
--------------------

=IF(C3="","",IF(ISNUMBER(MATCH(1,(C4:$C$140=C3)*(L 4:$L$1400),0)),INDEX(L4:$L$140,MATCH(1,(C4:$C$140 =C3)*(L4:$L$1400),0)),""))

--------------------


Can anyone offer anything quicker or code??

I enclose a small attachment showing what I am trying to achieve but
for those who don't like opening attachments the wording in it is :

The desired objective is to place in column Q the next appearing number
in column L of the name in column C.
The VLOOKUP formula in column Q presents the desired number but
(problem!) presents a zero when next appearance = blank.
When this happens I want the formula/code to repeatedly lookup the next
appearance until it finds a number.
Examples of where next numbers appear are given here in column R.


If anyone can help me to this end I would be most grateful.
Big thanks.
Colwyn.


+-------------------------------------------------------------------+
|Filename: example.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=60|
+-------------------------------------------------------------------+

--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47019

can this formula be coded?
 

I can easily be coded in a macro in the formula is correct. First make sure
you formula is corrrect by reviewing the formula using the Efvaluate formula
function.

Tools - formula Auditing - Evaluate Formula.

You are first checking if C3 has a Comma "","" then comparing C3 with
C4:$C$140. Does this make sense?

"colwyn" wrote:


I'm using Excel 2007 and s/s is 325501 rows deep. It consists of series
of ranges between 4 and 30 rows deep.

What I want to do is locate the next appearance of a name and copy its
accompanying number.
Doing this manully is not feasible, given the large size of the s/s .

I have the following formula but have had it running for 5 hours now
and it is only down to row 22300:


Code:
--------------------

=IF(C3="","",IF(ISNUMBER(MATCH(1,(C4:$C$140=C3)*(L 4:$L$1400),0)),INDEX(L4:$L$140,MATCH(1,(C4:$C$140 =C3)*(L4:$L$1400),0)),""))

--------------------


Can anyone offer anything quicker or code??

I enclose a small attachment showing what I am trying to achieve but
for those who don't like opening attachments the wording in it is :

The desired objective is to place in column Q the next appearing number
in column L of the name in column C.
The VLOOKUP formula in column Q presents the desired number but
(problem!) presents a zero when next appearance = blank.
When this happens I want the formula/code to repeatedly lookup the next
appearance until it finds a number.
Examples of where next numbers appear are given here in column R.


If anyone can help me to this end I would be most grateful.
Big thanks.
Colwyn.


+-------------------------------------------------------------------+
|Filename: example.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=60|
+-------------------------------------------------------------------+

--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47019

can this formula be quickened or coded?
 

Joel Guest - thanks.

I've taken out the first part where C3 checks for comma and have also
put the formula in braces. The rest is correct and any help you can give
will be greatly appreciated.

The formula now reads:

Code:
--------------------

{=IF(ISNUMBER(MATCH(1,(C4:$C$140=C3)*(L4:$L$1400) ,0)),INDEX(L4:$L$140,MATCH(1,(C4:$C$140=C3)*(L4:$L $1400),0)),"")}

--------------------


Big thanks.
Colwyn.


--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47019

can this formula be quickened or coded?
 

the code will look something like the code below. I'm putting the Next
Number in column S since you didn't specify.

for Row 3 the code will produce this statement
Set c = Range("C4:C325501").Find(what:=Range("C3"), _
LookIn:=xlValues, lookat:=xlWhole)

for Row 4 the code will produce this statement
Set c = Range("C5:C325501").Find(what:=Range("C4"), _
LookIn:=xlValues, lookat:=xlWhole)


Sub GetNext()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 3 To (LastRow - 1)
Data = Range("C" & RowCount)
Set c = Range("C" & (RowCount + 1) & ":C" & LastRow).Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Num = Range("L" & c.Row)
Range("S" & RowCount) = Num
End If

Next RowCount

End Sub


"colwyn" wrote:


Joel Guest - thanks.

I've taken out the first part where C3 checks for comma and have also
put the formula in braces. The rest is correct and any help you can give
will be greatly appreciated.

The formula now reads:

Code:
--------------------

{=IF(ISNUMBER(MATCH(1,(C4:$C$140=C3)*(L4:$L$1400) ,0)),INDEX(L4:$L$140,MATCH(1,(C4:$C$140=C3)*(L4:$L $1400),0)),"")}

--------------------


Big thanks.
Colwyn.


--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47019

can this formula be quickened or coded?
 

Joel Guest- thanks. That's very good and full appreciation for your
efforts.

Problem with it is: it doesn't provide the next value when there is an
occasion where a name occurs without a value but later does so.
For instance, a name mayoccur on row 1, re-occur on row 20 without a
value and again on row 40 with a value.
I need row 1 to retrieve the value on row 40.

I'll enclose attachment with your code and its product as it will
better illustrate.
Big thanks.
Colwyn.


+-------------------------------------------------------------------+
|Filename: example 2.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=61|
+-------------------------------------------------------------------+

--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47019

can this formula be quickened or coded?
 

try these changes

Sub GetNext()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 3 To (LastRow - 1)
Data = Range("C" & RowCount)
With Range("C" & (RowCount + 1) & ":C" & LastRow)
Set c = .Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Num = Range("L" & c.Row)
Do While Num = "" And _
Not c Is Nothing And _
c.Row 3

Set c = .FindNext()
Num = Range("L" & c.Row)
Loop

Range("S" & RowCount) = Num
End If
End With
Next RowCount

End Sub

"colwyn" wrote:


Joel Guest- thanks. That's very good and full appreciation for your
efforts.

Problem with it is: it doesn't provide the next value when there is an
occasion where a name occurs without a value but later does so.
For instance, a name mayoccur on row 1, re-occur on row 20 without a
value and again on row 40 with a value.
I need row 1 to retrieve the value on row 40.

I'll enclose attachment with your code and its product as it will
better illustrate.
Big thanks.
Colwyn.


+-------------------------------------------------------------------+
|Filename: example 2.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=61|
+-------------------------------------------------------------------+

--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47019

can this formula be quickened or coded?
 

I needed to add C to the findnext

Sub GetNext()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 3 To (LastRow - 1)
Data = Range("C" & RowCount)
With Range("C" & (RowCount + 1) & ":C" & LastRow)
Set c = .Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Num = Range("L" & c.Row)
Do While Num = "" And _
Not c Is Nothing And _
c.Row 3

Set c = .FindNext(after:=c)
Num = Range("L" & c.Row)
Loop

Range("S" & RowCount) = Num
End If
End With
Next RowCount

End Sub

"colwyn" wrote:


Joel Guest- thanks. That's very good and full appreciation for your
efforts.

Problem with it is: it doesn't provide the next value when there is an
occasion where a name occurs without a value but later does so.
For instance, a name mayoccur on row 1, re-occur on row 20 without a
value and again on row 40 with a value.
I need row 1 to retrieve the value on row 40.

I'll enclose attachment with your code and its product as it will
better illustrate.
Big thanks.
Colwyn.


+-------------------------------------------------------------------+
|Filename: example 2.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=61|
+-------------------------------------------------------------------+

--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47019

can this formula be quickened or coded?
 

Joel Guest - thanks. Still not working. It works on the first range then
freezes. On 'debug' it highlights the line "Num = Range("L" & c.Row)"

???
Can it be fixed?
Big thanks.
Colwyn.


--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47019

can this formula be quickened or coded?
 

I fixed the problem. the reason the code hung was if you were on row 10 and
the data was 8 in column C the macro would find 8 also in row 20 and 30 but
column L was empty in both cases. the code would get hung in a loop finding
8 in row 20, then 30, then 20, then 30 and never getting out of the code.
The problem with find and find next is it wraps and when it gets to the last
row goes back to the 1st row in the search field.


The code right now wraps that if it doesn't find the number at a row higher
than then current row it goes back to row 3 and searches until it gets to the
current row. So the result is it will put the data from column L on a lower
row instead of a higher row If you don't want it to wrap and put and leave a
blank if it doesn't find the number in a higher row then make this change

from:
Range("S" & RowCount) = Num
to:
if c.Row RowCount then
Range("S" & RowCount) = Num
end if

Sub GetNext()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 3 To (LastRow - 1)
Data = Range("C" & RowCount)
With Range("C3:C" & LastRow)
Set c = .Find(what:=Data, after:=Range("C" & RowCount), _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Num = Range("L" & c.Row)
Do While Num = "" And _
Not c Is Nothing And _
c.Row RowCount

Set c = .FindNext(after:=c)
Num = Range("L" & c.Row)
Loop

Range("S" & RowCount) = Num
End If
End With
Next RowCount

End Sub


"colwyn" wrote:


Joel Guest - thanks. Still not working. It works on the first range then
freezes. On 'debug' it highlights the line "Num = Range("L" & c.Row)"

???
Can it be fixed?
Big thanks.
Colwyn.


--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47019

can this formula be quickened or coded?
 

Crossposting
You have crossposted 'here'
(http://www.mrexcel.com/forum/showthread.php?t=362455)


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47019