How to combine year?

[michellejames]

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,

 

[jtakw]

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
	A	B	C	D	E
1	sku	year	make	model	Combine year
2	100747900A00PG	2012	Tesla	S	2012-2016
3	100747900A00PG	2013	Tesla	S
4	100747900A00PG	2014	Tesla	S
5	100747900A00PG	2015	Tesla	S
6	100747900A00PG	2016	Tesla	S
7	26300-35503	2008	Hyundai	Accent	2008-2016
8	26300-35503	2009	Hyundai	Accent
9	26300-35503	2010	Hyundai	Accent
10	26300-35503	2011	Hyundai	Accent
11	26300-35503	2012	Hyundai	Accent
12	26300-35503	2013	Hyundai	Accent
13	26300-35503	2014	Hyundai	Accent
14	26300-35503	2015	Hyundai	Accent
15	26300-35503	2016	Hyundai	Accent
16	26300-35503	2017	Hyundai	XYZ	2017
17	26300-35503	2008	Hyundai	Elantra	2008-2015
18	26300-35503	2009	Hyundai	Elantra
19	26300-35503	2010	Hyundai	Elantra
20	26300-35503	2011	Hyundai	Elantra
21	26300-35503	2012	Hyundai	Elantra
22	26300-35503	2013	Hyundai	Elantra
23	26300-35503	2014	Hyundai	Elantra
24	26300-35503	2015	Hyundai	Elantra
25	26300-35503	2013	Hyundai	Elantra Coupe	2013-2014
26	26300-35503	2014	Hyundai	Elantra Coupe
27	26300-35503	2013	Hyundai	Elantra GT	2013-2016
28	26300-35503	2014	Hyundai	Elantra GT
29	26300-35503	2015	Hyundai	Elantra GT
30	26300-35503	2016	Hyundai	Elantra GT
31	26300-35503	2013	Hyundai	Genesis Coupe	2013-2014
32	26300-35503	2014	Hyundai	Genesis Coupe
33	26300-35503	2017	Hyundai	Ioniq	2017-2018
34	26300-35503	2018	Hyundai	Ioniq
35	26300-35503	2018	Kia	Forte5	2018
36	26300-35503	2008	Kia	Magentis	2008-2010
37	26300-35503	2009	Kia	Magentis
38	26300-35503	2010	Kia	Magentis
39	26300-35503	2017	Kia	Niro	2017-2018
40	26300-35503	2018	Kia	Niro
41	A44690	2004	Cadillac	SRX	2004-2009
42	A44690	2005	Cadillac	SRX
43	A44690	2006	Cadillac	SRX
44	A44690	2007	Cadillac	SRX
45	A44690	2008	Cadillac	SRX
46	A44690	2009	Cadillac	SRX
47	A44690	2005	Cadillac	STS	2005-2008
48	A44690	2006	Cadillac	STS
49	A44690	2007	Cadillac	STS
50	A44690	2008	Cadillac	STS
51	B16	1970	Ford	Custom	1970-1972
52	B16	1971	Ford	Custom
53	B16	1972	Ford	Custom
54	B16	1972	Ford	Custom 500	1972-1973
55	B16	1973	Ford	Custom 500
56	B3127	1986	Ford	E-350 Econoline	1986-1987
57	B3127	1987	Ford	E-350 Econoline
58	B3127	1978	Ford	E-350 Econoline Club Wagon	1978-1979
59	B3127	1979	Ford	E-350 Econoline Club Wagon
60	B3127	1986	Ford	F-350	1986-1987
61	B3127	1987	Ford	F-350
62	B3127	1978	Ford	Fairmont	1978-1982
63	B3127	1979	Ford	Fairmont
64	B3127	1980	Ford	Fairmont
65	B3127	1981	Ford	Fairmont
66	B3127	1982	Ford	Fairmont
67	B3127	1978	Ford	Granada	1978-1981
68	B3127	1979	Ford	Granada
69	B3127	1980	Ford	Granada
70	B3127	1981	Ford	Granada
71	B3127	1978	Ford	LTD	1978
72	B3127	1987	Ford	LTD Crown Victoria	1987-1989
73	B3127	1988	Ford	LTD Crown Victoria
74	B3127	1989	Ford	LTD Crown Victoria
75	B3127	1978	Ford	LTD II	1978-1979
76	B3127	1979	Ford	LTD II
77	B3127	1979	Ford	Mustang	1979-1982
78	B3127	1980	Ford	Mustang
79	B3127	1981	Ford	Mustang
80	B3127	1982	Ford	Mustang
81	B3127	1978	Ford	Mustang II	1978
82	B3127	1978	Ford	Ranchero	1978-1979
83	B3127	1979	Ford	Ranchero
84	B3127	1978	Ford	Thunderbird	1978-1979
85	B3127	1979	Ford	Thunderbird
Out put

Cell Formulas
Range	Formula
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.

 

[MickG]

Try this on your Basic Data sheet:-

[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

 

[michellejames]

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,

 

[MickG]

Please upload you file to Box.com or Dropbox.com

 

[Fluff]

My Anti-virus software blocked the download, and flagged it as dangerous.