How to combine year?

michellejames

New Member
Joined
Aug 16, 2013
Messages
26
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.

skuyearmake model
100747900A00PG2012TeslaS
100747900A00PG2013TeslaS
100747900A00PG2014TeslaS
100747900A00PG2015TeslaS
100747900A00PG2016TeslaS
26300-355032008HyundaiAccent
26300-355032009HyundaiAccent
26300-355032010HyundaiAccent
26300-355032011HyundaiAccent
26300-355032012HyundaiAccent
26300-355032013HyundaiAccent
26300-355032014HyundaiAccent
26300-355032015HyundaiAccent
26300-355032016HyundaiAccent
26300-355032017HyundaiXYZ
26300-355032008HyundaiElantra
26300-355032009HyundaiElantra
26300-355032010HyundaiElantra
26300-355032011HyundaiElantra
26300-355032012HyundaiElantra
26300-355032013HyundaiElantra
26300-355032014HyundaiElantra
26300-355032015HyundaiElantra
26300-355032013HyundaiElantra Coupe
26300-355032014HyundaiElantra Coupe
26300-355032013HyundaiElantra GT
26300-355032014HyundaiElantra GT
26300-355032015HyundaiElantra GT
26300-355032016HyundaiElantra GT
26300-355032013HyundaiGenesis Coupe
26300-355032014HyundaiGenesis Coupe
26300-355032017HyundaiIoniq
26300-355032018HyundaiIoniq
26300-355032018KiaForte5
26300-355032008KiaMagentis
26300-355032009KiaMagentis
26300-355032010KiaMagentis
26300-355032017KiaNiro
26300-355032018KiaNiro
A446902004CadillacSRX
A446902005CadillacSRX
A446902006CadillacSRX
A446902007CadillacSRX
A446902008CadillacSRX
A446902009CadillacSRX
A446902005CadillacSTS
A446902006CadillacSTS
A446902007CadillacSTS
A446902008CadillacSTS
B161970FordCustom
B161971FordCustom
B161972FordCustom
B161972FordCustom 500
B161973FordCustom 500
B31271986FordE-350 Econoline
B31271987FordE-350 Econoline
B31271978FordE-350 Econoline Club Wagon
B31271979FordE-350 Econoline Club Wagon
B31271986FordF-350
B31271987FordF-350
B31271978FordFairmont
B31271979FordFairmont
B31271980FordFairmont
B31271981FordFairmont
B31271982FordFairmont
B31271978FordGranada
B31271979FordGranada
B31271980FordGranada
B31271981FordGranada
B31271978FordLTD
B31271987FordLTD Crown Victoria
B31271988FordLTD Crown Victoria
B31271989FordLTD Crown Victoria
B31271978FordLTD II
B31271979FordLTD II
B31271979FordMustang
B31271980FordMustang
B31271981FordMustang
B31271982FordMustang
B31271978FordMustang II
B31271978FordRanchero
B31271979FordRanchero
B31271978FordThunderbird
B31271979FordThunderbird

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


Output

