combine each subset values in their respective place

M

michellejames

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

<tbody>
</tbody>


Regards,
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
How about
Code: 
Sub michellejames()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("J2", Range("J" & Rows.Count).End(xlUp).Offset(1))
         If Not .Exists(Cl.Value) Then
            .Item(Cl.Value) = Cl.Offset(, 8).Value
            Cl.Offset(-1, 9) = .Item(Cl.Offset(-1).Value)
         Else
            .Item(Cl.Value) = .Item(Cl.Value) & ", " & Cl.Offset(, 8).Value
         End If
      Next Cl
   End With
End Sub
 
Upvote 0
Hi,

If you have the TEXTJOIN function, it will probably do what you want, but I Don't have that function and I'm not sure.

Do you mind using a Helper Column that you can hide out of sight?
 
Upvote 0
Hi There,

Greeting! Again thanks. It working fine.

Regards,

Fluff said:
How about
Code: 
Sub michellejames()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("J2", Range("J" & Rows.Count).End(xlUp).Offset(1))
         If Not .Exists(Cl.Value) Then
            .Item(Cl.Value) = Cl.Offset(, 8).Value
            Cl.Offset(-1, 9) = .Item(Cl.Offset(-1).Value)
         Else
            .Item(Cl.Value) = .Item(Cl.Value) & ", " & Cl.Offset(, 8).Value
         End If
      Next Cl
   End With
End Sub
Click to expand...
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Color duplicates rows in two different sets of data and also the rows which are present in both sets of data.
Replies
0
Views
340
Noob_Excel
N
M
  • Question
Find the min year and max year in each subset.
Replies
8
Views
724
Fluff
Fluff
M
  • Question
How to combine year?
Replies
5
Views
831
Fluff
Fluff
J
  • Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
923
JEC
JEC
K
  • Question
Grouping or sum if
Replies
6
Views
611
Aladin Akyurek
Aladin Akyurek

Forum statistics

Threads
1,214,790
Messages
6,121,608
Members
449,038
Latest member
apwr

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