Question

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

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 3Hats--------------------------------------Sept 3Shoes------------------------------------Sept 4Shoes------------------------------------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:

All Answers

Array Version

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.

Reponse To Answer

Socks 2-Sep Socks 2-SepShoes 3-Sep Shoes 2-SepHats 4-Sep Hats 2-SepShoes 4-Sep Shirts #VALUE!Shoes #VALUE!Shirts #VALUE! 2-SepOne 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..

Create a new discussion

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

Post type

Subject title

Topic Tags

Select up to 3 tags (1 tag required)

Cloud

Piracy

Security

Apple

Microsoft

IT Employment

Google

Open Source

Mobility

Social Enterprise

Community

Smartphones

Operating Systems

Windows

Mac

Malware

Tablets

Networking

Browser

Hardware

Software

Web Developerment

Linux

Off Topic

Message Body

Track this discussion and email me when there are updates

Please note: Do not post advertisements, offensive material, profanity, or personal attacks. Please remember to be considerate of other members. If you're new to the TechRepublic Forums, please read our TechRepublic Forums FAQ. All submitted content is subject to our Terms Of Use.