Need code to consolidate rows if parts numbers match

[clent724]

I am not an excel expert and would love to get help with the issue I'm having. I will paste a link to my excel file and also a screenshot of it.

In column B there is multiple rows with the same part number. I need to delete the duplicates so there is only 1 row with that part number and combine the different categories in column L. The file I am working on has about 2500 rows so I shortened it and uploaded to my onedrive.

Here is the file before: https://onedrive.live.com/redir?resi...int=file%2ccsv

and here is what I'd like the final product to look like: https://onedrive.live.com/redir?resi...int=file%2ccsv


Thank you in advance for any help!

[mrice]

Try this macro. It should work if the data is sorted by SKU

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010/2013.

[jindon]

This will give you the result in other sheet and no need to be sorted by col.B

Please Login or Register  to view this content.

[clent724]

Unfortunately neither of those worked. I attached a link of the complete file I am working on. Here is a better example of what I need.

B2, B3, B4, B5 and B6 are all duplicates. I need them combined into one. All other data is the same in every other row except for L2, L3, L4, L5 and L6. Those would need to be combined with a ; between them if the part number in column B match.

Full excel file: https://onedrive.live.com/redir?resi...int=file%2ccsv


Curt.jpg

[mrice]

When you say that the code didn't work, what do you actually mean? Did you get error messages, did it do something unwanted to the data or did it do nothing at all? It appeared to work for me on the large file although it took quite a long time to run.

The code needs to be pasted into a module and run.

[clent724]

When you say that the code didn't work, what do you actually mean? Did you get error messages, did it do something unwanted to the data or did it do nothing at all? It appeared to work for me on the large file although it took quite a long time to run.

The code needs to be pasted into a module and run.

I'm sorry, I should described what happened. It just went on and on forever and keeps crashing excel. You tried it with the file I supplied and it worked for you then?

[jindon]

Just try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Since concatenation makes a long string, it might not display properly though.

[clent724]

It gives an error saying:

Compile Error Expected end sub

and highlights Sub test () in yellow. I changed like you said and here is what I ended up pasting in the module window:

Sub test()
Dim a, i As Long, ii As Long
a = Cells(1).CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 1 To UBound(a, 1)
If Not .exists(a(i, 2)) Then
.Item(a(i, 2)) = .Count + 1
For ii = 1 To UBound(a, 2)
a(.Count, ii) = a(i, ii)
Next
Else
a(.Item(a(i, 2)), 12) = _
Join(Array(a(.Item(a(i, 2)), 12), a(i, 12)), vbLf)
End If
Next
i = .Count
End With
Sheets.Add.Cells(1).Resize(i, UBound(a, 2)).Value = a

Is that correct?

[clent724]

Whenever I shortened the amount of rows in my excel file to 100 rows so I could test this code again it worked but in column L it did not put ; between the values; For example, it ended up like this:

SEARCH 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/1997

and I need it like this:

SEARCH 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/1997


I am going to try that macro on a different PC with the full file and hope that it doesn't crash but most likely I'm thinking because I have so many rows (110,033) it will continue happening. If I absolutely need to, I will split the file up into 4 files just to get the end result I need.




Edit: After the file was shortened I tried the very first code and it did put the ; between the files like I need. I guess I will have to make a few smaller files and try that way. I will let you know how it goes.

[jindon]

You can change vbLf to ";"

Please Login or Register  to view this content.

You must edit your post and wrap the code with the code tag.
It's a MUST forum rule.
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

[clent724]

Thank you, that code worked perfectly. Now I am having an issue after all data has been consolidated. In column L there is some duplicates now since some of the parts were in so many rows. Here is an example of one value:

SEARCH BY MANUFACTURER/Curt Manufacturing/Chevrolet/C3500/1999;SEARCH BY MANUFACTURER/Curt Manufacturing/Chevrolet/C1500/1988;SEARCH BY MANUFACTURER/Curt Manufacturing/GMC/C2500/1989;SEARCH BY MANUFACTURER/Curt Manufacturing/GMC/C2500/1988;SEARCH BY MANUFACTURER/Curt Manufacturing/GMC/C2500/1989;SEARCH BY MANUFACTURER/Curt Manufacturing/GMC/C2500/1989;SEARCH BY MANUFACTURER/Curt Manufacturing/GMC/C2500/1989;SEARCH BY MANUFACTURER/Curt Manufacturing/GMC/C2500/1989;SEARCH BY MANUFACTURER/Curt Manufacturing/GMC/C2500/1989;
SEARCH BY MANUFACTURER/Curt Manufacturing/GMC/C2500/1990;SEARCH BY MANUFACTURER/Curt Manufacturing/GMC/C2500/1990;SEARCH BY MANUFACTURER/Curt Manufacturing/GMC/C2500/1990;

Is there a way to highlight column L and remove these duplicates? I do need to keep the ; between each remaining one though.

Thank you so much!

[jindon]

clent724,...................

You must edit your post and wrap the code with the code tag.
It's a MUST forum rule.
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]