If cells in a column match then combine all values in another column

[clent724]

I am not sure if there's even a formula for this to work. I attached images to show what my data looks like now and what I would like as the end result. The file I am working on actually has 9800 rows so this would be such a huge relief to have this work.

Basically, if part numbers in column A match then I need to combine the values in column E with a ; in between them.

Then the remaining data in columns 3, 4 and 5 can be removed since they are the same as row 2. This is just an example. The file I will be working on actually had around 9800 rows with many different part numbers.

I tried doing my best to explain this and feel that the pictures will help. I'm very sorry if I'm not clear. Thanks in advance for any help!

 

[clent724]

Admittedly my machine is a few years old but with over 100,000 rows of test data, after about 10 minutes of trying to run the code from post #18, my Excel (2010) gave up & crashed.

The code below processed the same data in about 6-8 seconds so you could also give it a try.
You can change the columns of interest by editing the 'Const' lines near the start of the code.
I've also used 'tmp' as the sheet name to work on. You can change that name or just change that whole line to "With ActiveSheet" if that is relevant.

I haven't looked at your actual files but from the look of the link you may be dealing with a csv. If that is the case then some of the formatting applied by both my code & hiker's may not be relevant. Post back if any of that is causing problems or you just want to remove the unnecessary processing anyway.

Sub clent724_Rearrange_v2()
  Dim a, b, c, d
  Dim i As Long, k As Long, lr As Long
  Dim dict As Object
  
  Const ProductCol As Long = 2      '<- Column B
  Const ColToCombine As Long = 12   '<- Column L
  
  Set dict = CreateObject("Scripting.Dictionary")
  dict.CompareMode = 1
  Application.ScreenUpdating = False
  With Sheets("tmp")
    lr = .Cells(.Rows.Count, ProductCol).End(xlUp).Row
    a = .Cells(1, ProductCol).Resize(lr + 1).Value
    b = .Cells(1, ColToCombine).Resize(lr).Value
    ReDim c(1 To lr, 1 To 1)
    ReDim d(1 To lr, 1 To 1) As String
    For i = 1 To lr
      dict(b(i, 1)) = 1
      If a(i, 1) <> a(i + 1, 1) Then
        k = k + 1
        c(i, 1) = k
        d(k, 1) = Join(dict.keys, ";" & Chr(10))
        dict.RemoveAll
      End If
    Next i
    With .Cells(1, ColToCombine).Resize(lr)
      .WrapText = True
      .Value = c
      .Parent.UsedRange.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      On Error Resume Next
      .SpecialCells(xlBlanks).EntireRow.Delete
      On Error GoTo 0
      .Resize(k).Value = d
      .Columns.AutoFit
      .Rows.AutoFit
    End With
  End With
  Application.ScreenUpdating = True
End Sub

I have been using this code and to this point it has worked great. I did run into an issue though. Because it combines many rows of data, the column L sometimes has too many lines or characters of data and it cuts off at a certain point and then the remaining data gets forced down a row and any data after my last column gets scrambled. I will post a screenshot of what I am talking about. It is hard for me to explain.

As you can see in the picture, M306 actually belongs down a column to 307. L304 is excessively long that is why it shoves everything down.

Is there a way to keep this from happening?

 

[clent724]

Bump

 

[Peter_SSs]

As you can see in the picture, M306 actually belongs down a column to 307. L304 is excessively long that is why it shoves everything down.

1. Did you mean down a row?

2. From that image I can't see that M306 should be anywhere else in particular. Remember we are not as familiar with your sheet as you are. What should I look for in that image to tell me M306 is in the wrong place?

3. Is that an image from before or after the code has been run?

4. From what I can actually see, nothing looks like it is or would be "excessively long". Can you quantify what that expression might mean?

 

[clent724]

1. Did you mean down a row?

2. From that image I can't see that M306 should be anywhere else in particular. Remember we are not as familiar with your sheet as you are. What should I look for in that image to tell me M306 is in the wrong place?

3. Is that an image from before or after the code has been run?

4. From what I can actually see, nothing looks like it is or would be "excessively long". Can you quantify what that expression might mean?

Yes, I'm sorry. I meant down a row. Here is another image to help clarify a little better.

L302 contains many lines of data after the code has been run. Since L302 has so much data it cuts off at a certain point and then in cell A304 it leaves: t Type=" and everything after that just disappears.

