Combining multiple cells into one (without losing data) if the rest of the row is equal

Hi,

Unfortunately I'm pretty much a novice when it comes to Excel so the more complex functions of the software seem to elude me so hopefully someone here can be of help.
I have a big file of close to 5000 rows I think. It contains parts for cars but one part can fit on a range of car models. I'd like to rework the list so it will show each part number in a row with in that row one car model with a range of years that car was made. It should not sum them all up but rather display only the lowest and highest value so it shows the range.
I think the attached simplified spreadsheet will make things more clearly. car_applicationlist.xlsx
Many thanks for any help you can offer.

Hi,

If it's all about simplifying, then you can use pivot table.

See if the approach like in the attached sheet works. I have just regrouped the years and used pivot table.

Definitely there are better, bigger array formulas can be used. But see if this helps.

Elayaz: many thanks for your effort. What you have on the second sheet is actually what I am looking to get in the end. However because the actual file is so extensive, I cannot manually combine the year ranges for every car. That is what I would like Excel to do for me.

The underlying idea is that we want to use the end product as a kind of address list to make advertisement texts with in Word, using the excel sheet to generate the titles per car model. So in order to make that work I need to end up with a file that has 1 car model / engine combination per row with the range of years that car was made.

So the pivot table is not what I am looking for because it cannot be read by other programms like Word, because it uses filters etc.

Got it.

Here to regroup the years, i have used a simple if formula in sheet "Blad1", A1

=IF(OR(B2=1990,B2<1993),"1990-1992","")

Post how you want to regroup the years, we can modify the formula and get a list per the need. We can proceed from there.

OK let me just add an excerpt from the actual file to make it more clear. The formula you mentioned would still require some kind of manual input of the years because in the example it only had 1990, 1991 and 1992 in it. But in reality some cars are only made in the 80's while other ones are from the new millenium etc. So with this formula I would need to look up per car the lowest and highest year it was made in and enter that into a formula. That would still be a huge task.

Attached a piece of the original file I am working with:
car_applications_actuallist.xlsx

Well, there may be big solutions/ vba code to extract the require data. My way to do this would be, extract the unique car model names and check the lowest and highest year it was made and group the years accordingly ( I have used data-->advance filter to get the unique model names... vlookup and index,sumproduct to get the low and high year based on the model names, which i have done in blad2)

Then, i would apply the grouped years to all data per the model name. CONCATENATE all the columns to remove the duplicates and de CONCATENATE to get back the unique records.(which i have done in blad1).

See if this example way of data extract helps. It's not huge task and can be done in few mins

--------
If my reply helped, Please add reputation (click on the star below the post)
If the reply solves your problem, Don't forget to mark the thread as "Solved" (Thread Tools->Mark thread as Solved)

PatrickShow, does this work..?

Still fighting with the list and your solution, but I have a feeling it might work. But it was not so simple as expected, so I pushed it back a bit in my time table