Question

  • Creator
    Topic
  • #2171037

    formula in excel that can find the last occurrence of each item in a list

    Locked

    by ding_gi ·

    I need a formula that can find the last occurrence of each item in a list.
    Scenario:
    Column A(Products) Column B(Date of Arrival)
    Socks————————————Sept 2
    Shoes————————————Sept 3
    Hats————————————–Sept 3
    Shoes————————————Sept 4
    Shoes————————————
    Shirts————————————–

    I need to get via excel formula, the latest Date of arrival per item say Sept 2 for “Socks”, Sept 3 for “Hats”, and Sept 4 for “Shoes”. Please note that “Shoes” here has 2 Date of Arrivals as compared to “Socks” and “Hats” that have only one Date of Arrival for each. If you will notice, “Shoes” is listed 3 times wherein the last one has blank Date of Arrival. Also, “Shirts” is listed but it has blank Date of Arrival. I need then a formula that will return the desired latest Date of arrivals as I indicated above and will just return blank for the “Shirts” since it has no Date of Arrival. To summarize the desired result:

    Product Latest Date of Arrival
    Socks———————-Sept 2
    Shoes———————-Sept 4
    Hats————————Sept 3
    Shirts———————-[Blank]

    Please help. Thanks. .

All Answers

  • Author
    Replies
    • #2887664

      Clarifications

      by ding_gi ·

      In reply to formula in excel that can find the last occurrence of each item in a list

      Clarifications

    • #2887655

      Array Version

      by dogknees ·

      In reply to formula in excel that can find the last occurrence of each item in a list

      Assuming the first column is in B4:B14, the second column is in C4:C14 and you list of all products in in E4:E10.

      In cell F4 enter the following formula. It is an array formula, so you need to enter it, then hold down Shift and Ctrl while you press Enter (Called CSE). If you get it right, the formula will be displayed with curly brackets {}. You can’t enter the curly brackets, Excel puts them there when you hit “CSE”.

      =INDEX($C$4:$C$14,MAX(IF($B$4:$B$14=E4,ROW($B$4:$B$14)-3,-1)))

      Copy the formula down to cell F10. If there is no entry, it will show an error, otherwise it will show the last value next each item.

      • #2887616

        Reponse To Answer

        by swtrader ·

        In reply to Array Version

        Interesting. Will you please explain what’s happening in the formula?

      • #2887600

        Reponse To Answer

        by ding_gi ·

        In reply to Array Version

        I followed your instructions but I am getting these results:

        Socks 2-Sep Socks 2-Sep
        Shoes 3-Sep Shoes 2-Sep
        Hats 4-Sep Hats 2-Sep
        Shoes 4-Sep Shirts #VALUE!
        Shoes #VALUE!
        Shirts #VALUE!
        2-Sep
        One thing I noticed is that, the formula consistently returns 2-Sep for all the products which should not be as they have different Dates of Arrival…. Please advise… Thanks a lot..

Viewing 1 reply thread