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

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

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

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

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

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

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