Convert Range of Cells into One Cell

Question

I am working on a database of car part numbers in Excel 2010. I want to simplify the list from this: (ex.)

Part Number|Year|Make|Model|Sub-model|Engine

  12345|1990|Ford|Focus|   blah  |blah
  12345|1991|Ford|Focus|   blah  |blah
  12345|1992|Ford|Focus|   blah  |blah
  54321|2001|Ford|Focus|   blah  |blah
  54321|2002|Ford|Focus|   blah  |blah
  54321|2003|Ford|Focus|   blah  |blah

to this:

Part Number|Year|Make|Model|Sub-model|Engine

  12345|1990-1992|Ford|Focus|   blah  |blah
  54321|2001-2003|Ford|Focus|   blah  |blah

Besides manually that is. The reason I need this is I'm dealing with like 200,000+ entries for one brand of parts. I'm wondering is there an easy way to do this? I already used kutools for excel to split it into separate workbooks based on make and model if that helps. Thanks for any help.

Answer 1

A somewhat lengthy formula can actually solve this. See my image below:

The best part about this is that it's kind of a search button as well. You just have to change the Part Number, Make, Model, Sub-model, or Engine and it will find the minimum and maximum year that fit your conditions.

The formula for cell I2 in my image is:

=CONCATENATE(MIN(IF(($A$2:$A$7=H2)*($C$2:$C$7=J2)*($D$2:$D$7=K2)*($E$2:$E$7=L2)*($F$2:$F$7=M2),$B$2:$B$7))," - ",MAX(IF(($A$2:$A$7=H2)*($C$2:$C$7=J2)*($D$2:$D$7=K2)*($E$2:$E$7=L2)*($F$2:$F$7=M2),$B$2:$B$7)))

This is an array formula so use Ctrl-Shift-Enter when confirming it. Modify the ranges accordingly.

The only downside here is that your sheet can take a massive calculation hit. This can only work so well after a few thousand rows depending on your machine.

Let me know if this works. I'll still work on a VBA one.

EDIT:

Only Part Number as condition:

=CONCATENATE(MIN(IF(($A$2:$A$7=H2),$B$2:$B$7))," - ",MAX(IF(($A$2:$A$7=H2),$B$2:$B$7)))

Hope this helps.