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!

 

[hiker95]

clent724,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


You are posting pictures. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense and I doubt that you would get an answer.


I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


If you are not able to provide screenshots, then:

You can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

 

[Trevor_S]

Sort the data by column A
In cell F1 enter =E1
In cell F2 enter =if(A1=A2,F1&";"&E2,E2)
Copy formula down alongside all rows
In cell G1 enter =if(A1=A2,"","#")
Copy formula down alongside all rows.
Column F builds up the description for matching items, and column G indicates the final row of matching items. Copy and paste special values columns F and G. Then sort on column G, and delete all rows where column G is blank. Delete column G then delete column E, and you should get the result you want.

 

[hiker95]

clent724,

Here is a macro solution for you to consider, based on the raw data structure that you have supplied pictures of, where the raw data is grouped in column A by Part Number.

Sample raw data in worksheet tmp:

Excel 2007
	A	B	C	D	E
1	Part Number	Brand	Short Description	Extended Descrption	Part Number
2	31101	Husky Liners	FLOOR LINER	Classic Style Front Floor Liners	SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/Blazer/1992-1994
3	31101	Husky Liners	FLOOR LINER	Classic Style Front Floor Liners	SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500/1988-1999
4	31101	Husky Liners	FLOOR LINER	Classic Style Front Floor Liners	SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500/1988-1998
5	31101	Husky Liners	FLOOR LINER	Classic Style Front Floor Liners	SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500 Suburban/1992-1995
6	33333	Husky Liners	FLOOR LINER	Classic Style Front Floor Liners	SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/Blazer/1992-1994
7	33333	Husky Liners	FLOOR LINER	Classic Style Front Floor Liners	SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500/1988-1999
8	33333	Husky Liners	FLOOR LINER	Classic Style Front Floor Liners	SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500/1988-1998
9	33333	Husky Liners	FLOOR LINER	Classic Style Front Floor Liners	SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500 Suburban/1992-1995
10	33333	Husky Liners	FLOOR LINER	Classic Style Front Floor Liners	SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500 Suburban/1996-2000
11
tmp

And, after the macro:

Excel 2007
	A	B	C	D	E
1	Part Number	Brand	Short Description	Extended Descrption	Part Number
2	31101	Husky Liners	FLOOR LINER	Classic Style Front Floor Liners	SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/Blazer/1992-1994;SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500/1988-1999;SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500/1988-1998;SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500 Suburban/1992-1995
3	33333	Husky Liners	FLOOR LINER	Classic Style Front Floor Liners	SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/Blazer/1992-1994;SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500/1988-1999;SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500/1988-1998;SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500 Suburban/1992-1995;SEARCH BY MANUFACTURER/Husky Liners/Cheverolet/C1500 Suburban/1996-2000
4
5
6
7
8
9
10
11
tmp

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ConsolidateData()
' hiker95, 02/26/2016, ME924474
Dim lr As Long, r As Long, n As Long, ew As Double
Application.ScreenUpdating = False
With Sheets("tmp")
  ew = .Columns("E:E").ColumnWidth
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For r = 2 To lr
    n = Application.CountIf(.Columns(1), .Cells(r, 1).Value)
    If n > 1 Then
      .Range("E" & r) = Join(Application.Transpose(.Range("E" & r & ":E" & r + n - 1)), ";")
      .Range("A" & r + 1 & ":E" & r + n - 1).ClearContents
    End If
    r = r + n - 1
  Next r
  On Error Resume Next
  .Range("A2:A" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  On Error GoTo 0
  .Columns("E:E").ColumnWidth = ew
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  .Range("E2:E" & lr).WrapText = True
  .UsedRange.Rows.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ConsolidateData macro.

 

[Peter_SSs]

Welcome to the MrExcel board!

I think this should also do what you want, and much faster - if you have nearly 10,000 rows and each part number uses about 4 rows like your sample, approximately 30 times faster by my testing.

Test in a copy of your workbook.

Sub clent724_Rearrange()
  Dim a, b
  Dim i As Long, k As Long
  
  With Sheets("tmp").Range("A1", Sheets("tmp").Range("E" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To UBound(a), 1 To 5)
    For i = 2 To UBound(a)
      If a(i, 1) = a(i - 1, 1) Then
        b(k, 5) = b(k, 5) & ";" & a(i, 5)
      Else
        k = k + 1
        b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(i, 3): b(k, 4) = a(i, 4): b(k, 5) = a(i, 5)
      End If
    Next i
    .Offset(1).Value = b
  End With
End Sub

 

[hiker95]

Peter_SSs,

Nicely done as always. I had found an old macro of yours that worked the same, but, I misplaced it.

The new macro is now in a safe place in my archives.

Thanks again.


clent724,

With the same screenshots as my reply #4.

Here is another macro for you to consider, where the raw data is grouped in column A by Part Number, that is a variation of Peter_SSs' macro code, that also uses two arrays in memory, and, will wrap the text in column E (per your pictures in your original reply #1).

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub ConsolidateData_Arrays()
' hiker95, 02/27/2016, ME924474
Dim a As Variant, i As Long, o As Variant, j As Long, c As Long
Application.ScreenUpdating = False
With Sheets("tmp")
  a = .Cells(1).CurrentRegion
  ReDim o(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 2 To UBound(a)
    If a(i, 1) = a(i - 1, 1) Then
      o(j, 5) = o(j, 5) & ";" & a(i, 5)
    Else
      j = j + 1
      For c = 1 To UBound(a, 2)
        o(j, c) = a(i, c)
      Next c
    End If
  Next i
  .Cells(2, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Range("E2:E" & UBound(o, 1)).WrapText = True
  .UsedRange.Rows.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ConsolidateData_Arrays macro.

 

[clent724]

Thank you all so much! This worked wonderful! I am so grateful I found this forum!

 

[hiker95]

clent724,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

 

[clent724]

I am having an issue with the code. I changed the file around a little and now I have the final product and am about ready to import it to our website but the code is no longer working. I am lost when attempting to edit this code to work since I have switched a few columns around to work with our importing template on our website.

Here is the file I am working on: https://onedrive.live.com/redir?resid=C7DDACAF447A40C4!182&authkey=!ADBAwr0GVEWtnGc&ithint=file,csv

Here is a screenshot of the file: https://onedrive.live.com/redir?resid=C7DDACAF447A40C4!188&authkey=!ADNHq9AjGRDf9T4&ithint=folder,

I am very sorry to be a bother again.

 

[hiker95]

I am lost when attempting to edit this code to work since I have switched a few columns around to work with our importing template on our website.

clent724,

It would appear that both workbooks are identical, with one worksheet each, and, the last row is 110,033.

Column A is blank except for the title in cell A1, and, there are titles, and, data going out to column R.


Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.