asked on

Expanding and filling year gaps in multiple listings

Hello,

I have a great deal of data in regards to automotive part fitment. each row contains a minimum year column and a maximum year column like this
min yr max yr make model sub engine
2001 2005 ford f150 crewcab v8

I need to convert it to
yr make model sub eng
2001 ford f150 crewcab v8
2002 ford f150 crewcab v8
2003 ford f150 crewcab v8
2004 ford f150 crewcab v8
2005 ford f150 crewcab v8
next vehicle would start here

I have 50k rows like this and am looking for a productive way to achieve this. I know I could separate out the rows, select blanks, populate downward from the cell above plus one but have no idea how to state this as a macro and make it not extremely manual and time consuming. I have attached a before and after sample.

All assistance is much appreciated.
ymmtest.xlsx

Nitin Sontakke

And where is the data in it's native form currently? Excel, SQL Server, Access?

Nitin Sontakke

Apologies first...I am giving solution in SQL Server script which may not be applicable. But then again, if you have access to it, very easy to import data from Excel and use this with minor modifications:

declare @table_before table
(
  [year_min] integer
  ,[year_max] integer
  ,[make] [varchar](50)
  ,[model] [varchar](50)
  ,[submodel] [varchar](50)
  ,[engtype] [varchar](50)
)

insert into @table_before values
(2001, 2004, 'Dodge', 'Neon', 'R/T', 'L4')
,(2001, 2005, 'Dodge', 'Neon', 'SE', 'L4')
,(2002, 2008, 'Mini', 'Cooper', 'S', 'L4')
,(1988, 1989, 'Honda', 'Prelude', '2.0 Si', 'L4')
,(2000, 2000, 'Toyota', 'Tacoma', 'SR5', 'V6')
,(2001, 2004, 'Toyota', 'Tacoma', 'S-Runner', 'V6')
,(1999, 2002, 'Toyota', '4Runner', 'SR5', 'V6')
,(2005, 2010, 'Nissan', 'Frontier', 'LE', 'V6')

declare @year_min [integer]

select @year_min = min([year_min]) from @table_before

declare @year_max [integer]

select @year_max = max([year_max]) from @table_before

declare @table_years table
(
  [the_year] integer
)

declare @year_index [integer] = @year_min

while @year_index < @year_max + 1
  begin
    insert into @table_years values (@year_index)
    select @year_index += 1;
  end

--select * from @table_years

select y.the_year, b.make, b.model, b.submodel, b.engtype
from @table_before b
left outer join @table_years y on y.the_year between b.year_min and b.year_max

Open in new window

Already tested at my end and works as expected.

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joe Reichsfeld

ASKER

I thank you both for these are great responses depending on the circumstances. I would use either depending on the situation. Today, the macro fit the bill as this is for a client and their person using it has no sql experience. The Vb macro worked like a champ except for one simple issue. Any blank cells cause it to stop. Solved easily by filling all blank cells with ~~ before running the macro and then deleting them all after running it. If only all of lifes issues were so complex! Thanks for your solutions, Nitin and Shums

Joe Reichsfeld

ASKER

Thank you very much, you took what could have been a very time consuming chore and made it a simple task!

Shums Faruk

You're Welcome Joe! Glad I was able to help:)