highest incident in a list

[Duane]

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.

 

[hatman]

How about something like this

Book1
	C	D	E	F	G
2				253468	tom
3	tom	150265
4	****	125586
5	harry	205825
6	tom	253468
7	****	789258
8	harry	365125
Sheet1

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

 

[Duane]

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]

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
	C	D	E	F	G
1	tom	123587
2	****	98658
3	harry	234568
4	tom	124758		354587	tom
5	tom	354587
6	****	295487
7	harry	125874
8	tom	1256
Sheet1

 

[Duane]

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, 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
	C	D	E	F	G	H	I	J
1	tom	123587		123587
2	****	98658
3	harry	234568
4	tom	124758		124758			354587	tom
5	tom	354587		354587			354587
6	****	295487
7	harry	125874
8	tom	1256		1256
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]

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]

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]

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.