VLOOKUP first numerical value (not text)

[titushanke]

Dear all, I have this situation:

RATENAME | DAY1 | DAY2 | DAY3 | DAY4
myrate | 200 | 200 | | |
myrate | | | 250 | 250 |
... and so on

in a separte worksheet I would like to have:

RATENAME | DAY1 | DAY2 | DAY3 | DAY4
myrate | 200 | 200 | 250 | 250 |
.... and so on

so basically compressing the list in the first worksheet to one row i
second worksheet.

My problem is that vlookup only looks up the first result it finds
even if the field is empty (see screenshots). I would instead need i
to find the first numerical value, as I am only itnerested in this.

This is the vlookup I am currently using:

=IF(ISERROR(VLOOKUP($A5,EFI!$B$3:$GK$1500,23,FALSE)),0,VLOOKUP($A5,EFI!$B$3:$GK$1500,23,FALSE))

Thank you for any help you can give me..

Titus

+-------------------------------------------------------------------
|Filename: excel-problem2.jpg
|Download: http://www.excelforum.com/attachment.php?postid=2996
+-------------------------------------------------------------------

 

[m_anoob]

hi there,
u can use the subtotal function at each change in "myrate" - end result
u get a subtotal for all the coloumns. now click on all the minus sign
on the left of the sheet. now u get onlyu the sub totals. now use
cntrl+g click on special . n select visible cells now. now paste it
another sheet.. n replace the word total with null..