PDA

View Full Version : Lookup column value and find value



Tom Jones
03-22-2014, 11:26 AM
.

SamT
03-23-2014, 07:04 AM
What?

http://ts3.mm.bing.net/th?id=HN.607990304877710246&pid=15.1&H=138&W=160

Chris Macro
03-24-2014, 01:35 PM
What?

http://ts3.mm.bing.net/th?id=HN.607990304877710246&pid=15.1&H=138&W=160

lol!

D_Marcel
03-24-2014, 02:36 PM
What?

http://ts3.mm.bing.net/th?id=HN.607990304877710246&pid=15.1&H=138&W=160


KKK

Tom Jones
03-24-2014, 04:36 PM
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
03-24-2014, 06:35 PM
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.

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

> 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
03-25-2014, 01:24 AM
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
03-25-2014, 07:06 AM
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.