+ Reply to Thread
Results 1 to 5 of 5

How to extend sequential id pattern

  1. #1
    Registered User
    Join Date
    07-25-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2013
    Posts
    2

    How to extend sequential id pattern

    Hey everybody!
    I'm new to this forum

    Here is my problem:
    I have a spreadsheet containing 3 columns and almost 24,000 rows of data.
    Let's say: Column A is "First Name", Column B is "Last Name" and Column C is "Tracking ID".

    The first 6,700 entries in Column C each cell already has its own unique, sequential "tracking" id which was entered manually by someone else.
    I'm really hoping that there is a way for me to use the already established pattern to populate the remaining 17,000 cells.

    I tried to highlight and extending but this did not work.
    I imagine that it's probably because the pattern is a bit complex?
    It is: "3digits"_"a letter [A-D]""a number [1-8]" starting with 001_a1.

    Column C
    001_a1
    ...
    001_a8
    001_b1
    ...
    001_b8
    001_c1
    ...
    001_c8
    001_d1
    ...
    001_d8
    002_a1
    ...
    002_a8
    002_b1
    ... and it continues this way to the last filled cell:
    215_c4

    I need a way to continue this pattern for the remaining approx. 17,000 cells.

    ANY help/ideas/shortcuts/ANYTHING will be greatly appreciated; I can't even think about how long it will take to manually enter.

    Thanks!

    EDIT: I've attached a sample file. Column C contains the first 321 actual tracking numbers.
    Attached Files Attached Files
    Last edited by guannarue; 07-25-2013 at 02:16 PM. Reason: Added sample file

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to extend sequential id pattern

    See the link below, for some tips to get an better result on your question.

    http://www.excelforum.com/the-water-...-question.html
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    293

    Re: How to extend sequential id pattern

    Here's a macro solution:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-25-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: How to extend sequential id pattern

    I appreciate your help but, I don't understand what I'm supposed to do with the macro as I've never even used one before. could you explain please?
    Thanks

    Quote Originally Posted by gjcase View Post
    Here's a macro solution:

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    293

    Re: How to extend sequential id pattern

    Okay. Sorry it took me a while to get back here.

    The first thing needed is to ensure the Developer tab is shown. By default, this is hidden. Use the following link for instructions on revealing the tab:
    http://msdn.microsoft.com/en-us/library/bb608625.aspx
    or just go to File/Options/Customize Ribbon and ensure the Developer box in the right window is checked. Then hit Okay.
    The other thing you should do is to adjust your security settings to allow macros to run. See the following link:
    http://www.addictivetips.com/windows...in-excel-2010/
    or go to File/Options/Trust Center/Trust Center Settings/Macro Settings.
    Ensure that your settings are set to either Disable all with notification or Enable all macros (not recommended). The former will allow macros to run only after a notification is acknowledged. The latter allows any macros to run without notification. If you have any significant exposure to files from outside, then the more prudent choice is the former. This is a tradeoff between security and convenience. (It's possible for someone to give you a file with an autorun macro which could harm your computer; hence the warning message.)

    Next you need a Module to contain the code. I usually do this by just recording a quick macro. From the Developer Tab, Select Record Macro. You can accept the suggested name (most likely Macro1) and leave the optional parameters as listed. Hit OK to record the macro. On your spreadsheet, select a cell, and then another. From the Developer tab, select Stop Recording, and then select Macros, and Edit. The VBA editor will open. One of the windows in the Editor will have the Macro you recorded. It should say something like

    Please Login or Register  to view this content.
    The actual macro verbiage may vary a bit.

    Below the Macro1 macro (under the End Sub statement) paste the code from the earlier post (the SeqNo macro code). Replace the range C2:C25001 with your range if it's not starting in C2.

    Now you can run the macro. To do that, from the developer tab, select Macros, and select the macro called SeqNo and Run. If you get a caution note, tell it OK. That's it. Hope it works.
    Last edited by gjcase; 08-05-2013 at 04:05 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] SOLVED - Need help with a non-sequential number pattern
    By desertdweller in forum Excel - New Users/Basics
    Replies: 17
    Last Post: 10-15-2012, 12:03 PM
  2. [SOLVED] Create sequential letters (A, B, C) in sequential cells
    By Theale in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 08-29-2012, 02:47 PM
  3. Repeating sequential pattern
    By Bdown in forum Excel General
    Replies: 3
    Last Post: 06-07-2012, 05:13 PM
  4. Pull a sequential pattern from another sheet multiple times
    By Affy in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-27-2012, 10:20 PM
  5. [SOLVED] Non-sequential VLOOKUP function -OR- sequential sort of web query
    By Eric S in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-28-2005, 04:06 PM

Tags for this Thread

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