# Finding the last entry of a particular type in a list

#### Roccofan

##### Board Regular
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.

RF

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.

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?

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

Book1
ABCDEFGHI
1ColorRowColor
2Red2Red
3Blue3
4Red47isthelastrow
6Yellow6Redisthevalue
7Red7
8Yellow8
9Blue9
10Yellow10
11Yellow11
12
Sheet3

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

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

On 2002-09-19 11:44, Roccofan wrote:

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

to be array-entered.

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

Replies
5
Views
359
Replies
6
Views
193
Replies
1
Views
149
Replies
10
Views
393
Replies
7
Views
328

1,203,046
Messages
6,053,191
Members
444,644
Latest member
keepontruckinc4

### 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.

### Which adblocker are you using?

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

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