+ Reply to Thread
Results 1 to 19 of 19

Auto Multiply Rows

  1. #1
    Registered User
    Join Date
    12-12-2014
    Location
    Ankara
    MS-Off Ver
    2010
    Posts
    41

    Unhappy Auto Multiply Rows

    Hi all,
    attached xlsx is sample of my schools list
    I wanna multiply (or copy or dublicate) every school 8 each times
    I have more than thousands schools and lots of excels every time.
    Is it possible to do this automatically.

    I will be very grateful to the person who will help me in this work

    best regards

    ps: here is i am doing this job manually video
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Auto Multiply Rows

    this would be one way...
    =INDEX(A$2:A$1000000,ROUNDUP(ROW(A1)/8,0))
    You'd have to put it in F2, drag down as far as you want and drag right through col J.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    12-12-2014
    Location
    Ankara
    MS-Off Ver
    2010
    Posts
    41

    Re: Auto Multiply Rows

    Hi Sam
    Thanks for ur reply and solution
    I wanna do this in an other Sheet
    How can i?

    regards

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Auto Multiply Rows

    click the plus sign beside Sheet1 tab to create another tab, then put this in sheet2 cell A2, drag right to cell E2 then drag down as far as you want...
    =INDEX(Sheet1!A$2:A$1000000,ROUNDUP(ROW(A1)/8,0))

  5. #5
    Registered User
    Join Date
    12-12-2014
    Location
    Ankara
    MS-Off Ver
    2010
    Posts
    41

    Re: Auto Multiply Rows

    Worked so good
    Thanks so much Sam
    I appreciate that

    regards

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Auto Multiply Rows

    You're welcome, glad I could help you.

  7. #7
    Registered User
    Join Date
    12-12-2014
    Location
    Ankara
    MS-Off Ver
    2010
    Posts
    41

    Re: Auto Multiply Rows

    Ahhh Sam
    Have a small problem,
    If a cell is empty, your formula puts 0 (zero)
    Is it possible if empty don't put zero, leave it empty
    i am dealing thousands and millons of cells, can't follow zeros

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Auto Multiply Rows

    yes, there are two ways to handle that. One is to use conditional formatting (CF) to change the font color of any zeros to the same background color of the fill, makes them essentially disappear. Click on CF >> new rule >> format only cells that contain >> cell value, equal to, 0, click on format and select the same font color (white usually) as the cell background color and they'll "disappear".

    OR the other is to change the formula to this...
    =IF(INDEX(Sheet1!A$2:A$1000000,ROUNDUP(ROW(A1)/8,0))=0,"",INDEX(Sheet1!A$2:A$1000000,ROUNDUP(ROW(A1)/8,0)))
    it will leave blanks when it runs out of data to copy.

  9. #9
    Registered User
    Join Date
    12-12-2014
    Location
    Ankara
    MS-Off Ver
    2010
    Posts
    41

    Unhappy Re: Auto Multiply Rows

    You are great man Sam
    Second formula worked for me

    Relying on your kindness and helpfulness I want to ask another question Sam, please forgive me
    I cant upload a sample file, there is a problem in excelforum
    Think that ColumA have number "1" (from A1 to A640 all cells are "1")
    i wanna do this;
    the first 8 will be 1,
    the next 8 are 2,
    the next 8 are 3,
    the next 8 are 4,
    ....
    ....
    it will continue while increasingly like this
    i know it is possible for you
    please forgive this newbie

    best regards

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Auto Multiply Rows

    are you saying you want to overwrite what is in column A? So the first 8 will stay as 1 but the next 8 will change to 2 etc and this will overwrite what is in col A already?

  11. #11
    Registered User
    Join Date
    12-12-2014
    Location
    Ankara
    MS-Off Ver
    2010
    Posts
    41

    Re: Auto Multiply Rows

    no no no
    forget our sample excel
    create a new axcel and write A1 "1"
    And multiply it to the A640
    Now: ColumA have number "1" (from A1 to A640 all cells are "1")
    do this;
    the first 8 will be 1,
    the next 8 are 2,
    the next 8 are 3,
    the next 8 are 4,
    ....
    ....
    it will continue while increasingly like this

  12. #12
    Registered User
    Join Date
    12-12-2014
    Location
    Ankara
    MS-Off Ver
    2010
    Posts
    41

    Re: Auto Multiply Rows

    Quote Originally Posted by Sam Capricci View Post
    OR the other is to change the formula to this...
    =IF(INDEX(Sheet1!A$2:A$1000000,ROUNDUP(ROW(A1)/8,0))=0,"",INDEX(Sheet1!A$2:A$1000000,ROUNDUP(ROW(A1)/8,0)))
    it will leave blanks when it runs out of data to copy.
    ohhhhh
    It doesn't run when i change 8 to 16
    why didn't it work? it should have worked ????

    (as i said in my first entry: every school 8 each times)
    sometimes school numbers can be 16
    Last edited by c a g a t a y; 10-02-2020 at 10:40 AM.

  13. #13
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Auto Multiply Rows

    Now: ColumA have number "1" (from A1 to A640 all cells are "1")
    do this;
    the first 8 will be 1,
    the next 8 are 2,
    the next 8 are 3,
    the next 8 are 4,
    For post #11 I really don't follow you. If you could upload a sample workbook with what you have AND what you expect that would be helpful.
    it sounds like A1 through A640 will all be 1 but then you talk about the first 8 as 1, the next 8 will be 2, etc. Where would these be located?
    This formula will give you 8 1s, then 8 2s etc anywhere you put it...
    =ROUNDUP(ROWS($A$1:A1)/8,0)
    But if that isn't what you want, then, as I noted, a workbook with what you have AND what you expect would be very helpful.

    As for post #12, I don't know how to get the formula to change midstream from 8 to 16 then back to 8, or from 16 to 8 then back to 16. Now, I think I know how to adjust the formula but that would require a helper column as a reference. You'd have to put the number in another cell beside the institution that tells how many times you want that duplicated and with that I'm still not sure, I've never tried that before.

  14. #14
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Auto Multiply Rows

    And I have to go now, I have a meeting coming up.
    you might want to start a new post for this as it is a new problem. You can put a link to this post as a reference and post your question there, someone will come along to help.

  15. #15
    Registered User
    Join Date
    12-12-2014
    Location
    Ankara
    MS-Off Ver
    2010
    Posts
    41

    Re: Auto Multiply Rows

    Quote Originally Posted by Sam Capricci View Post
    For post #11 I really don't follow you. If you could upload a sample workbook with what you have AND what you expect that would be helpful.
    it sounds like A1 through A640 will all be 1 but then you talk about the first 8 as 1, the next 8 will be 2, etc. Where would these be located?
    This formula will give you 8 1s, then 8 2s etc anywhere you put it...
    =ROUNDUP(ROWS($A$1:A1)/8,0)
    But if that isn't what you want, then, as I noted, a workbook with what you have AND what you expect would be very helpful.
    Dear Sam
    Forget our sample excel
    Pls look
    https://www.excelforum.com/excel-gen...ml#post5404033

  16. #16
    Registered User
    Join Date
    12-12-2014
    Location
    Ankara
    MS-Off Ver
    2010
    Posts
    41

    Re: Auto Multiply Rows

    Quote Originally Posted by Sam Capricci View Post

    As for post #12, I don't know how to get the formula to change midstream from 8 to 16 then back to 8, or from 16 to 8 then back to 16. Now, I think I know how to adjust the formula but that would require a helper column as a reference. You'd have to put the number in another cell beside the institution that tells how many times you want that duplicated and with that I'm still not sure, I've never tried that before.
    Dear Sam
    Think that
    We did an Excel for (multiply) 8
    I need another excel, in this i need 16 (different from other)
    I put this formula to another excell (for multiply 16)
    It didn't work
    Just changed two 8 to 16
    =IF(INDEX(Sheet1!A$2:A$1000000,ROUNDUP(ROW(A1)/16,0))=0,"",INDEX(Sheet1!A$2:A$1000000,ROUNDUP(ROW(A1)/16,0)))

    I think it must work

    regards
    Last edited by c a g a t a y; 10-02-2020 at 01:47 PM.

  17. #17
    Registered User
    Join Date
    10-01-2020
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Auto Multiply Rows

    Merhaba,

    Unfortunately, I couldn't send you link or attach solved file.

    However, you should follow the below written formula:

    =IF(ROWS(D$2:G2)>H$2*I$2, "",INDEX($A$2:$A$6,MOD(ROWS(D$2:G2)-1,I$2)+1))

  18. #18
    Registered User
    Join Date
    10-01-2020
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Auto Multiply Rows

    Merhaba,

    Please follow the below written formula:

    =IF(ROWS(D$2:G2)>H$2*I$2, "",INDEX($A$2:$A$6,MOD(ROWS(D$2:G2)-1,I$2)+1))
    I managed to attach my solved sample file.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    12-12-2014
    Location
    Ankara
    MS-Off Ver
    2010
    Posts
    41

    Re: Auto Multiply Rows

    Merhaba Ilgar
    I solved formula problem in this topic
    Do you help me in another topic?
    https://www.excelforum.com/excel-gen...ml#post5404033

    Bir Beden İki Can, Can AzerbeyCAN

+ 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. [SOLVED] count and multiply with 2 rows
    By leydet4 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-10-2019, 01:23 AM
  2. Multiply the # of rows by the number in column A
    By Josephrandall in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-28-2017, 12:27 PM
  3. [SOLVED] Make A Cell Auto Multiply By A Constant Number
    By CoopertheRescuedog in forum Excel General
    Replies: 2
    Last Post: 02-03-2016, 01:26 AM
  4. Multiply rows with a value before adding.
    By ravicse in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-04-2014, 11:59 AM
  5. Replies: 1
    Last Post: 01-24-2014, 06:20 PM
  6. Multiply Rows By 365
    By marinos.a in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-11-2010, 04:02 PM
  7. Replies: 15
    Last Post: 07-31-2006, 03:15 AM

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