skuyearmake modelCombine yearmake model
100747900A00PG2012TeslaS2012-2016TeslaS
100747900A00PG2013TeslaS
100747900A00PG2014TeslaS
100747900A00PG2015TeslaS
100747900A00PG2016TeslaS
26300-355032008HyundaiAccent2008 -2016HyundaiAccent
26300-355032009HyundaiAccent
26300-355032010HyundaiAccent
26300-355032011HyundaiAccent
26300-355032012HyundaiAccent
26300-355032013HyundaiAccent
26300-355032014HyundaiAccent
26300-355032015HyundaiAccent
26300-355032016HyundaiAccent
26300-355032017HyundaiXYZ2017HyundaiXYZ
26300-355032008HyundaiElantra2008-2015HyundaiElantra
26300-355032009HyundaiElantra
26300-355032010HyundaiElantra
26300-355032011HyundaiElantra
26300-355032012HyundaiElantra
26300-355032013HyundaiElantra
26300-355032014HyundaiElantra
26300-355032015HyundaiElantra
26300-355032013HyundaiElantra Coupe2013-2014HyundaiElantra Coupe
26300-355032014HyundaiElantra Coupe
26300-355032013HyundaiElantra GT2013-2016HyundaiElantra GT
26300-355032014HyundaiElantra GT
26300-355032015HyundaiElantra GT
26300-355032016HyundaiElantra GT
26300-355032013HyundaiGenesis Coupe2013-2014HyundaiGenesis Coupe
26300-355032014HyundaiGenesis Coupe
26300-355032017HyundaiIoniq2017-2018HyundaiIoniq
26300-355032018HyundaiIoniq
26300-355032018KiaForte52018KiaForte5
26300-355032008KiaMagentis2008-2010KiaMagentis
26300-355032009KiaMagentis
26300-355032010KiaMagentis
26300-355032017KiaNiro2017-2018KiaNiro
26300-355032018KiaNiro
A446902004CadillacSRX2004-2009CadillacSRX
A446902005CadillacSRX
A446902006CadillacSRX
A446902007CadillacSRX
A446902008CadillacSRX
A446902009CadillacSRX
A446902005CadillacSTS2005-2008CadillacSTS
A446902006CadillacSTS
A446902007CadillacSTS
A446902008CadillacSTS
B161970FordCustom1970-1972FordCustom
B161971FordCustom
B161972FordCustom
B161972FordCustom 5001972-1973FordCustom 500
B161973FordCustom 500
B31271986FordE-350 Econoline1986-1987FordE-350 Econoline
B31271987FordE-350 Econoline
B31271978FordE-350 Econoline Club Wagon1978-1979FordE-350 Econoline Club Wagon
B31271979FordE-350 Econoline Club Wagon
B31271986FordF-3501986-1987FordF-350
B31271987FordF-350
B31271978FordFairmont1978-1982FordFairmont
B31271979FordFairmont
B31271980FordFairmont
B31271981FordFairmont
B31271982FordFairmont
B31271978FordGranada1978-1981FordGranada
B31271979FordGranada
B31271980FordGranada
B31271981FordGranada
B31271978FordLTD1978FordLTD
B31271987FordLTD Crown Victoria1987-1989FordLTD Crown Victoria
B31271988FordLTD Crown Victoria
B31271989FordLTD Crown Victoria
B31271978FordLTD II1978-1979FordLTD II
B31271979FordLTD II
B31271979FordMustang1979-1982FordMustang
B31271980FordMustang
B31271981FordMustang
B31271982FordMustang
B31271978FordMustang II1978FordMustang II
B31271978FordRanchero1978-1979FordRanchero
B31271979FordRanchero
B31271978FordThunderbird1978-1979FordThunderbird
B31271979FordThunderbird

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

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

