Regrouping values of multiple cells according to variable data

[DavidVeilleux]

Hi, I am trying to automate a very long process with excel. I work in a motorsport business and I would like to do application charts automatically in excel. Let's say, a bearing fits on a XXXX 4-wheeler from 1996 to 1998, 2000 and 2002 to 2005. I want to have this written this way : 92-98, 00, 02-05. My data is presented as a list:

Model Year
XXXX 1996
XXXX 1997
XXXX 1998
XXXX 2000
XXXX 2002
XXXX 2003
XXXX 2004
XXXX 2005
YYYY 1999
YYYY 2001
YYYY 2002

N.B. There is always more than one model in the list.

Presently I make a pivot table out of the data and I'm trying to have a formula per row

Years
Models 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 Result
XXXX 1 1 1 1 1 1 1 1 96-98, 00, 02-05
YYYY 1 1 1 99, 01-02

My formula I presently use is this one, but it is static as my ranges were manually typed in but they change on every line (highlighted in yellow).

=CONCATENATE(RIGHT(INDIRECT(ADDRESS(1;MATCH("X";2:2;0)));2);"-";RIGHT(INDIRECT(ADDRESS(1;MATCH("Y";AT2:BZ2;0)+MATCH("X";2:2;0)-2));2);", ";RIGHT(INDIRECT(ADDRESS(1;MATCH("X";AX2:BZ2;0)+MATCH("Y";AT2:BZ2;0)+MATCH("X";2:2;0)-2));2);"-";RIGHT(INDIRECT(ADDRESS(1;MATCH("y";BD2:BZ2;0)+MATCH("X";AX2:BZ2;0)+MATCH("Y";AT2:BZ2;0)+MATCH("X";2:2;0)-4));2))

Maybe I'm going at it wrong, so don't hesitate to use a completely different method.