+ Reply to Thread
Results 1 to 6 of 6

Replicate a cell based on a total in another cell

  1. #1
    Registered User
    Join Date
    06-03-2011
    Location
    Hatfield, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    Question Replicate a cell based on a total in another cell

    Hi all,

    I hope this is the right section!

    I have come across an issue that I'm sure must have a simple answer, but can't seem to figure it out.
    I have a list of points in column A, and matching totals in column B.
    I need one column with duplicates of the entries in column A based on the corresponding total in column B.

    Anyone know a formula or other method to achieve this?

    I have attached an example xlsx, sheet 1 is an example of that data as it is now, sheet 2, as it is required.

    Many thanks in advance!
    Attached Files Attached Files
    Last edited by adx1000; 05-21-2013 at 10:44 AM. Reason: Solved

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

    Re: Replicate a cell based on a total in another cell

    Put zero in D1 of Sheet1, then put this formula in D2:

    =SUM(B$1:B2)

    and copy down to D6.

    Then put this formula in F2:

    =IF(ROWS($1:1)>MAX(D:D),"",INDEX(A:A,MATCH(ROWS($1:1)-1,D:D)+1))

    then copy down as far as you need to. If you want it in a different sheet, then you can put this in A2 of Sheet3:

    =IF(ROWS($1:1)>MAX(Sheet1!D:D),"",INDEX(Sheet1!A:A,MATCH(ROWS($1:1)-1,Sheet1!D:D)+1))

    Hope this helps.

    Pete

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Replicate a cell based on a total in another cell

    try a pivot table
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    06-03-2011
    Location
    Hatfield, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Replicate a cell based on a total in another cell

    Quote Originally Posted by Pete_UK View Post
    Put zero in D1 of Sheet1, then put this formula in D2:

    =SUM(B$1:B2)

    and copy down to D6.

    Then put this formula in F2:

    =IF(ROWS($1:1)>MAX(D:D),"",INDEX(A:A,MATCH(ROWS($1:1)-1,D:D)+1))

    then copy down as far as you need to. If you want it in a different sheet, then you can put this in A2 of Sheet3:

    =IF(ROWS($1:1)>MAX(Sheet1!D:D),"",INDEX(Sheet1!A:A,MATCH(ROWS($1:1)-1,Sheet1!D:D)+1))

    Hope this helps.

    Pete
    Thanks Pete, this seems to work perfectly, not really sure how, I shall have to investigate when I have a chance!

    I will give it a go with the real data and report back. Thanks again!

    EDIT: Just tried it with the real dataset, seems to have worked perfectly. I assume the D column is calculating a total, so if it says 434 for example, copying the formula in F2 down to 435 should give me the lot, correct? oh, and thanks again!
    Last edited by adx1000; 05-21-2013 at 06:52 AM.

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Replicate a cell based on a total in another cell

    This single array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) will work as well. In cell C2 of your sheet1 and copied down for as many cells as equals the sum of your column B:

    =INDEX($A$2:$A$7,MATCH(TRUE,ROWS($A$2:$A2)<=MMULT(--(ROW(INDIRECT("1:"&COUNTA($A$2:$A$7)))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA($A$2:$A$7))))),$B$2:$B$7),0))

    Change the end row reference in red as and when required.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

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

    Re: Replicate a cell based on a total in another cell

    Quote Originally Posted by adx1000 View Post
    EDIT: Just tried it with the real dataset, seems to have worked perfectly. I assume the D column is calculating a total, so if it says 434 for example, copying the formula in F2 down to 435 should give me the lot, correct? oh, and thanks again!
    Yes, that is correct, although it doesn't matter if you copy the formula beyond that, so you could copy to row 500 or even row 1000 to make sure you have picked up all the records.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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