Regards,
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">sku</td><td style=";">year</td><td style=";">make </td><td style=";">model</td><td style=";">Combine year</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">100747900A00PG</td><td style="text-align: right;;">2012</td><td style=";">Tesla</td><td style=";">S</td><td style=";">2012-2016</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">100747900A00PG</td><td style="text-align: right;;">2013</td><td style=";">Tesla</td><td style=";">S</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">100747900A00PG</td><td style="text-align: right;;">2014</td><td style=";">Tesla</td><td style=";">S</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">100747900A00PG</td><td style="text-align: right;;">2015</td><td style=";">Tesla</td><td style=";">S</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">100747900A00PG</td><td style="text-align: right;;">2016</td><td style=";">Tesla</td><td style=";">S</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">26300-35503</td><td style="text-align: right;;">2008</td><td style=";">Hyundai</td><td style=";">Accent</td><td style=";">2008-2016</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">26300-35503</td><td style="text-align: right;;">2009</td><td style=";">Hyundai</td><td style=";">Accent</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">26300-35503</td><td style="text-align: right;;">2010</td><td style=";">Hyundai</td><td style=";">Accent</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">26300-35503</td><td style="text-align: right;;">2011</td><td style=";">Hyundai</td><td style=";">Accent</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">26300-35503</td><td style="text-align: right;;">2012</td><td style=";">Hyundai</td><td style=";">Accent</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">26300-35503</td><td style="text-align: right;;">2013</td><td style=";">Hyundai</td><td style=";">Accent</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">26300-35503</td><td style="text-align: right;;">2014</td><td style=";">Hyundai</td><td style=";">Accent</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">26300-35503</td><td style="text-align: right;;">2015</td><td style=";">Hyundai</td><td style=";">Accent</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">26300-35503</td><td style="text-align: right;;">2016</td><td style=";">Hyundai</td><td style=";">Accent</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">26300-35503</td><td style="text-align: right;;">2017</td><td style=";">Hyundai</td><td style=";">XYZ</td><td style="text-align: right;;">2017</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">26300-35503</td><td style="text-align: right;;">2008</td><td style=";">Hyundai</td><td style=";">Elantra</td><td style=";">2008-2015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">26300-35503</td><td style="text-align: right;;">2009</td><td style=";">Hyundai</td><td style=";">Elantra</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">26300-35503</td><td style="text-align: right;;">2010</td><td style=";">Hyundai</td><td style=";">Elantra</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">26300-35503</td><td style="text-align: right;;">2011</td><td style=";">Hyundai</td><td style=";">Elantra</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">26300-35503</td><td style="text-align: right;;">2012</td><td style=";">Hyundai</td><td style=";">Elantra</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">26300-35503</td><td style="text-align: right;;">2013</td><td style=";">Hyundai</td><td style=";">Elantra</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">26300-35503</td><td style="text-align: right;;">2014</td><td style=";">Hyundai</td><td style=";">Elantra</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">26300-35503</td><td style="text-align: right;;">2015</td><td style=";">Hyundai</td><td style=";">Elantra</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">26300-35503</td><td style="text-align: right;;">2013</td><td style=";">Hyundai</td><td style=";">Elantra Coupe</td><td style=";">2013-2014</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style=";">26300-35503</td><td style="text-align: right;;">2014</td><td style=";">Hyundai</td><td style=";">Elantra Coupe</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">26300-35503</td><td style="text-align: right;;">2013</td><td style=";">Hyundai</td><td style=";">Elantra GT</td><td style=";">2013-2016</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">26300-35503</td><td style="text-align: right;;">2014</td><td style=";">Hyundai</td><td style=";">Elantra GT</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style=";">26300-35503</td><td style="text-align: right;;">2015</td><td style=";">Hyundai</td><td style=";">Elantra GT</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style=";">26300-35503</td><td style="text-align: right;;">2016</td><td style=";">Hyundai</td><td style=";">Elantra GT</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style=";">26300-35503</td><td style="text-align: right;;">2013</td><td style=";">Hyundai</td><td style=";">Genesis Coupe</td><td style=";">2013-2014</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style=";">26300-35503</td><td style="text-align: right;;">2014</td><td style=";">Hyundai</td><td style=";">Genesis Coupe</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style=";">26300-35503</td><td style="text-align: right;;">2017</td><td style=";">Hyundai</td><td style=";">Ioniq</td><td style=";">2017-2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">34</td><td style=";">26300-35503</td><td style="text-align: right;;">2018</td><td style=";">Hyundai</td><td style=";">Ioniq</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">35</td><td style=";">26300-35503</td><td style="text-align: right;;">2018</td><td style=";">Kia</td><td style=";">Forte5</td><td style="text-align: right;;">2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">36</td><td style=";">26300-35503</td><td style="text-align: right;;">2008</td><td style=";">Kia</td><td style=";">Magentis</td><td style=";">2008-2010</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">37</td><td style=";">26300-35503</td><td style="text-align: right;;">2009</td><td style=";">Kia</td><td style=";">Magentis</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">38</td><td style=";">26300-35503</td><td style="text-align: right;;">2010</td><td style=";">Kia</td><td style=";">Magentis</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">39</td><td style=";">26300-35503</td><td style="text-align: right;;">2017</td><td style=";">Kia</td><td style=";">Niro</td><td style=";">2017-2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">40</td><td style=";">26300-35503</td><td style="text-align: right;;">2018</td><td style=";">Kia</td><td style=";">Niro</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">41</td><td style=";">A44690</td><td style="text-align: right;;">2004</td><td style=";">Cadillac</td><td style=";">SRX</td><td style=";">2004-2009</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">42</td><td style=";">A44690</td><td style="text-align: right;;">2005</td><td style=";">Cadillac</td><td style=";">SRX</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">43</td><td style=";">A44690</td><td style="text-align: right;;">2006</td><td style=";">Cadillac</td><td style=";">SRX</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">44</td><td style=";">A44690</td><td style="text-align: right;;">2007</td><td style=";">Cadillac</td><td style=";">SRX</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">45</td><td style=";">A44690</td><td style="text-align: right;;">2008</td><td style=";">Cadillac</td><td style=";">SRX</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">46</td><td style=";">A44690</td><td style="text-align: right;;">2009</td><td style=";">Cadillac</td><td style=";">SRX</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">47</td><td style=";">A44690</td><td style="text-align: right;;">2005</td><td style=";">Cadillac</td><td style=";">STS</td><td style=";">2005-2008</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">48</td><td style=";">A44690</td><td style="text-align: right;;">2006</td><td style=";">Cadillac</td><td style=";">STS</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">49</td><td style=";">A44690</td><td style="text-align: right;;">2007</td><td style=";">Cadillac</td><td style=";">STS</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">50</td><td style=";">A44690</td><td style="text-align: right;;">2008</td><td style=";">Cadillac</td><td style=";">STS</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">51</td><td style=";">B16</td><td style="text-align: right;;">1970</td><td style=";">Ford</td><td style=";">Custom</td><td style=";">1970-1972</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">52</td><td style=";">B16</td><td style="text-align: right;;">1971</td><td style=";">Ford</td><td style=";">Custom</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">53</td><td style=";">B16</td><td style="text-align: right;;">1972</td><td style=";">Ford</td><td style=";">Custom</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">54</td><td style=";">B16</td><td style="text-align: right;;">1972</td><td style=";">Ford</td><td style=";">Custom 500</td><td style=";">1972-1973</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">55</td><td style=";">B16</td><td style="text-align: right;;">1973</td><td style=";">Ford</td><td style=";">Custom 500</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">56</td><td style=";">B3127</td><td style="text-align: right;;">1986</td><td style=";">Ford</td><td style=";">E-350 Econoline</td><td style=";">1986-1987</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">57</td><td style=";">B3127</td><td style="text-align: right;;">1987</td><td style=";">Ford</td><td style=";">E-350 Econoline</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">58</td><td style=";">B3127</td><td style="text-align: right;;">1978</td><td style=";">Ford</td><td style=";">E-350 Econoline Club Wagon</td><td style=";">1978-1979</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">59</td><td style=";">B3127</td><td style="text-align: right;;">1979</td><td style=";">Ford</td><td style=";">E-350 Econoline Club Wagon</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">60</td><td style=";">B3127</td><td style="text-align: right;;">1986</td><td style=";">Ford</td><td style=";">F-350</td><td style=";">1986-1987</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">61</td><td style=";">B3127</td><td style="text-align: right;;">1987</td><td style=";">Ford</td><td style=";">F-350</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">62</td><td style=";">B3127</td><td style="text-align: right;;">1978</td><td style=";">Ford</td><td style=";">Fairmont</td><td style=";">1978-1982</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">63</td><td style=";">B3127</td><td style="text-align: right;;">1979</td><td style=";">Ford</td><td style=";">Fairmont</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">64</td><td style=";">B3127</td><td style="text-align: right;;">1980</td><td style=";">Ford</td><td style=";">Fairmont</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">65</td><td style=";">B3127</td><td style="text-align: right;;">1981</td><td style=";">Ford</td><td style=";">Fairmont</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">66</td><td style=";">B3127</td><td style="text-align: right;;">1982</td><td style=";">Ford</td><td style=";">Fairmont</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">67</td><td style=";">B3127</td><td style="text-align: right;;">1978</td><td style=";">Ford</td><td style=";">Granada</td><td style=";">1978-1981</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">68</td><td style=";">B3127</td><td style="text-align: right;;">1979</td><td style=";">Ford</td><td style=";">Granada</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">69</td><td style=";">B3127</td><td style="text-align: right;;">1980</td><td style=";">Ford</td><td style=";">Granada</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">70</td><td style=";">B3127</td><td style="text-align: right;;">1981</td><td style=";">Ford</td><td style=";">Granada</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">71</td><td style=";">B3127</td><td style="text-align: right;;">1978</td><td style=";">Ford</td><td style=";">LTD</td><td style="text-align: right;;">1978</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">72</td><td style=";">B3127</td><td style="text-align: right;;">1987</td><td style=";">Ford</td><td style=";">LTD Crown Victoria</td><td style=";">1987-1989</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">73</td><td style=";">B3127</td><td style="text-align: right;;">1988</td><td style=";">Ford</td><td style=";">LTD Crown Victoria</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">74</td><td style=";">B3127</td><td style="text-align: right;;">1989</td><td style=";">Ford</td><td style=";">LTD Crown Victoria</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">75</td><td style=";">B3127</td><td style="text-align: right;;">1978</td><td style=";">Ford</td><td style=";">LTD II</td><td style=";">1978-1979</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">76</td><td style=";">B3127</td><td style="text-align: right;;">1979</td><td style=";">Ford</td><td style=";">LTD II</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">77</td><td style=";">B3127</td><td style="text-align: right;;">1979</td><td style=";">Ford</td><td style=";">Mustang</td><td style=";">1979-1982</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">78</td><td style=";">B3127</td><td style="text-align: right;;">1980</td><td style=";">Ford</td><td style=";">Mustang</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">79</td><td style=";">B3127</td><td style="text-align: right;;">1981</td><td style=";">Ford</td><td style=";">Mustang</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">80</td><td style=";">B3127</td><td style="text-align: right;;">1982</td><td style=";">Ford</td><td style=";">Mustang</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">81</td><td style=";">B3127</td><td style="text-align: right;;">1978</td><td style=";">Ford</td><td style=";">Mustang II</td><td style="text-align: right;;">1978</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">82</td><td style=";">B3127</td><td style="text-align: right;;">1978</td><td style=";">Ford</td><td style=";">Ranchero</td><td style=";">1978-1979</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">83</td><td style=";">B3127</td><td style="text-align: right;;">1979</td><td style=";">Ford</td><td style=";">Ranchero</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">84</td><td style=";">B3127</td><td style="text-align: right;;">1978</td><td style=";">Ford</td><td style=";">Thunderbird</td><td style=";">1978-1979</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">85</td><td style=";">B3127</td><td style="text-align: right;;">1979</td><td style=";">Ford</td><td style=";">Thunderbird</td><td style=";"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Out put</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">{=IF(<font color="Blue">COUNTIFS(<font color="Red">A$2:A2,A2,D$2:D2,D2</font>)=1,MIN(<font color="Red">IF(<font color="Green">A$2:A$85=A2,IF(<font color="Purple">D$2:D$85=D2,B$2:B$85</font>)</font>)</font>)&IF(<font color="Red">COUNTIFS(<font color="Green">A$2:A$85,A2,B$2:B$85,B2,D$2:D$85,D2</font>)=COUNTIFS(<font color="Green">A$2:A$85,A2,D$2:D$85,D2</font>),"","-"&MAX(<font color="Green">IF(<font color="Purple">A$2:A$85=A2,IF(<font color="Teal">D$2:D$85=D2,B$2:B$85</font>)</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

michellejames

New Member
Joined
Aug 16, 2013
Messages
26
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,
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,704
Office Version
365
Platform
Windows
My Anti-virus software blocked the download, and flagged it as dangerous.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,112
Messages
5,466,741
Members
406,495
Latest member
Arlind Elezi

This Week's Hot Topics

Top