Combining Data... Multiple Rows and Columns.

[MattGold]

I work with a handful of automotive parts manufactures.

We take their data and turn it into our own format.

Some manufacturers provide data in a year-by-year format, however I want to "roll-up" this data into a range of years, and combine auxiliary information.

Ex:
Sample Date

Group | Part# | Year | Make | Model | Liters | Cylinder

57i Entry Level Kit | 57-0521-1 | 2002 | RENAULT | Megane | 1.6L | L4
57i Entry Level Kit | 57-0521-1 | 2002 | RENAULT | Megane | 2.0L | L4
57i Entry Level Kit | 57-0521-1 | 2003 | RENAULT | Megane | 1.6L | L4
57i Entry Level Kit | 57-0521-1 | 2003 | RENAULT | Megane | 2.0L | L4

I would want the end result to be:

Group | Part# | Year | Make | Model | Liters | Cylinder

57i Entry Level Kit | 57-0521-1 | 2002-2003 | RENAULT | Megane | 1.6L, 2.0L | L4


The Part # is really the Rosetta Stone in this, anything that has the same Part #, Make and Model should have all the other data 'rolled up' into specific fields.

I really don't know where to starts as the files I'm working with are 20K+ Lines of data.


TIA.

-M

[Bjornar]

I think I have a solution for this, but I would like to have some more sample data, to check the solution.
Could you maybe put some more lines of real life data into an excel file and attach it to your reply ?

[Zonk~]

Hi Matt,
I've attached a detailed solution for you. Please let me know if you have questions about how anything works. The solution steps are laid out in the spreadsheet.

-B

[MattGold]

Zonk... you seem to be onto something... I suppose I broke down the actual task a tad more then I should have.

Attached is the actual sample data.

Thanks so much,


-M