tough lookup question

[Guest]

I think this is complicated but here goes:

I have a spreadsheet that looks like this:

col1 Col2 Col3

bunch of junk
more junk
NUMBER #1234

firstname lastname


This repeats over and over. What I would like to do is populate col3, when
col2 is not null, with the value of the closest previous row that has NUMBER
#9999. Does anyone know a formula or code to do this?

Thanks in advance.

 

[Guest]

.... and the result from your example would be ..... ??

col1 Col2 Col3

bunch of junk
more junk
NUMBER #1234

firstname lastname 1234

 

[Guest]

Yes exactly. But in case I didn't explain it well enough, there are many
columns that have last and first names and I want to fill them all with the
nearest previous NUMBER #999. Do you know how to do this?

Thanks in advance.

 

[Guest]

I am sure VBA will be required to do this: I don't have a formula solution
but that doesn't mean there isn't one!

 

[Guest]

I may have a solution but I would like to try it on a good sample of data.
Can you send a w/book to:

toppers <at> NOSPAMjohntopley.fsnet.co.uk

Remove NOSPAM

 

[Guest]

Oops, I tried to send it but it came back undeliverable? I used:

(e-mail address removed)?

Thanks,

 

[Guest]

Thanks, I'm sending it over.

I may have a solution but I would like to try it on a good sample of data.
Can you send a w/book to:

toppers <at> NOSPAMjohntopley.fsnet.co.uk

Remove NOSPAM

 

[Guest]

yes ... should be OK

Oops, I tried to send it but it came back undeliverable? I used:

(e-mail address removed)?

Thanks,

 

[Guest]

It says "Destination not valid with DNS." Can you receive an Excel file or
should I zip it?

 

[Guest]

I have received both before without any problem. If you continue to have
problems, post your address and I'll contact you!