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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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
Back
Top