We help IT Professionals succeed at work.

Expanding and filling year gaps in multiple listings

185 Views
Last Modified: 2017-03-21
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
Comment
Watch Question

Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
And where is the data in it's native form currently? Excel, SQL Server, Access?
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
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.
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Joe ReichsfeldFounder, Owner, Business Consultant

Author

Commented:
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 ReichsfeldFounder, Owner, Business Consultant

Author

Commented:
Thank you very much, you took what could have been a very time consuming chore and made it a simple task!
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.