Home Ask Login Register

Developers Planet

Your answer is one click away!

Zeph February 2016

Excel Macro to combine cells of data when data matches in another column

The best way I can explain my problem is by showing a few screenshots.

I need to turn data like this:

[BEfore Data

Into something that displays like this:

After Data AFter Data

There are multiple part numbers in the file, and I need the macro to take all the data from a matching part number and transform the data into what is displayed in the second image. All the part numbers are grouped with their data together, so it wouldn't need to run the loop through the top every single time, but adding to the entries with each new piece of data. Something also needs to be done for the years as well, because the way the data is presented, is in a range of years, and I need an entry for each year in that range.

Additional Information:

I am using this data for prep for category data for a BigCommerce site, that is working with a year/make/model plugin on the site, to create a vehicle lookup system. Thus in order for the user to look up their vehicle accurately the categories need to be listed the way they are in the second picture, which needs to be the result of the macro.

I thank anyone who takes the time to look into this, it will cut down the time I spend doing this manually by a huge amount.


Scott Holtzman February 2016

You can do this with a formula (without actual VBA):

  1. In cell F2 write: ="YMM/"&C2&"/"&D2&"/"&E2&";"
  2. In cell F3 write: =F2&"YMM/"&C3&"/"&D3&"/"&E3&";"
  3. drag down the formula in F3 until the last row.
  4. The last row will contain the entire string of all vehicles.

enter image description here

I just noticed you may have duplicate values. You can use the built in Remove Duplicates feature to remove those before using the above technique.

Post Status

Asked in February 2016
Viewed 3,988 times
Voted 10
Answered 1 times


Leave an answer

Quote of the day: live life