+ Reply to Thread
Results 1 to 15 of 15

Complicated series, for me anyway.

  1. #1
    Registered User
    Join Date
    03-25-2014
    Location
    Alabama
    MS-Off Ver
    Excel MAC 2011
    Posts
    39

    Complicated series, for me anyway.

    Hi everyone,

    I am tasked with creating a database consisting of a sequential number with a letter/number prefix from which another application will pull the information. The sequential number will also be duplicated once in the field below it. The number, (or how many) in the series is unknown at the moment. Do any of you think you can help me with this?
    I'm using Excel for Mac 2011

    Here's an example. (complete with prefix)

    23PAL0059001
    23PAL0059001
    23PAL0059002
    23PAL0059002
    23PAL0059003
    23PAL0059003
    23PAL0059004
    23PAL0059004
    23PAL0059005
    23PAL0059005

    and so on

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Complicated series, for me anyway.

    Is that the data you have to duplicate?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    03-25-2014
    Location
    Alabama
    MS-Off Ver
    Excel MAC 2011
    Posts
    39

    Re: Complicated series, for me anyway.

    Yes, I need two of each sequential number in a column with the 23PAL prefix and the two leading zeros. Here's what I'm trying to do. Generally I do it with just numbers using the automated series function.

    example.jpg

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Complicated series, for me anyway.

    So the data you posted would become this?

    23PAL0059001
    23PAL0059001
    23PAL0059001
    23PAL0059001
    23PAL0059002
    23PAL0059002
    23PAL0059002
    23PAL0059002
    23PAL0059003
    23PAL0059003
    23PAL0059003
    23PAL0059003
    23PAL0059004
    23PAL0059004
    23PAL0059004
    23PAL0059004
    23PAL0059005
    23PAL0059005
    23PAL0059005
    23PAL0059005

  5. #5
    Registered User
    Join Date
    03-25-2014
    Location
    Alabama
    MS-Off Ver
    Excel MAC 2011
    Posts
    39

    Re: Complicated series, for me anyway.

    Oh... no. the sample I posted initially is what I want the finished database to be. I need two of each sequential number. The 2 real issue I have is I don't know how to produce the duplicated number and I don't know how to make it have that prefix.

    This is what I need for a finished product

    23PAL0059001
    23PAL0059001
    23PAL0059002
    23PAL0059002
    23PAL0059003
    23PAL0059003
    23PAL0059004
    23PAL0059004
    23PAL0059005
    23PAL0059005

  6. #6
    Registered User
    Join Date
    03-25-2014
    Location
    Alabama
    MS-Off Ver
    Excel MAC 2011
    Posts
    39

    Re: Complicated series, for me anyway.

    There will be THOUSANDS of these numbers in the finished database and typing them in isn't an option. So I'm basically looking for a way to automate it by telling Excel how many pairs of numbers I need.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Complicated series, for me anyway.

    Can you upload an example workbook with a before/after?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

  8. #8
    Registered User
    Join Date
    03-25-2014
    Location
    Alabama
    MS-Off Ver
    Excel MAC 2011
    Posts
    39

    Re: Complicated series, for me anyway.

    Ahh, I see the confusion.

    There is no before! What I am posting as an example IS the after.

    Someone just tells me, I need 23PAL0059001 through 23PAL0075000 (or whatever, this is just an example)

  9. #9
    Registered User
    Join Date
    03-25-2014
    Location
    Alabama
    MS-Off Ver
    Excel MAC 2011
    Posts
    39

    Re: Complicated series, for me anyway.

    I just noticed that other screenshot I posted sucks rocks. This is what I need as a finished database.... only there might be 30,000 of them (or more) in the series.

    example2-01.jpg

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Complicated series, for me anyway.

    Isn't having 23PAL0059001 through 23PAL0075000 kind of a before/start/something to actually work with?

    Here's a brute force method based on those 2 values, which are hard-coded.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-25-2014
    Location
    Alabama
    MS-Off Ver
    Excel MAC 2011
    Posts
    39

    Re: Complicated series, for me anyway.

    Well, I am creating a database such as 23PAL0059001 through 23PAL0075000 and then Adobe Illustrator is grabbing those sequential numbers to print them on a label. I have to have two labels with each number on them. As I said this is the first time I've been asked to do this with alpha numeric. Usually it is simply a sequential series of just numbers xxx through xxx.

    I thank you very much for fighting through my ignorance and trying to help me. I'm unsure how/where to use the code you posted so if you could give me some insite on that as well I would really appreciate it.

  12. #12
    Registered User
    Join Date
    03-25-2014
    Location
    Alabama
    MS-Off Ver
    Excel MAC 2011
    Posts
    39

    Re: Complicated series, for me anyway.

    I suppose I figured it out, I added it as a macro and it worked like a charm! Thank you so much Norie!

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Complicated series, for me anyway.

    Glad you got it working.

    Sorry for not getting back to you but I'm unfamiliar with working with macros/code on a Mac so wasn't sure what you would have to do.

  14. #14
    Registered User
    Join Date
    03-25-2014
    Location
    Alabama
    MS-Off Ver
    Excel MAC 2011
    Posts
    39

    Re: Complicated series, for me anyway.

    Ive been using the Macro that Norie helped me with flawlessly for over 2 years. Now I'm stuck...

    The numbers have now reached over 100,000 and one of the leading zeros needs to be replaced.
    My sequence will be 23PAL0094000 through 23PAL0101999

    As with my previous posts the alpha numeric sequence is "23PAL00" followed by a sequential number however when I get to 100,000 it would need to change to "23PAL0" followed by the number.

    It will need to look like this at the change to 100,000:
    23PAL0099998
    23PAL0099998
    23PAL0099999
    23PAL0099999
    23PAL0100000
    23PAL0100000
    23PAL0100001
    23PAL0100001

    Here's the macro Norie wrote:
    Please Login or Register  to view this content.
    I certainly don't have the knowledge to modify this code as I've been trying unsuccessfully for an hour or so now.

    Can someone help me please?

  15. #15
    Registered User
    Join Date
    03-25-2014
    Location
    Alabama
    MS-Off Ver
    Excel MAC 2011
    Posts
    39

    Re: Complicated series, for me anyway.

    Edit: I have no idea why it double posted this!

    Ive been using the Macro that Norie helped me with flawlessly for over 2 years. Now I'm stuck...

    The numbers have now reached over 100,000 and one of the leading zeros needs to be replaced.
    My sequence will be 23PAL0094000 through 23PAL0101999

    As with my previous posts the alpha numeric sequence is "23PAL00" followed by a sequential number however when I get to 100,000 it would need to change to "23PAL0" followed by the number.

    It will need to look like this at the change to 100,000:
    23PAL0099998
    23PAL0099998
    23PAL0099999
    23PAL0099999
    23PAL0100000
    23PAL0100000
    23PAL0100001
    23PAL0100001

    Here's the macro Norie wrote:
    Please Login or Register  to view this content.
    I certainly don't have the knowledge to modify this code as I've been trying unsuccessfully for an hour or so now.

    Can someone help me please?
    Last edited by Cbutts; 11-11-2016 at 11:54 AM.

+ 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. Replies: 2
    Last Post: 11-11-2013, 05:37 PM
  2. Replies: 2
    Last Post: 12-13-2010, 01:39 PM
  3. [SOLVED] chart data series -- plot a table as a single series
    By hjc in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 09-20-2005, 01:05 PM
  4. [SOLVED] series graph -- one series being added to another series
    By rich zielinski via OfficeKB.com in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-30-2005, 02:06 PM
  5. [SOLVED] Filling in a Date Series using the Fill | Series menu command
    By Bob C in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-01-2005, 08:06 PM

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