+ Reply to Thread
Results 1 to 2 of 2

Excel macro to "fill in" consecutive numbers in non-consecutive list?

  1. #1
    Registered User
    Join Date
    03-03-2009
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    35

    Excel macro to "fill in" consecutive numbers in non-consecutive list?

    Hi,

    I did a search of these forums, and after looking through 10 pages of results, I was unable to find an answer, so I'm hoping someone can either help me, or point me to the solution...

    I have about 30,000 rows of data, and the first column is numbered NEARLY consecutively.

    1 data data data
    2 data data data
    3 data data data
    4 data data data
    7 data data data
    8 data data data
    9 data data data

    etc...
    (note "5" and "6" are missing, but there may be occurrences of 1, or 3 or more, missing consecutive numbers.)

    Is there any way (via macro or otherwise) to identify the non-consecutive "gaps" then insert a row, then fill the numbered column with the correct "consecutive" number?

    So, the end result would be a perfectly consecutive numbered column, (but with blank data in the newly created rows), like so...

    1 data data data
    2 data data data
    3 data data data
    4 data data data
    5
    6
    7 data data data
    8 data data data
    9 data data data

    Any help would be greatly appreciated!

  2. #2
    Registered User
    Join Date
    03-03-2009
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Excel macro to "fill in" consecutive numbers in non-consecutive list?

    Actually, I received a simple (no macro required), but effective solution over at Yahoo! answers...

    (Excel 2007)
    Generate an auto-numbered list (using click n drag) from 1 to 30,000 in the same column (Column A for this example), but below the existing data.

    Do an ascending sort of the data in Column A, then use the "delete duplicates" function on the data tools ribbon, using Column A as your selection criteria.


    (Excel 2003)
    Process is the same as Excel 2007, but use ASAP Utilities plugin to delete duplicates.

+ 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