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

C

clent724

New Member
Joined
Feb 26, 2016
Messages
18
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!

example1.jpg




example2.jpg
 
Sort by date Sort by votes
clent724 said:
I changed the file around a little ....
Click to expand...
I have only looked at the image, not the actual file (Many helpers here choose not to download files from other sites or, due to security restrictions at work sites, are unable to download such files), but if, as hiker says, you now have 110,000+ rows and 18 columns instead of 9,800 rows and 5 columns, I'd call that more than a "little" change. ;)

However, ..

- Post #1 referred to "Part numbers in column A". I can't see a heading "Part number" anywhere in that image. Do you mean "Product C..." in column B now? If not, what?

- Post #1 wanted column E values combined. From what I can see in the image, nothing is in column E. What column values should be combined now?

- The earlier code inserted the repeated values from the remaining columns (which were B, C & D only) into the results. Which, if any, of the remaining 16 columns now do you need in the resultant table?
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I'm so sorry to bother you with this. I know you are busy.

I will be using the row #1 as a template for all future imports onto our website. Part number (Product Code) is now in column B

Now instead of columns E values being combined, I would need column L values combined.

For example, rows 2, 3, 4, 5 and 6 are all the same with the exception of column L (which I would like to be combined with a ; between them).

The last file was the brand name Husky and this one is brand name Curt which is a larger manufacturer of products that is why there's so many more rows. Like I said though, I will always retain the row #1 as a template for in the future. In the screenshot you can see for example that column F is blank in this worksheet but some of my future worksheets may have values in those columns. Although it is currently blank, hopefully there is a code that can retain that information for in the future so I won't have to continue asking for help.

It is absolutely amazing the things that I have already learned from this forum. Thank you again!
 
Upvote 0
clent724,

In order to continue, we will need another workbook, with one worksheet containing the raw data, and, one worksheet containing the results (manually formatted by you for the results you are looking for).

You can post the workbook/worksheets on your one drive again, or:

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
 
Upvote 0
hiker95 said:
clent724,

In order to continue, we will need another workbook, with one worksheet containing the raw data, and, one worksheet containing the results (manually formatted by you for the results you are looking for).

You can post the workbook/worksheets on your one drive again, or:

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
Click to expand...

I created the workbook with a "before" and an "after" worksheet.

Here is a onedrive link to the workbook: https://onedrive.live.com/redir?resid=C7DDACAF447A40C4!190&authkey=!ACVTCluillL5LQI&ithint=file,xlsx

Thank you
 
Upvote 0
clent724,

Thanks for the new workbook.

Here is another macro solution for you to consider based on your latest workbook.

You can change the raw data worksheet name in the macro.

Sample raw data (not all 100 rows, and, columns, are shown for brevity):


Excel 2007
ABLR
1Item TypeProduct Code/SKUCategoryBrand Name
211014SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/2001Curt Manufacturing
311014SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/2000Curt Manufacturing
411014SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/1999Curt Manufacturing
511014SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/1998Curt Manufacturing
611014SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/1997Curt Manufacturing
7111481SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/2000Curt Manufacturing
8111481SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/1999Curt Manufacturing
9111481SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/1998Curt Manufacturing
10111481SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/1997Curt Manufacturing
11111481SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/1996Curt Manufacturing
12120521SEARCH BY MANUFACTURER/Curt Manufacturing/Cadillac/DeVille/1996Curt Manufacturing
13120521SEARCH BY MANUFACTURER/Curt Manufacturing/Cadillac/DeVille/1994Curt Manufacturing
14120521SEARCH BY MANUFACTURER/Curt Manufacturing/Cadillac/DeVille/1995Curt Manufacturing
1512224SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I30/2001Curt Manufacturing
1612224SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I30/2000Curt Manufacturing
1712224SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I35/2004Curt Manufacturing
1812224SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I35/2003Curt Manufacturing
1912224SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I35/2002Curt Manufacturing
2012224SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Maxima/2003Curt Manufacturing
2112224SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Maxima/2002Curt Manufacturing
2212224SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Maxima/2001Curt Manufacturing
2312224SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Maxima/2000Curt Manufacturing
tmp


And, after the macro:


Excel 2007
ABLR
1Item TypeProduct Code/SKUCategoryBrand Name
211014SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/2001;SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/1997Curt Manufacturing
3111481SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/1996Curt Manufacturing
4120521SEARCH BY MANUFACTURER/Curt Manufacturing/Cadillac/DeVille/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Cadillac/DeVille/1994;SEARCH BY MANUFACTURER/Curt Manufacturing/Cadillac/DeVille/1995Curt Manufacturing
512224SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I30/2001;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I30/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I35/2004;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I35/2003;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I35/2002;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Maxima/2003;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Maxima/2002;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Maxima/2001;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Maxima/2000Curt Manufacturing
613156SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/2003;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/2002;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/2001;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/2003;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/2004;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/2002;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/2001Curt Manufacturing
716437SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150 Heritage/2004;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/2001;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/2002;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/2003;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-250/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-250/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-250/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-250/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-250/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-250/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-250/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-250/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-250/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-250/1998Curt Manufacturing
828321SEARCH BY MANUFACTURER/Curt Manufacturing///Curt Manufacturing
945295SEARCH BY MANUFACTURER/Curt Manufacturing///Curt Manufacturing
1055345SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Mercury/Mountaineer/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Mercury/Mountaineer/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Mercury/Mountaineer/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Mercury/Mountaineer/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Mercury/Mountaineer/2001;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1995;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1995;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1995;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1995;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1995;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1995;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/2001;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/2001Curt Manufacturing
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).

