+ Reply to Thread
Results 1 to 8 of 8

Repeat values based on number count (without macro)

  1. #1
    Registered User
    Join Date
    10-18-2006
    Posts
    49

    Repeat values based on number count (without macro)

    Hi,
    Can somebody guide me how I can go about doing this? I can do it with VBA but I don't want to use macros. Is there a way to do it only with formulas.

    If I fill first few rows of column A with consecutive numbers (say 1,2,3,4), the column B should get populated as 1,1,1,2,2,3.
    If it is 5, then 1,1,1,1,2,2,2,3,3,4

    I couldn't succeed at doing this

    Thanks

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Repeat values based on number count (without macro)

    Maybe someone will figure it out how to make it with array (I can't :-S)

    Until then look here:

    Book1.xls

  3. #3
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Repeat values based on number count (without macro)

    Post a sample workbook with "before" and "after" examples so we can fully understand the logic of the pattern of values.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Repeat values based on number count (without macro)

    If row 1 were blank such that 1 to n is listed in A2 onwards with sequence in B2 onwards then using your examples and avoiding helpers:

    Please Login or Register  to view this content.
    As you amend the 1 to n in A so B listing should update accordingly... note key assumption that we're using 1 to n rather than say 10 to 10+n etc...

    Does that help ?
    Last edited by DonkeyOte; 12-16-2009 at 08:51 AM. Reason: typo

  5. #5
    Registered User
    Join Date
    10-18-2006
    Posts
    49

    Re: Repeat values based on number count (without macro)

    Thanks for the effort guys, though we are getting almost close.
    Don, your solution returned a cyclic reference error. Or is there something I am doing wrong? could you place in an excel file with an example and post it?

    zbor, your solution is close to what i need. Is there a way you can modify to populate the values as given below:
    Attached Files Attached Files

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Repeat values based on number count (without macro)

    I thought my advice was pretty clear...

    -- Row 1 should be blank.

    -- Enter 1 to n in cells A2 onwards

    -- Enter the formula provided into B2 onwards

    in the sample I looked at the formula had been entered into B1, also the first value in A2 was not 1 - ie 2 to n rather than to 1 to n and as advised previously the formula I provided would not work in those instances, ie in present form the formula expects 1 to n at all times.

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Repeat values based on number count (without macro)

    Are you numbers by order (1,2,3,4,5...) or not?

    In first case you need to define only one number (i.e. 5). In second case you need to define order (i.e. 3,2,5,1,4)

  8. #8
    Registered User
    Join Date
    10-18-2006
    Posts
    49

    Re: Repeat values based on number count (without macro)

    Don, you're great. It worked fine. Thanks a ton.

+ 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