Search code examples
excelvbapowerpivot

Excel Convert Make/Model/Year Range into Individual Year/Make/Model rows

I'm attempting to systematically expand a table of Make/Model/Year Range combinations into individual year rows. I've tried looking for solutions but none seem to match my use-case and I don't know the first thing about VBA so need a little hand-holding here.

I'm using Excel Office 365, I've tried figuring something out with PowerPivot and merging queries but none will accomplish what I need.

Here is the data I am working from (Columns A:D)

Make    Model      From    To
ACURA   MDX       2001     2009
ACURA   RDX       2008     2009
ACURA   SLX       1996     1998
ACURA   TL        1999     2008
ACURA   TSX       2008     2009

The desired output would be:

Year    Make    Model
2001    ACURA   MDX
2002    ACURA   MDX
2003    ACURA   MDX
2004    ACURA   MDX
2005    ACURA   MDX
2006    ACURA   MDX
2007    ACURA   MDX
2008    ACURA   MDX
2009    ACURA   MDX
2008    ACURA   RDX
2009    ACURA   RDX
1996    ACURA   SLX
1997    ACURA   SLX
1998    ACURA   SLX

...And so on.

Solution

  • Using Microsoft Query and a source Excel file with two named ranges MAKES and YEARS (second one is a single-column of years spanning the desired range)

    Query:

    SELECT MAKES.Make, MAKES.Model, YEARS.Year
FROM `C:\Users\userName\Desktop\Test.xlsx`.MAKES MAKES, 
     `C:\Users\userName\Desktop\Test.xlsx`.YEARS YEARS
WHERE MAKES.`From` <= YEARS.Year AND MAKES.To >= YEARS.Year