+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : Expanding Years in Spreadsheet

  1. #1
    Registered User
    Join Date
    04-18-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Expanding Years in Spreadsheet

    Hello all,
    I have attached a list of car parts. The first two columns are the years the part is available in, for example cell A1 is 1995, and A2 is 1998. I need to create blank rows beneath each entry to accomodate each year indivudually. I am having to manually insert, in this example, 3 blank rows and autofill down (I need the whole row to autofill down). When you see the excel file you will see I have almost 18,000 rows that need to be done by end of day Friday (April 20).

    Is there any equation or something that I could use to do this. The problem is that all the year avalabilities are not the same. Some parts are only for one year, and some 2, 5, 4... etc. in no particular order. Is this even possible?

    WAC spreadsheet years.xlsx

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Expanding Years in Spreadsheet

    Hi Felkernl,

    Welcome to the forum.

    What I have understood, after seeing your example, that you need to insert 3 rows after every row where all the data would be filled down from above (original row) and Years in first row would be increased by one per row .. Please let me know if I am correct in my understanding. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Expanding Years in Spreadsheet

    why do you need to add extra rows? would it be possible to a, say, 5 blank columns and then use a formula to fill in the years from that? it would keep your list from growing from 18 000 to potentially 90 000, and it would be a relatively simple matter to construct formulae to give you what you need?

    Edit:

    See attached for an example of what I mean, let me know if this will work for you?
    Attached Files Attached Files
    Last edited by FDibbins; 04-18-2012 at 02:28 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-18-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Expanding Years in Spreadsheet

    Thanks for the replies!

    I think I needed to have been more clear. Each product is available for a particular number of years. A1 has the first year it became available and column B1 is the last year available. This is actually going to be a template for uploading these parts to Ebay so I need each year to be its own seperate row and all the contents copied down.

    So in summary I need to insert the number of rows equal to the ending year minus the beginning year, fill series column A between the gaps, and regular fill dows the rest of the columns.

    Here is the original file. The top highlighted cells are an example of what I need the rest to look like. WAC spreadsheet years.xlsx

    Thanks again!

  5. #5
    Registered User
    Join Date
    04-18-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Expanding Years in Spreadsheet

    Ok, I have made some progress on this. I can space out the rows as necessary and fill down the text. All I need now is a macro that can do this: starting at the beginning year, fill series down and stop before the next beginning year and repeat just for column A. Basically fill series between the gaps in the rows.

    Thanks,
    felkernl

  6. #6
    Registered User
    Join Date
    04-18-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Expanding Years in Spreadsheet

    Figured it out

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1