Macro it's very Slow ....

L

leo_nunez

Hello! (sorry be me english)

I found this interesting web page and it's very amazing!. Okey here m
question ... :) (thank you for your time).

I have a excel workbook where i have a data like this.

Month---Year
08 2004
09 2004
10 2004
....................
08 2005
09 2005
10 2005
....................
08 2006
09 2006
10 2006
...................

Me question is ... if X=08 (Month) i need to find this occurrences. Th
result must be :
08 2004
08 2005
08 2006

Ok. I do this via excel macro and this must work (today it's very slo
...) , but there is a excel formula can do this ?. Like a VLOOKUP
HLOOKUP by example or another ?

Thank you for your help and time !

Bye
 
T

Tom Ogilvy

=Index($B$1:$B$100,Small(if($A$1:$A$100="08",row($A$1:$A$100)),row(A1))

Enter with ctrl+Shift+enter rather than just enter and then drag fill down
until it starts returning errors.
 
T

Tom Ogilvy

left off the final parentheses:

=INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100="08",ROW($A$1:$A$100)),ROW(A1)))

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
=Index($B$1:$B$100,Small(if($A$1:$A$100="08",row($A$1:$A$100)),row(A1))

Enter with ctrl+Shift+enter rather than just enter and then drag fill down
until it starts returning errors.
Click to expand...
 
L

leo_nunez

A last problem. I have a Spanish Excel. How i can translate the formul
to spanish ?

INDEX = INDICE (EQV SPANISH)
SMALL = ????
ROW=FILA (EQV SPANISH)

THANK YOU
 
T

Tom Ogilvy

Sub MakeFormula()
sform = "=INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100"
sform = sform & "=""08"",ROW($A$1:$A$100)),ROW(A1)))"
ActiveCell.formulaArray = sForm
End Sub

select the cell where you want the formula and run the macro. Excel should
translate it for you.
 
Top