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.

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,
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
Find the min year and max year in each subset.
Replies
8
Views
719
Fluff
Fluff
M
  • Question
combine each subset values in their respective place
Replies
5
Views
674
Fluff
Fluff

Forum statistics

Threads
1,214,625
Messages
6,120,598
Members
448,973
Latest member
ksonnia

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