+ Reply to Thread
Results 1 to 5 of 5

Copy value X number of times down column

Hybrid View

  1. 03-24-2014, 01:47 PM #1
    CCollin
    CCollin is offline
    Registered User
    Join Date
    03-20-2014
    Location
    CT, USA
    MS-Off Ver
    Excel 2011
    Posts
    2

    Copy value X number of times down column

    I found the following formula on another thread and it works perfectly until I get to a second listing of the same item.
    =IF(COUNTIF($E$5:E5,E5)=INDEX(B:B,MATCH(E5,A:A,0)),INDEX(A:A,MATCH(E5,A:A,0)+1),E5)

    Example:
    Item # of occurrences
    Sugar Melt 3
    Summer Beauty 1
    Blue Ice 1
    Sugar Melt 1
    Hot Lips 1

    I want it to be listed this way:
    Sugar Melt
    Sugar Melt
    Sugar Melt
    Summer Beauty
    Blue Ice
    Sugar Melt
    Hot Lips

    But when it gets the the second Sugar Melt the formula will only return Sugar Melt. Is there a way to fix this?
    This is part of a larger workbook so the Items are extracted from another sheet and will not be combined or sorted in this list.

    I have attached a workbook with more detail.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-24-2014, 01:57 PM #2
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Copy value X number of times down column

    Try this formula

    =IFERROR(INDEX($A$2:$A$15,MATCH(TRUE,MMULT(--(ROW($A$2:$A$15)>=TRANSPOSE(ROW($A$2:$A$15))),$B$2:$B$15)>=ROWS($1:1),0)),"")


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  3. 03-24-2014, 02:11 PM #3
    CCollin
    CCollin is offline
    Registered User
    Join Date
    03-20-2014
    Location
    CT, USA
    MS-Off Ver
    Excel 2011
    Posts
    2

    Thumbs up Re: Copy value X number of times down column

    Thanks! This works exactly how I need it to.
    Reply With QuoteRegister To Reply
  4. 03-24-2014, 02:20 PM #4
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Copy value X number of times down column

    You're welcome and thank you for the feedback!

    Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
    Reply With QuoteRegister To Reply
  5. 03-24-2014, 03:01 PM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Copy value X number of times down column

    Here's another one.

    This array formula** entered in E2:

    =IFERROR(LOOKUP(ROWS(E$2:E2)-1,SUBTOTAL(9,OFFSET(B$1,0,0,ROW(INDIRECT("1:"&COUNT(B:B))))),A$2:A$15),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.

    http://www.excelforum.com/showthread.php?t=926616
    Last edited by Tony Valko; 03-24-2014 at 03:03 PM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy set number of cells a set number of times within same worksheet
    By hal9000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-09-2013, 11:51 AM
  2. Copy and Paste data in column A number of times displayed in column B
    By anthony232 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-15-2013, 05:13 PM
  3. Copy column variable number of times between start date and end date
    By gbisk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2013, 07:57 AM
  4. [SOLVED] count the number of times each string appears in a column and make a summary column
    By intothewild in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-04-2012, 08:02 AM
  5. [SOLVED] find number of times a letter or a number appears in a column
    By dcoates in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-17-2012, 02:47 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1