Lookup column value and find value

[Tom Jones]

.

[SamT]

What?

[Chris Macro]

What?

lol!

[D_Marcel]

What?

KKK

[Tom Jones]

Hello,

Sorry I have not posted what I wanted but I thought I found an answer but unfortunately I failed.
If not found in the crystal response to my request :lol then please help me in the following problem:
In Sheet2 I have a database of sales per month in January 2014.
I would like that depending on the name chosen in B2 to display top 10 sales in E2: E11 and in D2: D11 day for these sales.

I attached a file and to see how is the data.
Thank you.

[D_Marcel]

Some suggestions:

> Using the .Find method, get the corresponding address of the cell "B2" in the Sheet2.
> Using a For To structure (loop), read all the values and respective dates, inserting them into a bi-dimensional array. I'm not sure if you can insert into the same array, dates and numbers.

[VBA]Set WorkArea = Sheet2.Range(Cells(2, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))
Set Target = WorkArea.Find(Sheet1.Cells(2, 2).Value)


For Y = 2 To 31
ReDim Preserve MyArray(Y)
MyArray(Y) = Sheet2.Cells(Target.Row, Y).Value
Next Y[/VBA]

> Load the values of your bi-dimensional array in the corresponding cells of 'Date' and 'Sales' using another loop with the commands UBound and LBound.

Good luck.

Douglas

[Tom Jones]

Hi Douglas,

Thanks for the reply. Sorry, do not know to program in VBA. If you can help me with complete code would be great, otherwise I will try to solve the problem using formulas.
If someone can help me with formulas would be great. Thanks.

[D_Marcel]

Guess this can't be done with formulae, I don't know a way to get the second, third, fourth... max values of a range. Perhaps we can use HLOOKUP to get the date acording to the value, but if you have a number that repeats, will be a problem because the formula will return the same date.

I'll try to think in something, but keep researching and try you also.