Return information by searchword

[Oeysbrei]

Hi,

I have attached a samplefile, but I will try to explane here what I will achieve.

In B2 there is a scrolldownlist from collumn G. When searching in this list I want all the data that contains the same searchword in area J2 to N100 to be returned to A5 to E100


Hope someone can help me find write a macro to help me solve this

Thanks in advance


Best regards
Øystein

[Glenn Kennedy]

Is a formula any ise to you?

=IF($B$2="","",IFERROR(INDEX(J:J,AGGREGATE(15,6,ROW($J$2:$J$20)/($J$2:$J$20=$B$2),ROWS(A$5:A5))),""))

in B2, copied across and down.

[kvsrinivasamurthy]

Formula will do.
In A5 copied to full range.

=IFERROR(INDEX(J$2:J$11,AGGREGATE(15,6,ROW($J$2:$J$100)/($J$2:$J$100=$B$2),ROWS(A$5:A5))-ROW($J$1)),"")

Format columns suitably for date and currency.

[Oeysbrei]

Hi,

Thanks for youre reply
After working with the formula I made it work perfect between sheets. What I dont understand is the part that says "AGGREGATE(15,6,".
Can you explaine this part?

Regards
Øystein

[kvsrinivasamurthy]

AGGREGATE(15,6,

Aggregate is function to get result from the array comes after 15,6,

15 for selecting the small value from the array.

6 is to avoid error values in the array.