+ Reply to Thread
Results 1 to 6 of 6

Repeating a List via functions

  1. #1
    Registered User
    Join Date
    03-25-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Question Repeating a List via functions

    Hi all,

    I have, what I think may be a simple request. I have a list of colours from A1 to A10. This list may change, get longer or get shorter. In B1 I would like this list to repeat over and over again. but changes. I have attached the list in question, and what I would like it to do.

    So to clarify - add a colour to Column A, it will add to the repeated list in Column B.

    LISTA.xlsx

    Regards,

    JM

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Repeating a List via functions

    B1 (or any cell outside column A):
    Please Login or Register  to view this content.
    and copy down as long as you need

    PS. In A only "colours", no header, no sum etc.
    If there is something - correct COUNTA
    Best Regards,

    Kaper

  3. #3
    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: Repeating a List via functions

    Hi,

    One way in B1 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    but can I ask why you are repeating the list? What's the end goal?
    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.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,601

    Re: Repeating a List via functions

    Put this formula in B1:

    =INDEX(A:A,MOD(ROWS($1:1)-1,COUNTA(A:A))+1)

    then copy down.

    Note that your file was set for Manual recalculation - change this to Automatic.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    03-25-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Repeating a List via functions

    Thanks,

    These both worked a charm, I just needed the colours to be inserted into a list which will then have subcategories attached to them (every new instance of the colour would then have a new sub category associated with it).

    Thanks again!

  6. #6
    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: Repeating a List via functions

    Hi,

    OK. Just another thought. You might, depending on your circumstances find that a table of colours, with colours down the rows and subtypes across the columns might be more flexible.

+ 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. Consolidating If statement with repeating functions
    By jeffreybrown in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-21-2012, 05:26 PM
  2. How can I average data from a repeating list into a unique list?
    By Hicoda in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-03-2006, 02:50 PM
  3. Replies: 0
    Last Post: 11-10-2005, 11:20 AM
  4. repeating functions
    By firelake in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-28-2005, 05:05 PM
  5. How: Functions repeating...
    By DanStephens in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-16-2005, 05: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