Find Data from oldest date

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Hi, I have had a look around online but can't seem to find what I want.

I have devices in one column and dates received in another. I do not wish to reorder the column as they are set up in device order.

I want to find which device was received the earliest.

Please can someone advise the formula for this? Column A for device and L for date received.

Thank you :)
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,996
If the dates are in the left column you can use a V-lookup. something like :
Code:
=VLOOKUP(MIN(B2:B10),B2:C10,2,FALSE)
For the above I had dates in B2:B10 and serial Nos in C2:10. Here, the vlookup looks up the smallest date and returns the next column
 

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Hi, Thanks for this.

I have device in column A and I have time in stock in column M - is there a way to manipulate this to get the highest value for time in stock to render the device?

Thanks - not very good at VLOOKUP.
 

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Just to update - had a quick read up on basics of VLOOKUP and I saw that the lookup has to be in the left most column. Jiggled my data around and sorted it :)
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,996
Ahh good news. I was just searching how to do it formulaic (not my strong point) using offset and index/match.

Got tied up in knots and couldn't quite get it. Happy you've fixed it!
 

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Is there a simple way to modify it so that data from columns 1,2,3 and 10 are pulled into a string? So the one that has been here longest shows device, colour, memory and network all in one cell? Thanks
 

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Managed to do it - I feel this could be neater but as they say, if it's not broke, don't fix it:

Code:
=CONCATENATE(VLOOKUP(MAX(Shipped!N:N),Shipped!N:N:Shipped!P:P,3,FALSE)," ",(VLOOKUP(MAX(Shipped!N:N),Shipped!N:N:Shipped!Q:Q,4,FALSE))," ",(VLOOKUP(MAX(Shipped!N:N),Shipped!N:N:Shipped!R:R,5,FALSE))," ",(VLOOKUP(MAX(Shipped!N:N),Shipped!N:N:Shipped!Y:Y,12,FALSE)))
 

Watch MrExcel Video

Forum statistics

Threads
1,101,745
Messages
5,482,605
Members
407,353
Latest member
yahor

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top