Finding the last entry of a particular type in a list

Roccofan

Board Regular
Joined
Apr 18, 2002
Messages
61
Hello,

I'm trying to find an efficient (any) way of find the last entry of a particular kind in a list of entries.

Example: you have 100 rows listing the purchases of 5 differenct car colors. What I'm trying to do is find a formula or VBA code that will return the cell that contains the last entry that was made for a "red" car. You can assume the car color is the first cell in the row.

Thank you in advance.

RF
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
On 2002-09-19 11:34, Roccofan wrote:
Hello,

I'm trying to find an efficient (any) way of find the last entry of a particular kind in a list of entries.

Example: you have 100 rows listing the purchases of 5 differenct car colors. What I'm trying to do is find a formula or VBA code that will return the cell that contains the last entry that was made for a "red" car. You can assume the car color is the first cell in the row.

Thank you in advance.

RF

Do you mean you have a vertical range like this:

blue
5
pink
7
blue
10
red
20
red
12

that is, in a single column?
 

Roccofan

Board Regular
Joined
Apr 18, 2002
Messages
61
Hi Aladin,

No. The data looks like this.

1 Blue 2002 4DR
2 Green 1999 2DR
4 Yellow 2002 3DR
5 Blue 2002 4DR
6 Gold 2002 3DR

What I need is the cell reference for that last blue entry. Lookups don't help because they need to data in ascending or decending order. I've used an INDEX/MATCH combination in the past, but I can't remember if that instance is applicable to this situation, plus that model is at home.

Thanks.

RF
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Book1
ABCDEFGHI
1ColorRowColor
2Red2Red
3Blue3
4Red47isthelastrow
5Green5A7isthecelladdress
6Yellow6Redisthevalue
7Red7
8Yellow8
9Blue9
10Yellow10
11Yellow11
12
Sheet3

This message was edited by Mark W. on 2002-09-19 11:49
 

Roccofan

Board Regular
Joined
Apr 18, 2002
Messages
61
Mark and Aladin,

Thank you both for your help.

Mark W.: You have NO idea what a tremendous help you've been to me over the past couple of years.

Thank you again.

RF
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
On 2002-09-19 11:44, Roccofan wrote:
Hi Aladin,

No. The data looks like this.

1 Blue 2002 4DR
2 Green 1999 2DR
4 Yellow 2002 3DR
5 Blue 2002 4DR
6 Gold 2002 3DR

What I need is the cell reference for that last blue entry. Lookups don't help because they need to data in ascending or decending order. I've used an INDEX/MATCH combination in the past, but I can't remember if that instance is applicable to this situation, plus that model is at home.

Thanks.

RF

Also worth considering...

=ADDRESS(MAX((Data=D1)*(ROW(Data))),1,4)

to be array-entered.

Data refers to your data range.
Book6
ABCDE
1ColorYearCodeBlueA5
2Blue20024DRA5
3Green19992DR
4Yellow20023DR
5Blue20024DR
6Gold20023DR
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,328
Members
414,444
Latest member
lionking15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top