+ Reply to Thread
Results 1 to 5 of 5

Generating a items in a list n times

  1. #1
    Registered User
    Join Date
    09-09-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Generating a items in a list n times

    Hello all, this may be a simple question but any help is much appreciated!

    I'm trying to generate a sequence of items from column(A) a number of times that is given in column(B) and then output in column(C). This might be easier to explain as follows:

    Column A
    Apple
    Pear
    Mango

    Column B
    2
    4
    3

    Column C
    Apple
    Apple
    Pear
    Pear
    Pear
    Pear
    Mango
    Mango
    Mango

    Thanks in advance!

    J

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Generating a items in a list n times

    Hi,

    There'll be an easy VBA method but if you want a standard Excel solution one way would be as follows.

    In C1 enter
    =REPT(A1&"|",B1)

    In C2 enter
    =C1&REPT(A2&"|",B2)
    and copy this down the whole column C list.

    Then pick the last item, copy it and paste it back somewhere with Paste Special values.
    You'll now have a list in a single cell,
    Use the Text to Columns functionality to split this into columns using the "|" pipe separator as the delimiter.

    Finally select all the values across the columns and use Copy, Paste Special Transpose to paste this horizontal list to a vertical list.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Generating a items in a list n times

    To do it with formula (probably not the easiest way) without cutting and pasting:

    In cell C1 enter 0
    In cell C2 put the formula =IF(ROW()>SUM(B:B),"",IF(ROW()>SUM(INDIRECT("B1:" & CELL("Address",OFFSET($B$1,C1,0)))),C1+1,C1)) and copy down
    In cell D1 put the formula =IF(C1<>"",OFFSET($A$1,C1,0),"") and copy down

    You can now hide column C, or move it to a different column way off to the right, or whatever works best for you.

  4. #4
    Registered User
    Join Date
    09-09-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Generating a items in a list n times

    Thanks for your help guys!!!

    Much appreciated :-)

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Generating a items in a list n times

    ... or the aforementioned VBA method..

    Please Login or Register  to view this content.

+ 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