Code: 
Sub ConsolidateData_V2()
' hiker95, 03/08/2016, ME924474
Dim lr As Long, r As Long, n As Long, lw As Double
Application.ScreenUpdating = False
With Sheets("tmp")    '<-- you can change the sheet name here
  lw = .Columns("L:L").ColumnWidth
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  For r = 2 To lr
    n = Application.CountIf(.Columns(2), .Cells(r, 2).Value)
    If n > 1 Then
      .Range("L" & r) = Join(Application.Transpose(.Range("L" & r & ":L" & r + n - 1)), ";")
      .Range("A" & r + 1 & ":R" & r + n - 1).ClearContents
    End If
    r = r + n - 1
  Next r
  On Error Resume Next
  .Range("B2:B" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  On Error GoTo 0
  .Columns("L:L").ColumnWidth = lw
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  .Range("L2:L" & 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_V2 macro.
 
Upvote 0
Thank you! This worked good but in column L there are some duplicates now. Here is an example:

[TABLE="width: 381"]
<tbody>[TR]
[TD]SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1995;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1995;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1995;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1995;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1995;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1996

Is there a way to locate and remove these duplicates and still keep the ; between the remaining categories?
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
 
Upvote 0
Thank you! This worked good but in column L there are some duplicates now.
Click to expand...

clent724,

Here is a new macro for you to consider.

You can change the raw data worksheet name in the macro.

With the same beginning screenshot in my reply #16.

Sample results after the new macro:


Excel 2007
ABLR
1Item TypeProduct Code/SKUCategoryBrand Name
211014SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/2001;SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Honda/CR-V/1997Curt Manufacturing
3111481SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Hyundai/Elantra/1996Curt Manufacturing
4120521SEARCH BY MANUFACTURER/Curt Manufacturing/Cadillac/DeVille/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Cadillac/DeVille/1994;SEARCH BY MANUFACTURER/Curt Manufacturing/Cadillac/DeVille/1995Curt Manufacturing
512224SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I30/2001;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I30/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I35/2004;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I35/2003;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/I35/2002;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Maxima/2003;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Maxima/2002;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Maxima/2001;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Maxima/2000Curt Manufacturing
613156SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/2003;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/2002;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/2001;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Infiniti/QX4/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/2003;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/2004;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/2002;SEARCH BY MANUFACTURER/Curt Manufacturing/Nissan/Pathfinder/2001Curt Manufacturing
716437SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150 Heritage/2004;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/2001;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/2002;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-150/2003;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-250/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-250/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/F-250/1998Curt Manufacturing
828321SEARCH BY MANUFACTURER/Curt Manufacturing///Curt Manufacturing
945295SEARCH BY MANUFACTURER/Curt Manufacturing///Curt Manufacturing
1055345SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Mercury/Mountaineer/1997;SEARCH BY MANUFACTURER/Curt Manufacturing/Mercury/Mountaineer/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Mercury/Mountaineer/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Mercury/Mountaineer/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Mercury/Mountaineer/2001;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1995;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1996;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1998;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/2000;SEARCH BY MANUFACTURER/Curt Manufacturing/Ford/Explorer/2001Curt Manufacturing
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).

Code: 
Sub ConsolidateData_V3()
' hiker95, 03/09/2016, ME924474
Dim lr As Long, r As Long, n As Long, lw As Double
Application.ScreenUpdating = False
With Sheets("tmp")    '<-- you can change the sheet name here
  lw = .Columns("L:L").ColumnWidth
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  With .Range("S2:S" & lr)
    .Formula = "=B2&L2&R2"
    .Value = .Value
  End With
  With .Range("T2:T" & lr)
    .Formula = "=IF(COUNTIF($S$2:S2,S2)>1,""#N/A"","""")"
    .Value = .Value
  End With
  On Error Resume Next
  .Range("T2:T" & lr).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
  .Range("S2:T" & lr).ClearContents
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  lw = .Columns("L:L").ColumnWidth
  For r = 2 To lr
    n = Application.CountIf(.Columns(2), .Cells(r, 2).Value)
    If n > 1 Then
      .Range("L" & r) = Join(Application.Transpose(.Range("L" & r & ":L" & r + n - 1)), ";")
      .Range("A" & r + 1 & ":R" & r + n - 1).ClearContents
    End If
    r = r + n - 1
  Next r
  On Error Resume Next
  .Range("B2:B" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  On Error GoTo 0
  .Columns("L:L").ColumnWidth = lw
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  .Range("L2:L" & lr).WrapText = True
  .Rows(1).Resize(lr).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_V3 macro.
 
Last edited:
Upvote 0
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.

Rich (BB code): 
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
 
Last edited:
Upvote 0
Peter_SSs said:
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....
Click to expand...


I just tried this code this morning. I was out of the office for a while. This worked wonderfully! I really appreciate all of your help.
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Finding all combinations to a summed value
Replies
9
Views
289
cerberus1845
C
B
  • Question Question
Use INDIRECT combined with INDEX MATCH (multiple row criteria) to return values from specified sheet
Replies
4
Views
547
Peter_SSs
Peter_SSs
J
  • Solved
help matching & joining
Replies
4
Views
77
joeloveszoe
J
B
  • Solved
VLOOKUP issue with wildcard parameter for partial matches in number/letter combinations
2
Replies
10
Views
753
BrooksProctor
B
B
  • Solved
combining column data with a twist
Replies
5
Views
246
Boost001
B

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

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