Combine cells when column match

[Cristianst]

Hi Everyone,

I don't know how to explain it well, so I'll show you a table of what I need to do.

I have this:

Year	Model	Item Number
1984	A	100
1985	A	100
1986	A	100
1987	B	100
1988	B	100
1982	B	100
1983	B	100
1984	B	100
1985	B	100
1986	B	100
1987	B	100
1984	B	100
1985	B	100
1986	B	100
1987	B	100
1988	B	100
1989	B	100
1986	C	102
1987	C	102
1988	C	102
1990	D	102
1991	D	102
1992	D	102
1990	D	102
1991	D	102
1992	D	102
1984	E	102
1985	E	102
1986	E	102
1984	E	102
1985	E	102
1986	E	102
1986	E	102
1987	E	102
1988	E	102
1989	E	102

And I need it to be like this:

Item Number	Model
100	A 1984-1986, B 1987-1989
102	C 1986-1988, D 1990-1992, E 1984-1989

I have looked up for options with INDEX, MATCH but I don't understand it too well and can't get it to work. I'd be very grateful if anyone is able to show me how its done.

Thank you.

[#2]

A pivot table comes close, though it doesn allow the same layout as you showed, see attached.

[#3]

Thank you for your reply, I need it in the other format so it can be read by another program, I'll keep trying with pivot tables in mind though.

[#4]

If the Excel version you use has TEXTJOIN Function, you may use @Jan Karel Pieterse's example to join the text that you want.

I am using Excel 2013 that does not have TEXTJOIN function and I am not familiar with PivotTable.  If you needs the formula solution, please try the attached file.  However, I created some helper columns and NAME formula.

I am not sure if there are any Power Query solutions.

[#5]

It could be done with Power Query

In brief:

- query source table and merge Item Number and Model columns with separator which is definitely not in names of both;

- reference that query and group new column selecting min Year as output;

- same for max Year;

- merge above two queries and split merged Item Number & Model column;

- create new column as Model minYear-MaxYear;

- group on Item Number without aggregation of other columns;

- from resulting tables in each grouped row extract first column of such table as list;

- expand list as values with ", " separator.

Rest is cosmetic, please see attached

[#6]

Thank you very much for your replies, I'm going to try and replicate them on the full table and let you know.