highest incident in a list

Duane

Board Regular
Joined
Mar 14, 2002
Messages
228
Hey gang,

OK, so, like, here's the thing... I need a function or set of nested functions that will allow me to know what the highest odometer reading is for a particualr vehicle.

I have a list of invoices. One VIN may have been serviced two, three, four, five times. I need to be able to enter a VIN and have a formula look through the list, find the highest odometer reading for that VIN, whether the VIN shows up once, twice, three or N times.

An example...

abcde12345F6789101 8,000
SomeOtherVINs x
abcde12345F6789101 16,000
SomeOtherVINs, x
abcde12345F6789101 24,000

So, if I entered abcde12345F6789101 in some cell, the formula(s) in the column to the right should return 24,000 until another invoice with that VIN is entered, then the formula would show whatever is the highest kms.

Can this be done? Thanks much for any help provided.


Thanks for any help you can provide.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
How about something like this
Book1
CDEFG
2253468tom
3tom150265
4****125586
5harry205825
6tom253468
7****789258
8harry365125
Sheet1


=MAX(IF(G2=C3:C8,D3:D8,0)) confirmed with Ctrl+Shift+Enter
 

Duane

Board Regular
Joined
Mar 14, 2002
Messages
228
How about something like this

=MAX(IF(G2=C3:C8,D3:D8,0)) confirmed with Ctrl+Shift+Enter

Thanks for the quick response, but this array formula (MrExcel likes to call these CSE formulas) produces a #NUM! error.

I like the idea though, something like this should work...any other suggestions? (I am not fluent with how to next functions in an array formula approach).

I appreciate your help.
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Yup, do NOT select the entire column, give it a discreet range, otherwise the CSE formula will choke. If your range changes, consider a Dynamic Range (either Named or not).

Example of Dynamic Range (not named) but also note that this depends upon both columns being the same length with no spaces in the data...
Book1
CDEFG
1tom123587
2****98658
3harry234568
4tom124758354587tom
5tom354587
6****295487
7harry125874
8tom1256
Sheet1
 

Duane

Board Regular
Joined
Mar 14, 2002
Messages
228

ADVERTISEMENT

Yup, do NOT select the entire column, give it a discreet range, otherwise the CSE formula will choke. If your range changes, consider a Dynamic Range (either Named or not).

Excellent, mate. I did select the entire column. Fixed, and working just peachy now, thanks much.

Just out of curiousity, is there a way to do this without array formulas (like SUMPRODUCT or otherwise?) Array formulas tend to slow things down.

Thanks much.
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Well, sumproduct will give you the sum of all matches... not very useful if you are looking for the maximum. You could do this in two steps:
Book1
CDEFGHIJ
1tom123587123587
2****98658 
3harry234568 
4tom124758124758354587tom
5tom354587354587354587
6****295487 
7harry125874 
8tom12561256
Sheet1


On the other hand, one array formula should not slow you down that much. Are you, perhaps, generating this output simultaneiousky for all unique owners? If that is the case, consider a pivot table instead (don't ask me, I have never tried making one, but I am pretty sure this would be a place to use one).


What else are you doing that this is slowing you down so much?
 

Duane

Board Regular
Joined
Mar 14, 2002
Messages
228

ADVERTISEMENT

Well, sumproduct will give you the sum of all matches... not very useful if you are looking for the maximum. You could do this in two steps:
On the other hand, one array formula should not slow you down that much. Are you, perhaps, generating this output simultaneiousky for all unique owners? If that is the case, consider a pivot table instead (don't ask me, I have never tried making one, but I am pretty sure this would be a place to use one).


What else are you doing that this is slowing you down so much?

I'm managing a fleet of vehicles. Each row is one vehicle. We have approximately 370 vehicles and growing. So, the main sheet has a formula in each row. At some point, our IT may be commissioned to convert our entire database into CICS mainframe, but in the meantime I'm not conversant in Access.

Also, I've actually used SUMPRODUCT as a multiple criteria lookup, but it's straightforward (if this range matches criteria1, that range matches criteria2, the other range matches criteria3, show me what's in range 4). I was at a loss how to get SUMPRODUCT to match criteria and then give me the max in the final range.
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
I've actually used SUMPRODUCT as a multiple criteria lookup
Yup, that's a great use, when you get a SINGLE match, but if you have multiple records that match your criteria, you get the sum of all of those matches
I was at a loss how to get SUMPRODUCT to match criteria and then give me the max in the final range
That's because you can't. A CSE formula is the only way that I know how to do it in one single step. Perhaps Barry Houdini can come up with something (and prove me wrong as he usually does) but I can't be of any more help at this point...
 

Duane

Board Regular
Joined
Mar 14, 2002
Messages
228
I've actually used SUMPRODUCT as a multiple criteria lookup
Yup, that's a great use, when you get a SINGLE match, but if you have multiple records that match your criteria, you get the sum of all of those matches
I was at a loss how to get SUMPRODUCT to match criteria and then give me the max in the final range
That's because you can't. A CSE formula is the only way that I know how to do it in one single step. Perhaps Barry Houdini can come up with something (and prove me wrong as he usually does) but I can't be of any more help at this point...

Hey man, you've been of tremendous help, the CSE is in and working just fine. I was merely going forward for theoretic purposes and for the value of the message board, and your answer also helped in this area.

It's all good, much thanks.
 

Forum statistics

Threads
1,136,508
Messages
5,676,270
Members
419,617
Latest member
Shane50GT

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