How to combine year?

M

michellejames

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

Greeting! We would like combine year for each same make and mode for each sku. I have try short, subtotal. But still not reach the result. Please check my excel of output tab and suggest me some formula or macro.

[TABLE="width: 375"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]sku[/TD]
[TD]year[/TD]
[TD]make [/TD]
[TD]model[/TD]
[/TR]
[TR]
[TD]100747900A00PG[/TD]
[TD="align: right"]2012[/TD]
[TD]Tesla[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]100747900A00PG[/TD]
[TD="align: right"]2013[/TD]
[TD]Tesla[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]100747900A00PG[/TD]
[TD="align: right"]2014[/TD]
[TD]Tesla[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]100747900A00PG[/TD]
[TD="align: right"]2015[/TD]
[TD]Tesla[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]100747900A00PG[/TD]
[TD="align: right"]2016[/TD]
[TD]Tesla[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2008[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2009[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2010[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2011[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2012[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2013[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2014[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2015[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2016[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2017[/TD]
[TD]Hyundai[/TD]
[TD]XYZ[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2008[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2009[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2010[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2011[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2012[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2013[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2014[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2015[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2013[/TD]
[TD]Hyundai[/TD]
[TD]Elantra Coupe[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2014[/TD]
[TD]Hyundai[/TD]
[TD]Elantra Coupe[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2013[/TD]
[TD]Hyundai[/TD]
[TD]Elantra GT[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2014[/TD]
[TD]Hyundai[/TD]
[TD]Elantra GT[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2015[/TD]
[TD]Hyundai[/TD]
[TD]Elantra GT[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2016[/TD]
[TD]Hyundai[/TD]
[TD]Elantra GT[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2013[/TD]
[TD]Hyundai[/TD]
[TD]Genesis Coupe[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2014[/TD]
[TD]Hyundai[/TD]
[TD]Genesis Coupe[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2017[/TD]
[TD]Hyundai[/TD]
[TD]Ioniq[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2018[/TD]
[TD]Hyundai[/TD]
[TD]Ioniq[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2018[/TD]
[TD]Kia[/TD]
[TD]Forte5[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2008[/TD]
[TD]Kia[/TD]
[TD]Magentis[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2009[/TD]
[TD]Kia[/TD]
[TD]Magentis[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2010[/TD]
[TD]Kia[/TD]
[TD]Magentis[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2017[/TD]
[TD]Kia[/TD]
[TD]Niro[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2018[/TD]
[TD]Kia[/TD]
[TD]Niro[/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2004[/TD]
[TD]Cadillac[/TD]
[TD]SRX[/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2005[/TD]
[TD]Cadillac[/TD]
[TD]SRX[/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2006[/TD]
[TD]Cadillac[/TD]
[TD]SRX[/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2007[/TD]
[TD]Cadillac[/TD]
[TD]SRX[/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2008[/TD]
[TD]Cadillac[/TD]
[TD]SRX[/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2009[/TD]
[TD]Cadillac[/TD]
[TD]SRX[/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2005[/TD]
[TD]Cadillac[/TD]
[TD]STS[/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2006[/TD]
[TD]Cadillac[/TD]
[TD]STS[/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2007[/TD]
[TD]Cadillac[/TD]
[TD]STS[/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2008[/TD]
[TD]Cadillac[/TD]
[TD]STS[/TD]
[/TR]
[TR]
[TD]B16[/TD]
[TD="align: right"]1970[/TD]
[TD]Ford[/TD]
[TD]Custom[/TD]
[/TR]
[TR]
[TD]B16[/TD]
[TD="align: right"]1971[/TD]
[TD]Ford[/TD]
[TD]Custom[/TD]
[/TR]
[TR]
[TD]B16[/TD]
[TD="align: right"]1972[/TD]
[TD]Ford[/TD]
[TD]Custom[/TD]
[/TR]
[TR]
[TD]B16[/TD]
[TD="align: right"]1972[/TD]
[TD]Ford[/TD]
[TD]Custom 500[/TD]
[/TR]
[TR]
[TD]B16[/TD]
[TD="align: right"]1973[/TD]
[TD]Ford[/TD]
[TD]Custom 500[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1986[/TD]
[TD]Ford[/TD]
[TD]E-350 Econoline[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1987[/TD]
[TD]Ford[/TD]
[TD]E-350 Econoline[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]E-350 Econoline Club Wagon[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]E-350 Econoline Club Wagon[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1986[/TD]
[TD]Ford[/TD]
[TD]F-350[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1987[/TD]
[TD]Ford[/TD]
[TD]F-350[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]Fairmont[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]Fairmont[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1980[/TD]
[TD]Ford[/TD]
[TD]Fairmont[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1981[/TD]
[TD]Ford[/TD]
[TD]Fairmont[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1982[/TD]
[TD]Ford[/TD]
[TD]Fairmont[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]Granada[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]Granada[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1980[/TD]
[TD]Ford[/TD]
[TD]Granada[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1981[/TD]
[TD]Ford[/TD]
[TD]Granada[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]LTD[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1987[/TD]
[TD]Ford[/TD]
[TD]LTD Crown Victoria[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1988[/TD]
[TD]Ford[/TD]
[TD]LTD Crown Victoria[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1989[/TD]
[TD]Ford[/TD]
[TD]LTD Crown Victoria[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]LTD II[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]LTD II[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]Mustang[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1980[/TD]
[TD]Ford[/TD]
[TD]Mustang[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1981[/TD]
[TD]Ford[/TD]
[TD]Mustang[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1982[/TD]
[TD]Ford[/TD]
[TD]Mustang[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]Mustang II[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]Ranchero[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]Ranchero[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]Thunderbird[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]Thunderbird[/TD]
[/TR]
</tbody>[/TABLE]


Output

[TABLE="width: 849"]
<colgroup><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD]sku[/TD]
[TD]year[/TD]
[TD]make [/TD]
[TD]model[/TD]
[TD]Combine year[/TD]
[TD]make [/TD]
[TD]model[/TD]
[/TR]
[TR]
[TD]100747900A00PG[/TD]
[TD="align: right"]2012[/TD]
[TD]Tesla[/TD]
[TD]S[/TD]
[TD]2012-2016[/TD]
[TD]Tesla[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]100747900A00PG[/TD]
[TD="align: right"]2013[/TD]
[TD]Tesla[/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100747900A00PG[/TD]
[TD="align: right"]2014[/TD]
[TD]Tesla[/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100747900A00PG[/TD]
[TD="align: right"]2015[/TD]
[TD]Tesla[/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100747900A00PG[/TD]
[TD="align: right"]2016[/TD]
[TD]Tesla[/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2008[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[TD]2008 -2016[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2009[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2010[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2011[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2012[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2013[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2014[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2015[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2016[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2017[/TD]
[TD]Hyundai[/TD]
[TD]XYZ[/TD]
[TD="align: right"]2017[/TD]
[TD]Hyundai[/TD]
[TD]XYZ[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2008[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[TD]2008-2015[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2009[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2010[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2011[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2012[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2013[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2014[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2015[/TD]
[TD]Hyundai[/TD]
[TD]Elantra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2013[/TD]
[TD]Hyundai[/TD]
[TD]Elantra Coupe[/TD]
[TD]2013-2014[/TD]
[TD]Hyundai[/TD]
[TD]Elantra Coupe[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2014[/TD]
[TD]Hyundai[/TD]
[TD]Elantra Coupe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2013[/TD]
[TD]Hyundai[/TD]
[TD]Elantra GT[/TD]
[TD]2013-2016[/TD]
[TD]Hyundai[/TD]
[TD]Elantra GT[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2014[/TD]
[TD]Hyundai[/TD]
[TD]Elantra GT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2015[/TD]
[TD]Hyundai[/TD]
[TD]Elantra GT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2016[/TD]
[TD]Hyundai[/TD]
[TD]Elantra GT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2013[/TD]
[TD]Hyundai[/TD]
[TD]Genesis Coupe[/TD]
[TD]2013-2014[/TD]
[TD]Hyundai[/TD]
[TD]Genesis Coupe[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2014[/TD]
[TD]Hyundai[/TD]
[TD]Genesis Coupe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2017[/TD]
[TD]Hyundai[/TD]
[TD]Ioniq[/TD]
[TD]2017-2018[/TD]
[TD]Hyundai[/TD]
[TD]Ioniq[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2018[/TD]
[TD]Hyundai[/TD]
[TD]Ioniq[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2018[/TD]
[TD]Kia[/TD]
[TD]Forte5[/TD]
[TD="align: right"]2018[/TD]
[TD]Kia[/TD]
[TD]Forte5[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2008[/TD]
[TD]Kia[/TD]
[TD]Magentis[/TD]
[TD]2008-2010[/TD]
[TD]Kia[/TD]
[TD]Magentis[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2009[/TD]
[TD]Kia[/TD]
[TD]Magentis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2010[/TD]
[TD]Kia[/TD]
[TD]Magentis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2017[/TD]
[TD]Kia[/TD]
[TD]Niro[/TD]
[TD]2017-2018[/TD]
[TD]Kia[/TD]
[TD]Niro[/TD]
[/TR]
[TR]
[TD]26300-35503[/TD]
[TD="align: right"]2018[/TD]
[TD]Kia[/TD]
[TD]Niro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2004[/TD]
[TD]Cadillac[/TD]
[TD]SRX[/TD]
[TD]2004-2009[/TD]
[TD]Cadillac[/TD]
[TD]SRX[/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2005[/TD]
[TD]Cadillac[/TD]
[TD]SRX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2006[/TD]
[TD]Cadillac[/TD]
[TD]SRX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2007[/TD]
[TD]Cadillac[/TD]
[TD]SRX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2008[/TD]
[TD]Cadillac[/TD]
[TD]SRX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2009[/TD]
[TD]Cadillac[/TD]
[TD]SRX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2005[/TD]
[TD]Cadillac[/TD]
[TD]STS[/TD]
[TD]2005-2008[/TD]
[TD]Cadillac[/TD]
[TD]STS[/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2006[/TD]
[TD]Cadillac[/TD]
[TD]STS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2007[/TD]
[TD]Cadillac[/TD]
[TD]STS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A44690[/TD]
[TD="align: right"]2008[/TD]
[TD]Cadillac[/TD]
[TD]STS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B16[/TD]
[TD="align: right"]1970[/TD]
[TD]Ford[/TD]
[TD]Custom[/TD]
[TD]1970-1972[/TD]
[TD]Ford[/TD]
[TD]Custom[/TD]
[/TR]
[TR]
[TD]B16[/TD]
[TD="align: right"]1971[/TD]
[TD]Ford[/TD]
[TD]Custom[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B16[/TD]
[TD="align: right"]1972[/TD]
[TD]Ford[/TD]
[TD]Custom[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B16[/TD]
[TD="align: right"]1972[/TD]
[TD]Ford[/TD]
[TD]Custom 500[/TD]
[TD]1972-1973[/TD]
[TD]Ford[/TD]
[TD]Custom 500[/TD]
[/TR]
[TR]
[TD]B16[/TD]
[TD="align: right"]1973[/TD]
[TD]Ford[/TD]
[TD]Custom 500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1986[/TD]
[TD]Ford[/TD]
[TD]E-350 Econoline[/TD]
[TD]1986-1987[/TD]
[TD]Ford[/TD]
[TD]E-350 Econoline[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1987[/TD]
[TD]Ford[/TD]
[TD]E-350 Econoline[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]E-350 Econoline Club Wagon[/TD]
[TD]1978-1979[/TD]
[TD]Ford[/TD]
[TD]E-350 Econoline Club Wagon[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]E-350 Econoline Club Wagon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1986[/TD]
[TD]Ford[/TD]
[TD]F-350[/TD]
[TD]1986-1987[/TD]
[TD]Ford[/TD]
[TD]F-350[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1987[/TD]
[TD]Ford[/TD]
[TD]F-350[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]Fairmont[/TD]
[TD]1978-1982[/TD]
[TD]Ford[/TD]
[TD]Fairmont[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]Fairmont[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1980[/TD]
[TD]Ford[/TD]
[TD]Fairmont[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1981[/TD]
[TD]Ford[/TD]
[TD]Fairmont[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1982[/TD]
[TD]Ford[/TD]
[TD]Fairmont[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]Granada[/TD]
[TD]1978-1981[/TD]
[TD]Ford[/TD]
[TD]Granada[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]Granada[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1980[/TD]
[TD]Ford[/TD]
[TD]Granada[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1981[/TD]
[TD]Ford[/TD]
[TD]Granada[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]LTD[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]LTD[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1987[/TD]
[TD]Ford[/TD]
[TD]LTD Crown Victoria[/TD]
[TD]1987-1989[/TD]
[TD]Ford[/TD]
[TD]LTD Crown Victoria[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1988[/TD]
[TD]Ford[/TD]
[TD]LTD Crown Victoria[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1989[/TD]
[TD]Ford[/TD]
[TD]LTD Crown Victoria[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]LTD II[/TD]
[TD]1978-1979[/TD]
[TD]Ford[/TD]
[TD]LTD II[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]LTD II[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]Mustang[/TD]
[TD]1979-1982[/TD]
[TD]Ford[/TD]
[TD]Mustang[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1980[/TD]
[TD]Ford[/TD]
[TD]Mustang[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1981[/TD]
[TD]Ford[/TD]
[TD]Mustang[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1982[/TD]
[TD]Ford[/TD]
[TD]Mustang[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]Mustang II[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]Mustang II[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]Ranchero[/TD]
[TD]1978-1979[/TD]
[TD]Ford[/TD]
[TD]Ranchero[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]Ranchero[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1978[/TD]
[TD]Ford[/TD]
[TD]Thunderbird[/TD]
[TD]1978-1979[/TD]
[TD]Ford[/TD]
[TD]Thunderbird[/TD]
[/TR]
[TR]
[TD]B3127[/TD]
[TD="align: right"]1979[/TD]
[TD]Ford[/TD]
[TD]Thunderbird[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

https://files.fm/u/95ez8cxr

Regards,
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Hi,

For your "Out put" tab, enter Array formula in E2 to be confirmed by CSE (Control, Shift, Enter), instructions below, and copied down:


Book1
ABCDE
1skuyearmakemodelCombine year
2100747900A00PG2012TeslaS2012-2016
3100747900A00PG2013TeslaS
4100747900A00PG2014TeslaS
5100747900A00PG2015TeslaS
6100747900A00PG2016TeslaS
726300-355032008HyundaiAccent2008-2016
826300-355032009HyundaiAccent
926300-355032010HyundaiAccent
1026300-355032011HyundaiAccent
1126300-355032012HyundaiAccent
1226300-355032013HyundaiAccent
1326300-355032014HyundaiAccent
1426300-355032015HyundaiAccent
1526300-355032016HyundaiAccent
1626300-355032017HyundaiXYZ2017
1726300-355032008HyundaiElantra2008-2015
1826300-355032009HyundaiElantra
1926300-355032010HyundaiElantra
2026300-355032011HyundaiElantra
2126300-355032012HyundaiElantra
2226300-355032013HyundaiElantra
2326300-355032014HyundaiElantra
2426300-355032015HyundaiElantra
2526300-355032013HyundaiElantra Coupe2013-2014
2626300-355032014HyundaiElantra Coupe
2726300-355032013HyundaiElantra GT2013-2016
2826300-355032014HyundaiElantra GT
2926300-355032015HyundaiElantra GT
3026300-355032016HyundaiElantra GT
3126300-355032013HyundaiGenesis Coupe2013-2014
3226300-355032014HyundaiGenesis Coupe
3326300-355032017HyundaiIoniq2017-2018
3426300-355032018HyundaiIoniq
3526300-355032018KiaForte52018
3626300-355032008KiaMagentis2008-2010
3726300-355032009KiaMagentis
3826300-355032010KiaMagentis
3926300-355032017KiaNiro2017-2018
4026300-355032018KiaNiro
41A446902004CadillacSRX2004-2009
42A446902005CadillacSRX
43A446902006CadillacSRX
44A446902007CadillacSRX
45A446902008CadillacSRX
46A446902009CadillacSRX
47A446902005CadillacSTS2005-2008
48A446902006CadillacSTS
49A446902007CadillacSTS
50A446902008CadillacSTS
51B161970FordCustom1970-1972
52B161971FordCustom
53B161972FordCustom
54B161972FordCustom 5001972-1973
55B161973FordCustom 500
56B31271986FordE-350 Econoline1986-1987
57B31271987FordE-350 Econoline
58B31271978FordE-350 Econoline Club Wagon1978-1979
59B31271979FordE-350 Econoline Club Wagon
60B31271986FordF-3501986-1987
61B31271987FordF-350
62B31271978FordFairmont1978-1982
63B31271979FordFairmont
64B31271980FordFairmont
65B31271981FordFairmont
66B31271982FordFairmont
67B31271978FordGranada1978-1981
68B31271979FordGranada
69B31271980FordGranada
70B31271981FordGranada
71B31271978FordLTD1978
72B31271987FordLTD Crown Victoria1987-1989
73B31271988FordLTD Crown Victoria
74B31271989FordLTD Crown Victoria
75B31271978FordLTD II1978-1979
76B31271979FordLTD II
77B31271979FordMustang1979-1982
78B31271980FordMustang
79B31271981FordMustang
80B31271982FordMustang
81B31271978FordMustang II1978
82B31271978FordRanchero1978-1979
83B31271979FordRanchero
84B31271978FordThunderbird1978-1979
85B31271979FordThunderbird
Out put
Cell Formulas
RangeFormula
E2{=IF(COUNTIFS(A$2:A2,A2,D$2:D2,D2)=1,MIN(IF(A$2:A$85=A2,IF(D$2:D$85=D2,B$2:B$85)))&IF(COUNTIFS(A$2:A$85,A2,B$2:B$85,B2,D$2:D$85,D2)=COUNTIFS(A$2:A$85,A2,D$2:D$85,D2),"","-"&MAX(IF(A$2:A$85=A2,IF(D$2:D$85=D2,B$2:B$85)))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Try this on your Basic Data sheet:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG14Dec06
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, Sp [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Txt = Join(Application.Index(Dn.Resize(, 4).Value, 1, Array(1, 3, 4)), ",")
    [COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
        .Add Txt, Array(Dn.Offset(, 1), Dn)
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Txt)
        [COLOR="Navy"]If[/COLOR] InStr(Q(0), "-") > 0 [COLOR="Navy"]Then[/COLOR]
            Sp = Split(Q(0), "-")
            Q(0) = Sp(0) & "-" & Dn.Offset(, 1).Value
        [COLOR="Navy"]Else[/COLOR]
            Q(0) = Q(0) & "-" & Dn.Offset(, 1).Value
        [COLOR="Navy"]End[/COLOR] If
   .Item(Txt) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("E1:G1").Value = Array("Combine year", "make", "model")

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    Cells(.Item(K)(1).Row, 5) = .Item(K)(0)
    Cells(.Item(K)(1).Row, 6) = .Item(K)(1).Offset(, 2).Value
    Cells(.Item(K)(1).Row, 7) = .Item(K)(1).Offset(, 3).Value
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hi There,

Greeting! First of all Thanks! This is code is working only for this my sample file. But I have large file and there this code is not working properly. It combine wrong year and wrong data. I am gong to show you only 2000 row data. I have more than 10000 row data. I have make VBA file according to your instruction.

https://files.fm/u/gwhrpmzp

Please check and fix this asap.

Regards,
 
Upvote 0
My Anti-virus software blocked the download, and flagged it as dangerous.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Find the min year and max year in each subset.
Replies
8
Views
814
Fluff
Fluff
M
  • Question Question
combine each subset values in their respective place
Replies
5
Views
760
Fluff
Fluff

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

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