Combine cells when column match

New Contributor

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:

YearModelItem Number
1984A100
1985A100
1986A100
1987B100
1988B100
1982B100
1983B100
1984B100
1985B100
1986B100
1987B100
1984B100
1985B100
1986B100
1987B100
1988B100
1989B100
1986102
1987C102
1988C102
1990D102
1991D102
1992D102
1990D102
1991D102
1992D102
1984E102
1985E102
1986E102
1984E102
1985E102
1986E102
1986E102
1987E102
1988E102
1989E102

 

And I need it to be like this:

Item NumberModel
100A 1984-1986, B 1987-1989
102C 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.

5 Replies

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

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.

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.

It could be done with Power Query

image.png

 

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

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