I know you previously said that people don't usually like to download excel files but if you do want to download the before the code and after the code files here is a link: https://onedrive.live.com/redir?resid=C7DDACAF447A40C4!218&authkey=!ADQ9yEhujBR4T9o&ithint=folder,

As you will be able to see in the file before the code, it combines rows 6616 through 7574 into one row (304) after the code and because there is so many vehicles the part fits it becomes excessively long and this issue happens.

<tbody>
</tbody>

 

[Peter_SSs]

From Excel specifications & limits:

Total number of characters that a cell can contain

32,767 characters

You are exceeding that limit.

You might want to consider whether you need all the information repeated in column L. For example, from a quick look it seems that perhaps in each line of the results in column L, the 'Vehicle Make' is the same and the 'Part Type' is the same.
- Is that correct?
- If so, could the first line in the result cell include that, but have those elements removed from subsequent lines in the result cell? I have no idea whether that would bring the character count back in the allowable range, but it may do.

 

[clent724]

From Excel specifications & limits:
You are exceeding that limit.

You might want to consider whether you need all the information repeated in column L. For example, from a quick look it seems that perhaps in each line of the results in column L, the 'Vehicle Make' is the same and the 'Part Type' is the same.
- Is that correct?
- If so, could the first line in the result cell include that, but have those elements removed from subsequent lines in the result cell? I have no idea whether that would bring the character count back in the allowable range, but it may do.

Yes, that would be just fine since most of them are duplicates. I'd like to thank you greatly for your help!!

 

[Peter_SSs]

Yes, that would be just fine since most of them are duplicates.

Data is still way too big. Further shavings could be made by, apart from the first item in each column L result:
- Remove the text "Vehicle " as it appears 3 times on each line
- Abbreviate "Year" to "Y" and "Model" to "M"

- Include multiples of these shorter results per line in column L. This does not reduce the number of characters per cell, but would allow you to see more results in the cell (row heights - and columns widths - have limits).

Results would be more like this, not particularly rows 2 & 3.

Excel Workbook
	L
2	Vehicle Year=2007-2014;Vehicle Make=Cadillac;Vehicle Model=Escalade;Part Type=Vinyl Protection FilmY=2007-2014;M=Escalade ESV: Y=2007-2013;M=Escalade EXT:
3	Vehicle Year=2007-2014;Vehicle Make=Cadillac;Vehicle Model=Escalade;Part Type=Vinyl Protection FilmY=2007-2014;M=Escalade ESV: Y=2007-2013;M=Escalade EXT:
4	Vehicle Year=2008-2010;Vehicle Make=Cadillac;Vehicle Model=CTS;Part Type=Vinyl Protection Film
5	Vehicle Year=2008-2010;Vehicle Make=Cadillac;Vehicle Model=CTS;Part Type=Vinyl Protection Film
Sample

However, doing all that, you still would have rows that exceed the cell limits.

I think you just have too much data to display in a cell. In any case, if it did fit, you wouldn't be able to view it easily as (I'm pretty sure) it wouldn't visibly fit even if column width & row height was maximised.

What are you hoping to actually do with these huge strings anyway?

 

[clent724]

Data is still way too big. Further shavings could be made by, apart from the first item in each column L result:
- Remove the text "Vehicle " as it appears 3 times on each line
- Abbreviate "Year" to "Y" and "Model" to "M"

- Include multiples of these shorter results per line in column L. This does not reduce the number of characters per cell, but would allow you to see more results in the cell (row heights - and columns widths - have limits).

Results would be more like this, not particularly rows 2 & 3.

Excel Workbook
	L
2	Vehicle Year=2007-2014;Vehicle Make=Cadillac;Vehicle Model=Escalade;Part Type=Vinyl Protection FilmY=2007-2014;M=Escalade ESV: Y=2007-2013;M=Escalade EXT:
3	Vehicle Year=2007-2014;Vehicle Make=Cadillac;Vehicle Model=Escalade;Part Type=Vinyl Protection FilmY=2007-2014;M=Escalade ESV: Y=2007-2013;M=Escalade EXT:
4	Vehicle Year=2008-2010;Vehicle Make=Cadillac;Vehicle Model=CTS;Part Type=Vinyl Protection Film
5	Vehicle Year=2008-2010;Vehicle Make=Cadillac;Vehicle Model=CTS;Part Type=Vinyl Protection Film
Sample

However, doing all that, you still would have rows that exceed the cell limits.

