Find Data from oldest date
Results 1 to 7 of 7

Thread: Find Data from oldest date
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find Data from oldest date

    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

  2. #2
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,869
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find Data from oldest date

    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

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  3. #3
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Data from oldest date

    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.

  4. #4
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Data from oldest date

    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

  5. #5
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,869
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find Data from oldest date

    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!

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  6. #6
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Data from oldest date

    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

  7. #7
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Data from oldest date

    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)))

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •