Find the min year and max year in each subset.

M

michellejames

New Member
Joined
Aug 16, 2013
Messages
27
Hi there,

I have each mode and make for subset with different year. I need to find its max and min year. I thinking whether my I sort the data via make or mode or not? If i will not sort them then can I find each sku of each subset min and max year.

PG.Min YearMax YearMake NameModel NameEngine Base LiterVIO Summary
100747900A00PG20122016TeslaS88913
26300-3550320082018HyundaiSonata2.41553977
26300-3550320112016HyundaiElantra1.8885687
26300-3550320082018KiaOptima2.4870002
26300-3550320082019HyundaiElantra2.0711328
26300-3550320082018HyundaiAccent1.6536118
26300-3550320102018KiaSoul2.0527408
26300-3550320112018KiaSorento2.4504615
26300-3550320102018KiaSoul1.6437283
26300-3550320102018KiaForte2.0404992
26300-3550320132018HyundaiSanta Fe Sport2.4376146
26300-3550320112018KiaSportage2.4371436
26300-3550320082018KiaRio1.6256063
26300-3550320082018HyundaiTucson2.0218675
26300-3550320102015HyundaiTucson2.4207645
26300-3550320142016KiaForte1.8167540
26300-3550320122019HyundaiVeloster1.6161960
26300-3550320112018HyundaiSonata2.0150765
26300-3550320162018HyundaiTucson1.6119829
26300-3550320132018HyundaiSanta Fe Sport2.0113938
26300-3550320112018KiaOptima2.0107593
26300-3550320102012HyundaiSanta Fe2.496920
26300-3550320082009KiaSpectra2.081242
26300-3550320142018HyundaiElantra GT2.074771
26300-3550320082009HyundaiSanta Fe2.772752
26300-3550320072018KiaSportage2.072129
26300-3550320072010KiaSportage2.757493
26300-3550320132013HyundaiElantra GT1.840437
26300-3550320172018KiaNiro1.638560
26300-3550320102014HyundaiGenesis Coupe2.037514
26300-3550320102015KiaForte Koup2.024728
26300-3550320142016KiaForte52.023619
26300-3550320162018KiaSorento2.023578
26300-3550320082012KiaRondo2.421361
26300-3550320102013KiaForte2.417167
26300-3550320172018HyundaiIoniq1.616745
26300-3550320102013KiaForte Koup2.416454
26300-3550320132013HyundaiElantra Coupe1.815606
26300-3550320082012KiaRondo2.714742
26300-3550320082009HyundaiTucson2.713346
26300-3550320082011KiaRio51.613026
26300-3550320172018HyundaiElantra1.611740
26300-3550320152018HyundaiSonata1.69335
26300-3550320082009KiaSpectra52.08710
26300-3550320082008HyundaiTiburon2.08005
26300-3550320082010KiaOptima2.77532
26300-3550320162018KiaOptima1.67476
26300-3550320082008HyundaiTiburon2.74758
26300-3550320142014HyundaiElantra Coupe2.04194
26300-3550320142016KiaForte Koup1.63676
26300-3550320142016KiaForte51.63553
26300-3550320182018KiaStinger2.02921
26300-3550320182018HyundaiElantra GT1.61593
26300-3550320192019HyundaiVeloster2.029
26300-3550320142016KiaRondo2.09
A4469020042009CadillacSRX3.676973
A4469020052011CadillacSTS3.664275
A4469020052010CadillacSTS4.627912
A4469020042009CadillacSRX4.621521

<tbody>
</tbody>


AS vio is important for us hence I can not short it. How to get the min and max year

PG.Make NameModel NameMin YearMax Year
100747900A00PGTeslaS??
26300-35503HyundaiAccent??
26300-35503HyundaiElantra??
26300-35503HyundaiElantra Coupe??
26300-35503HyundaiElantra GT??
26300-35503HyundaiGenesis Coupe??
26300-35503HyundaiIoniq??
26300-35503HyundaiSanta Fe??
26300-35503HyundaiSanta Fe Sport??
26300-35503HyundaiSonata??
26300-35503HyundaiTiburon??
26300-35503HyundaiTucson??
26300-35503HyundaiVeloster??
26300-35503KiaForte??
26300-35503KiaForte Koup??
26300-35503KiaForte5??
26300-35503KiaNiro??
26300-35503KiaOptima??
26300-35503KiaRio??
26300-35503KiaRio5??
26300-35503KiaRondo??
26300-35503KiaSorento??
26300-35503KiaSoul??
26300-35503KiaSpectra??
26300-35503KiaSpectra5??
26300-35503KiaSportage??
26300-35503KiaStinger??
A44690CadillacSRX??
A44690CadillacSTS??
A44690CadillacSRX??

<tbody>
</tbody>


Result

PG.Make NameModel NameMin YearMax Year
26300-35503HyundaiSonata20082018
26300-35503HyundaiElantra20082019
26300-35503KiaOptima20082018
26300-35503HyundaiAccent20082018
26300-35503KiaSoul20102018

<tbody>
</tbody>
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
you can use PowerQuery or PivotTable

is that what you want?

PowerQueryPivotTable
PG.Make NameModel NameMinMaxPG.Make NameModel NameMin of Min YearMax of Max Year
100747900A00PGTeslaS
2012​
2016​
100747900A00PGTeslaS
2012​
2016​
26300-35503HyundaiAccent
2008​
2018​
26300-35503HyundaiAccent
2008​
2018​
26300-35503HyundaiElantra
2008​
2019​
26300-35503HyundaiElantra
2008​
2019​
26300-35503HyundaiElantra Coupe
2013​
2014​
26300-35503HyundaiElantra Coupe
2013​
2014​
26300-35503HyundaiElantra GT
2013​
2018​
26300-35503HyundaiElantra GT
2013​
2018​
26300-35503HyundaiGenesis Coupe
2010​
2014​
26300-35503HyundaiGenesis Coupe
2010​
2014​
26300-35503HyundaiIoniq
2017​
2018​
26300-35503HyundaiIoniq
2017​
2018​
26300-35503HyundaiSanta Fe
2008​
2012​
26300-35503HyundaiSanta Fe
2008​
2012​
26300-35503HyundaiSanta Fe Sport
2013​
2018​
26300-35503HyundaiSanta Fe Sport
2013​
2018​
26300-35503HyundaiSonata
2008​
2018​
26300-35503HyundaiSonata
2008​
2018​
26300-35503HyundaiTiburon
2008​
2008​
26300-35503HyundaiTiburon
2008​
2008​
26300-35503HyundaiTucson
2008​
2018​
26300-35503HyundaiTucson
2008​
2018​
26300-35503HyundaiVeloster
2012​
2019​
26300-35503HyundaiVeloster
2012​
2019​
26300-35503KiaForte
2010​
2018​
26300-35503KiaForte
2010​
2018​
26300-35503KiaForte Koup
2010​
2016​
26300-35503KiaForte Koup
2010​
2016​
26300-35503KiaForte5
2014​
2016​
26300-35503KiaForte5
2014​
2016​
26300-35503KiaNiro
2017​
2018​
26300-35503KiaNiro
2017​
2018​
26300-35503KiaOptima
2008​
2018​
26300-35503KiaOptima
2008​
2018​
26300-35503KiaRio
2008​
2018​
26300-35503KiaRio
2008​
2018​
26300-35503KiaRio5
2008​
2011​
26300-35503KiaRio5
2008​
2011​
26300-35503KiaRondo
2008​
2016​
26300-35503KiaRondo
2008​
2016​
26300-35503KiaSorento
2011​
2018​
26300-35503KiaSorento
2011​
2018​
26300-35503KiaSoul
2010​
2018​
26300-35503KiaSoul
2010​
2018​
26300-35503KiaSpectra
2008​
2009​
26300-35503KiaSpectra
2008​
2009​
26300-35503KiaSpectra5
2008​
2009​
26300-35503KiaSpectra5
2008​
2009​
26300-35503KiaSportage
2007​
2018​
26300-35503KiaSportage
2007​
2018​
26300-35503KiaStinger
2018​
2018​
26300-35503KiaStinger
2018​
2018​
A44690CadillacSRX
2004​
2009​
A44690CadillacSRX
2004​
2009​
A44690CadillacSTS
2005​
2011​
A44690CadillacSTS
2005​
2011​
 
Last edited:
Upvote 0
With formulae