I think you just have too much data to display in a cell. In any case, if it did fit, you wouldn't be able to view it easily as (I'm pretty sure) it wouldn't visibly fit even if column width & row height was maximised.

What are you hoping to actually do with these huge strings anyway?

It is used on our website so the customer can filter their vehicle and see which part fits their vehicle.

The vehicle years could be included like you did to cut down on a lot of the data. Basically instead of a line with just Vehicle Year 2014 and another with Vehicle Year 2015 it could say Vehicle Year 2014-2015 as long as the Vehicle Model is the same.

 

[Peter_SSs]

Basically instead of a line with just Vehicle Year 2014 and another with Vehicle Year 2015 it could say Vehicle Year 2014-2015 as long as the Vehicle Model is the same.

I'm not sure how feasible that is. Here is a section from your actual data

Excel Workbook
	L
6858	Vehicle Year=1995;Vehicle Make=Toyota;Vehicle Model=Pickup;Part Type=Fender Flare
6859	Vehicle Year=1994;Vehicle Make=Toyota;Vehicle Model=Pickup;Part Type=Fender Flare
6860	Vehicle Year=1993;Vehicle Make=Toyota;Vehicle Model=Pickup;Part Type=Fender Flare
6861	Vehicle Year=1992;Vehicle Make=Toyota;Vehicle Model=Pickup;Part Type=Fender Flare
6862	Vehicle Year=1991;Vehicle Make=Toyota;Vehicle Model=Pickup;Part Type=Fender Flare
6863	Vehicle Year=1990;Vehicle Make=Toyota;Vehicle Model=Pickup;Part Type=Fender Flare
6864	Vehicle Year=1989;Vehicle Make=Toyota;Vehicle Model=Pickup;Part Type=Fender Flare
6865	Vehicle Year=1988;Vehicle Make=Toyota;Vehicle Model=Pickup;Part Type=Fender Flare
Husky before code (2)

What I think you are saying is that this could be combined into a single line like:
Vehicle Year=1988-1995;Vehicle Make=Toyota;Vehicle Model=Pickup;Part Type=Fender Flare
& then possibly further reduced to something like
Y=1988-1995;MK=Toyota;MD=Pickup;PT=Fender Flare
or even this?
1988-1995;Toyota;Pickup;Fender Flare

Is the above what you are meaning & if so, which one of those is preferred?

Also, if the above is correct, how do you envisage handling this section, also from your real data, noting that Year=2004 is missing?

Excel Workbook
	L
5982	Vehicle Year=2005;Vehicle Make=Chevrolet;Vehicle Model=Silverado 1500 HD;Part Type=Fender Flare
5983	Vehicle Year=2003;Vehicle Make=Chevrolet;Vehicle Model=Silverado 1500 HD;Part Type=Fender Flare
5984	Vehicle Year=2002;Vehicle Make=Chevrolet;Vehicle Model=Silverado 1500 HD;Part Type=Fender Flare
5985	Vehicle Year=2001;Vehicle Make=Chevrolet;Vehicle Model=Silverado 1500 HD;Part Type=Fender Flare
Husky before code (2)

Also note that it is best not quote whole posts, especially if they are longish. It makes reading & navigating the thread harder. If you want/need to quote, prune the quote to only include the part(s) that you want to specifically refer to, as I did with yours in this post.

 

[Peter_SSs]

Here's another one that looks like it would be difficult to handle with code. For each line, the Make, Model & Part Type are identical.
The individual year lines cover 2012-2016 (not in ascending or descending order) and the combined year line covers a different set of years 2011-2015.

Excel Workbook
	L
12185	Vehicle Year=2014;Vehicle Make=Toyota;Vehicle Model=Sienna;Part Type=Floor Liner
12186	Vehicle Year=2013;Vehicle Make=Toyota;Vehicle Model=Sienna;Part Type=Floor Liner
12187	Vehicle Year=2012;Vehicle Make=Toyota;Vehicle Model=Sienna;Part Type=Floor Liner
12188	Vehicle Year=2015;Vehicle Make=Toyota;Vehicle Model=Sienna;Part Type=Floor Liner
12189	Vehicle Year=2016;Vehicle Make=Toyota;Vehicle Model=Sienna;Part Type=Floor Liner
12190	Vehicle Year=2011-2015;Vehicle Make=Toyota;Vehicle Model=Sienna;Part Type=Floor Liner
Husky before code (2)