+ Reply to Thread
Results 1 to 8 of 8

Increase the amount of rows based on cell value

  1. #1
    Registered User
    Join Date
    03-15-2015
    Location
    España
    MS-Off Ver
    2013
    Posts
    80

    Increase the amount of rows based on cell value

    Hello there,

    I have a table that has the following data:
    State City Location Neighborhood Tables
    Antioquia Medellín Puesto # 2 Comuna 1 33
    Antioquia Bello Another Puesto Comuna 2 10

    As you can see, there's a table named "Tables" which means the amount of positions inside a neighborhood. The first one has 33. Therefore, I need to find a way to multiply the amount of rows of the first one 32 times, so I end up with 33 total rows for the first row. AND replace or add a new column with the number of the table. Like this:

    State City Location Neighborhood Tables Table #
    Antioquia Medellín Puesto # 2 Comuna 1 33 1
    Antioquia Medellín Puesto # 2 Comuna 1 33 2
    Antioquia Medellín Puesto # 2 Comuna 1 33 3
    Antioquia Medellín Puesto # 2 Comuna 1 33 .... 33
    Antioquia Bello Another Puesto Comuna 2 10 1
    Antioquia Bello Another Puesto Comuna 2 10 2
    Antioquia Bello Another Puesto Comuna 2 10 .... 10

    Any advise?

    I have attached a spreadsheet containing the original data. It does not contain any sensitive information.
    Attached Files Attached Files
    Last edited by elbrujo; 09-21-2019 at 09:22 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    06-02-2012
    Location
    London, England
    MS-Off Ver
    365
    Posts
    397

    Re: Increase the amount of rows based on cell value

    You may be able to use formulas for this but it would be complicated so perhaps a VBA solution would work?

    Copy the code below, right click the sheet tab and select View Code. Paste the code into the sheet module and run it.

    It's not particularly fast (well not on my old laptop anyway) but it will get the job done eventually.

    Please Login or Register  to view this content.
    Beth.

  3. #3
    Registered User
    Join Date
    03-15-2015
    Location
    España
    MS-Off Ver
    2013
    Posts
    80

    Re: Increase the amount of rows based on cell value

    Beth, thank you very much! it worked perfectly fine indeed, it took some time, but it worked!

    Thank you for your help,

  4. #4
    Valued Forum Contributor
    Join Date
    06-02-2012
    Location
    London, England
    MS-Off Ver
    365
    Posts
    397

    Re: Increase the amount of rows based on cell value

    Happy I could help

    I hope it was at least quicker than completing the task manually!

    Beth.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Increase the amount of rows based on cell value

    Another option, which should be significantly faster.
    Please Login or Register  to view this content.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Increase the amount of rows based on cell value

    It can be done with a formula, too. But your data set is so large, that I wouldn't even bother trying. Stick with the VBA solutions...
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  7. #7
    Registered User
    Join Date
    03-15-2015
    Location
    España
    MS-Off Ver
    2013
    Posts
    80

    Re: Increase the amount of rows based on cell value

    Thank you @Fluff13 for your time ! it works fine
    And yes @Glenn, I thought it wouldn't be very efficient.

    Thanks you all for your help!

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Increase the amount of rows based on cell value

    Glad to help & thanks for the feedback

+ 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. Adding Salary Increase to already prorated salary amount based on a salary increase date
    By Excelhelppleasethank in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-09-2019, 03:43 PM
  2. [SOLVED] Macro to insert rows with copied text a certain amount of times based on a value in a cell
    By rhendrickson11 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-21-2017, 11:20 AM
  3. Automatically increase rows based on cell data
    By BoydCrowder'sCollar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2015, 05:14 AM
  4. betting strategy, IF amount won is less than starting amount, increase bet
    By Butcher1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-09-2014, 07:53 PM
  5. Replies: 1
    Last Post: 12-07-2011, 01:19 PM
  6. Replies: 4
    Last Post: 08-13-2011, 05:51 PM
  7. Show an original amount plus % increase in same cell.
    By rosebud28805 in forum Excel General
    Replies: 3
    Last Post: 01-14-2011, 01:54 PM

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