Identify Highest and Lowest Value...

M

MattGold

New Member
Joined
Mar 24, 2005
Messages
5
I'm working on a four column file of automotive data, with Start Year, End Year, Make & Model.

Here's what I have:
1996 | 1998 | FORD | CONTOUR
1996 | 2000 | FORD | CONTOUR
1997 | 2005 | FORD | CONTOUR
1996 | 2003 | FORD | CROWN VICTORIA
1999 | 2003 | FORD | CROWN VICTORIA
2002 | 2008 | FORD | CROWN VICTORIA


Here's what I need:
1996 | 2005 | FORD | CONTOUR
2002 | 2003 | FORD | CROWN VICTORIA

For each matched Make / Model (Column A / D) I need the lowest Start Year (Column A) and highest End Year (Column B).

Bonus points if there is a gap in years that can be identified...

Such as this:
1996 | 1998 | FORD | TAURUS
1998 | 2002 | FORD | TAURUS
2006 | 2009 | FORD | TAURUS
2008 | 2015 | FORD | TAURUS

Output should be this:
1996 | 2002 | FORD | TAURUS
2006 | 2015 | FORD | TAURUS
 

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.
Sort by date Sort by votes
contour1993
611996
19961998fordcontour21996<<<<<<<
19972000fordcontour31996
19972005fordcontour41993
19932005fordcontour51993
19982005fordcontour61993
19992005fordcontour
19962003fordcrown victoria
19992003fordcrown victoria
20022008fordcrown victoria
the 6, the numbers 1 to 6,
are generated automatically for contour in G1
h1 takes the min of i2:i100
cell marked<<<<<
=IF(H3="","",IF(OFFSET($A$2,H3,0)<i2,offset($a$2,h3,0),i2))< td=""></i2,offset($a$2,h3,0),i2))<>
easy to find the max from col b
and to repeat for crown victoria
ignore <<<<< formula mrexcel has damaged it
will try to put it in next post


<tbody>
</tbody>
 
Upvote 0
= IF (H3 = "" , "" , IF ( OFFSET ( $ A $ 2 , H 3 , 0 ) < I 2 , OFFSET ( $ A $ 2 , H 3 , 0 ) ,I 2 ) )

<tbody>
</tbody>
 
Upvote 0
Lets say you have data from Column A to D as shown in post #1

In E2 , For minimum value

=MIN(IF($C$2:$C$5000=C2,IF($D$2:$D$5000=D2,$A$2:$A$5000)))

In F2, for maximum

=MAX(IF($C$2:$C$5000=C2,IF($D$2:$D$5000=D2,$B$2:$B$5000)))

Confirm with CTRL+SHIFT+ENTER, not just ENTER
 
Upvote 0
oldbrewer:

I really appreciate you making the effort - but I really don't understand.

Here's what I believe the formula to be:
=IF(H3="","", IF(OFFSET($A$2, H3, 0)<I2,OFFSET($A$2,H3,0),I2))


What cell should this be in and what should be in columns H & I?
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
sum formula
Replies
9
Views
274
fjns
F
N
  • Question
Color duplicates rows in two different sets of data and also the rows which are present in both sets of data.
Replies
0
Views
340
Noob_Excel
N
S
  • Solved
How to separate text out to a new column - Text to Column does not work
Replies
7
Views
552
kevin9999
K
skyh3ck
  • Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
269
skyh3ck
skyh3ck
Ironman
  • Solved
Display Message Box only once when value exceeded
2
Replies
19
Views
3K
johnnyL
J

Forum statistics

Threads
1,214,819
Messages
6,121,746
Members
449,050
Latest member
excelknuckles

Share this page

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