+ Reply to Thread
Results 1 to 7 of 7

Earliest and latest years based upon name of a person

  1. #1
    Registered User
    Join Date
    05-11-2020
    Location
    England
    Posts
    3

    Earliest and latest years based upon name of a person

    Hello all

    I have a spreadsheet with people and years.

    Column A is the year
    Column B is the name of the person
    Column C is where I would like the earliest year that person appears in the spreadsheet
    Column D is where I would like the latest year that person appears in the spreadsheet

    (There are other columns which aren't relevant to this query)

    For example
    John Smith could be in the spreadsheet 50 times from 1950 to 2000 - I would like C to show 1950 and D 2000
    Jane Dow could be in the spreadsheet 5 times from 2010 to 2015 - I would like C to show 2010 and D 2015

    Can you help?

    Thank you in advance.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,674

    Re: Earliest and latest years based upon name of a person

    Welcome to the forum.

    This calls for a MAXIFS or MAX(IF(...)), depending on which version of Excel you have. Please tell us and add it to your forum profile.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    568

    Re: Earliest and latest years based upon name of a person

    Just search for minif and maxif

  4. #4
    Registered User
    Join Date
    05-11-2020
    Location
    England
    Posts
    3

    Re: Earliest and latest years based upon name of a person

    Office 2019

    I can't work out how I get it to change per person. When I use Max and Min it defaults to the earliest and latest dates regardless of each person.

    I know I am missing something obvious. There are approx 1000 people in there some with quite a number of years, others with just one.

    Thank you.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,674

    Re: Earliest and latest years based upon name of a person

    MINIFS and MAXIFS allow you to specify criteria in the same way as SUMIFS and COUNTIFS. Give it a go.

    https://support.office.com/en-gb/art...b-9b6376b28883

    Please update your forum profile as requested earlier.
    Last edited by AliGW; 05-11-2020 at 04:22 PM.

  6. #6
    Registered User
    Join Date
    05-11-2020
    Location
    England
    Posts
    3

    Re: Earliest and latest years based upon name of a person

    Sorry they seem to suggest I need to have another column with the names in so it can cross reference, but I don't want to do that.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,674

    Re: Earliest and latest years based upon name of a person

    No, you refer to columns A and B.

    Something like this:

    =MAXIFS(A2:A500,B2:B500,B2)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to locate earliest and latest date
    By thedunna in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-09-2019, 03:14 PM
  2. Find earliest and latest time based on specific date
    By klturi421 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-05-2019, 11:20 PM
  3. Help with sorting dates from earliest to latest
    By probuddha in forum Excel General
    Replies: 11
    Last Post: 06-15-2015, 03:53 PM
  4. [SOLVED] Identifying earliest and latest times
    By Ron Purpura in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-13-2014, 10:56 AM
  5. Duplicate Based on Multi-Coulmn - Only Keep the Earliest and Latest Value
    By jturner12 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-19-2014, 02:56 AM
  6. Looking Up Earliest and Latest Time Values
    By warhead92100 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-25-2013, 06:07 AM
  7. same person, different dates...need the latest date from every person
    By Tanisman in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-26-2011, 07:16 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1