Results 1 to 6 of 6

Thread: Can you sequence blocks of numbers!??

  1. #1
    Neophyte Andy's Avatar
    Join Date
    Sep 2014
    Location
    North England
    Posts
    4
    Articles
    0

    Can you sequence blocks of numbers!??



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I am using Excel 2011 for Mac.

    I am trying to sequence two columns; one with some set text and a start number; the other with a number.

    I cannot work out how to sequence a block of the SAME number.

    So, I have 6 rows of "Claire and David 002" in column A; in column C is the number "5595" for the same 6 rows

    I want to then make the next 6 rows "Claire and David 003" in column A and column C has 6 rows of "5596"

    Then it would be 6 rows of "Claire and David 004" in column A and column C has 6 rows of "5597"

    and so on.

    I have attached a screen grab. So, I need to sequence the blocks so they appear as the first block of 6 ("Claire and David 001").

    Up until now I can create the first line by simple sequencing and then I copy the first line beneath in both columns and then pull both column A, B & C down to complete the block (of 6 in this case). But I have to do this for every block and when there are 1000 it takes so long!

    I am assuming that a solution would mean that the number of rows can be 'x' and the sequencing can go up by jumps of 'y' if necessary? Sometimes the sequence can jump up in 2s or 3s etc.

    Thanks for any help!

    Andy
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Screen Shot 2014-09-03 at 11.12.40.png 
Views:	7 
Size:	41.1 KB 
ID:	2612  

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    I don't think you can copy down to get those sequencies.

    One thing you can do is use formulas to get the sequences.

    e.g.

    In A2:

    ="Claire & David "&TEXT(MOD(INT((ROW()-ROW($A$2))/6),6)+1,"000")

    copied down as far as needed

    then in B2:

    =5593+MOD(INT((ROW()-ROW($A$2))/6),6)

    copied down same distance.

    You can then copy those 2 columns and paste special >> Values over themselves to eliminate the formulas and get them hard-coded.


  3. #3
    Neophyte Andy's Avatar
    Join Date
    Sep 2014
    Location
    North England
    Posts
    4
    Articles
    0
    That's great. Thanks for your help. I'll try and let you know!

  4. #4
    Neophyte Andy's Avatar
    Join Date
    Sep 2014
    Location
    North England
    Posts
    4
    Articles
    0
    Hi NBVC,

    That worked really well - many, many thanks - but I had to modify the formula.

    The second '6' in the formula seemed to control the amount of blocks, so after 6 it began to repeat the first block. In my actual workflow for this I had 474 blocks so I used ="Claire & David "&TEXT(MOD(INT((ROW()-ROW($A$2))/6),474)+1,"000")

    The B2 block worked fine with a similar modification.

    Can you tell me; if the 5593 value in B2 - =5593+MOD(INT((ROW()-ROW($A$2))/6),6) - jumped from 5593 to 5595 to 5597 (2 each time) etc, how would you modify the formula then?

    Kind regards
    Andy

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =5593+2*MOD(INT((ROW()-ROW($A$2))/6),6)


  6. #6
    Neophyte Andy's Avatar
    Join Date
    Sep 2014
    Location
    North England
    Posts
    4
    Articles
    0
    That's great. Thanks for your help. I'll pass this on.

    Andy

Posting Permissions

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