Excel 2013/2016
ABCDEFGHIJKLMN
1PG.Min YearMax YearMake NameModel NameEngine Base LiterVIO SummaryPG.Make NameModel Name
2100747900A00PG20122016TeslaS8891326300-35503HyundaiSonata20082018
326300-3550320082018HyundaiSonata2.4155397726300-35503HyundaiElantra20082019
426300-3550320112016HyundaiElantra1.888568726300-35503KiaOptima20082018
526300-3550320082018KiaOptima2.487000226300-35503HyundaiAccent20082018
626300-3550320082019HyundaiElantra271132826300-35503KiaSoul20102018
726300-3550320082018HyundaiAccent1.6536118
826300-3550320102018KiaSoul2527408
926300-3550320112018KiaSorento2.4504615
1026300-3550320102018KiaSoul1.6437283
1126300-3550320102018KiaForte2404992
1226300-3550320132018HyundaiSanta Fe Sport2.4376146
1326300-3550320112018KiaSportage2.4371436
1426300-3550320082018KiaRio1.6256063
1526300-3550320082018HyundaiTucson2218675
Sheet5
Cell Formulas
RangeFormula
M2{=MIN(IF(($A$2:$A$60=J2)*($D$2:$D$60=K2)*($E$2:$E$60=L2),($B$2:$B$60)))}
N2{=MAX(IF(($A$2:$A$60=J2)*($D$2:$D$60=K2)*($E$2:$E$60=L2),($C$2:$C$60)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Click on M2 then on frmula bar and
use Ctrl + Shift + Enter
and drag formula down
the same with N2
 
Last edited:
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
Hi There,

Greeting! I would like to combine the column R data in column s where the Last word mentioned.

Is this possible?

I would like combine all subset column R values in Column S of Last word mentioned beside cell
https://www.dropbox.com/s/ll1fvpodpuccifv/Find first and last year_v1.xlsx?dl=0

Please check

Example:

PG.Make NameModel NameMin YearMax YearUnique makeUnique/Duplicateyear make and modelcombine in one rowFind Last sku each group
100747900A00PGTeslaS201220162012-16Teslaunique2012-16 Tesla S2012-16 Tesla SLast
26300-35503HyundaiAccent200820182008-18Hyundaiunique2008-18 Hyundai Accent
26300-35503HyundaiElantra200820192008-19Duplicate2008-19 Elantra
26300-35503HyundaiElantra Coupe201320142013-14Duplicate2013-14 Elantra Coupe
26300-35503HyundaiElantra GT201320182013-18Duplicate2013-18 Elantra GT
26300-35503HyundaiGenesis Coupe201020142010-14Duplicate2010-14 Genesis Coupe
26300-35503HyundaiIoniq201720182017-18Duplicate2017-18 Ioniq
26300-35503HyundaiSanta Fe200820122008-12Duplicate2008-12 Santa Fe
26300-35503HyundaiSanta Fe Sport201320182013-18Duplicate2013-18 Santa Fe Sport
26300-35503HyundaiSonata200820182008-18Duplicate2008-18 Sonata
26300-35503HyundaiTiburon200820082008-08Duplicate2008-08 Tiburon
26300-35503HyundaiTucson200820182008-18Duplicate2008-18 Tucson
26300-35503HyundaiVeloster201220192012-19Duplicate2012-19 Veloster
26300-35503KiaForte201020182010-18Kiaunique2010-18 Kia Forte
26300-35503KiaForte Koup201020162010-16Duplicate2010-16 Forte Koup
26300-35503KiaForte5201420162014-16Duplicate2014-16 Forte5
26300-35503KiaNiro201720182017-18Duplicate2017-18 Niro
26300-35503KiaOptima200820182008-18Duplicate2008-18 Optima
26300-35503KiaRio200820182008-18Duplicate2008-18 Rio
26300-35503KiaRio5200820112008-11Duplicate2008-11 Rio5
26300-35503KiaRondo200820162008-16Duplicate2008-16 Rondo
26300-35503KiaSorento201120182011-18Duplicate2011-18 Sorento
26300-35503KiaSoul201020182010-18Duplicate2010-18 Soul
26300-35503KiaSpectra200820092008-09Duplicate2008-09 Spectra
26300-35503KiaSpectra5200820092008-09Duplicate2008-09 Spectra5
26300-35503KiaSportage200720182007-18Duplicate2007-18 Sportage
26300-35503KiaStinger201820182018-18Duplicate2018-18 Stinger2008-18 Hyundai Accent, 2008-19 Elantra, 2013-14 Elantra Coupe, 2013-18 Elantra GT, 2010-14 Genesis Coupe, 2017-18 Ioniq, 2008-12 Santa Fe, 2013-18 Santa Fe Sport, 2008-18 Sonata, 2008-08 Tiburon, 2008-18 Tucson, 2012-19 Veloster, 2010-18 Kia Forte, 2010-16 Forte Koup, 2014-16 Forte5, 2017-18 Niro, 2008-18 Optima, 2008-18 Rio, 2008-11 Rio5, 2008-16 Rondo, 2011-18 Sorento, 2010-18 Soul, 2008-09 Spectra, 2008-09 Spectra5, 2007-18 Sportage, 2018-18 StingerLast
A44690CadillacSRX200420092004-09Cadillacunique2004-09 Cadillac SRX
A44690CadillacSTS200520112005-11Duplicate2005-11 STS
A44690CadillacSRX200420092004-09Duplicate2004-09 SRX2004-09 Cadillac SRX, 2005-11 STS, 2004-09 SRXLast
DF99379ChevroletEquinox201820192018-19Chevroletunique2018-19 Chevrolet Equinox
DF99379ChevroletExpress 2500201720192017-19Duplicate2017-19 Express 2500
DF99379GMCSavana 2500201720192017-19GMCunique2017-19 GMC Savana 2500
DF99379GMCTerrain201820192018-19Duplicate2018-19 Terrain2018-19 Chevrolet Equinox, 2017-19 Express 2500, 2017-19 GMC Savana 2500, 2018-19 TerrainLast
DF99380ChevroletCruze201720192017-19Chevroletunique2017-19 Chevrolet Cruze
DF99380ChevroletSilverado 2500 HD201720192017-19Duplicate2017-19 Silverado 2500 HD
DF99380GMCSierra 2500 HD201720192017-19GMCunique2017-19 GMC Sierra 2500 HD
DF99380GMCSierra 3500 HD201720192017-19Duplicate2017-19 Sierra 3500 HD2017-19 Chevrolet Cruze, 2017-19 Silverado 2500 HD, 2017-19 GMC Sierra 2500 HD, 2017-19 Sierra 3500 HDLast
PA4278InfinitiQX60201420192014-19Infinitiunique2014-19 Infiniti QX602014-19 Infiniti QX60Last
PA4675InfinitiQ70201420192014-19Infinitiunique2014-19 Infiniti Q70
PA4675InfinitiQ70L201520192015-19Duplicate2015-19 Q70L2014-19 Infiniti Q70, 2015-19 Q70LLast
PA5558KMWM760i xDrive201920192019-19KMWunique2019-19 KMW M760i xDrive2019-19 KMW M760i xDriveLast
PA5655ToyotaCorolla200920192009-19Toyotaunique2009-19 Toyota Corolla2009-19 Toyota CorollaLast
PA5824Nissan370Z200920192009-19Nissanunique2009-19 Nissan 370Z2009-19 Nissan 370ZLast
PA5900KMW640i Gran Coupe201320192013-19KMWunique2013-19 KMW 640i Gran Coupe
PA5900KMW640i xDrive Gran Coupe201420192014-19Duplicate2014-19 640i xDrive Gran Coupe2013-19 KMW 640i Gran Coupe, 2014-19 640i xDrive Gran CoupeLast
PA6116DodgeDurango201120192011-19Dodgeunique2011-19 Dodge Durango
PA6116ToyotaHighlander201420192014-19Toyotaunique2014-19 Toyota Highlander
PA6116ToyotaSienna201120192011-19Duplicate2011-19 Sienna2011-19 Dodge Durango, 2014-19 Toyota Highlander, 2011-19 SiennaLast
PA99265ToyotaCamry201820192018-19Toyotaunique2018-19 Toyota Camry
PA99265ToyotaC-HR201820192018-19Duplicate2018-19 C-HR
PA99265ToyotaCorolla201920192019-19Duplicate2019-19 Corolla2018-19 Toyota Camry, 2018-19 C-HR, 2019-19 CorollaLast
PA99267ToyotaAvalon201920192019-19Toyotaunique2019-19 Toyota Avalon
PA99267ToyotaCamry201820192018-19Duplicate2018-19 Camry2019-19 Toyota Avalon, 2018-19 CamryLast

<colgroup><col><col><col><col span="5"><col><col><col></colgroup><tbody>
</tbody>


Regards,
 
Upvote 0
As this is a totally different question, please start a new thread
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
How to combine year?
Replies
5
Views
825
Fluff
Fluff
M
  • Question
combine each subset values in their respective place
Replies
5
Views
674
Fluff
Fluff

Forum statistics

Threads
1,214,584
Messages
6,120,385
Members
448,956
Latest member
JPav

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