+ Reply to Thread
Results 1 to 16 of 16

Insert rows below based on specific criteria

  1. #1
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    89

    Insert rows below based on specific criteria

    Hi

    I have this Excel file (please see below, with my attempt included).
    What is not highlighted is the input data and what is highlighted in green is the expected output, but I am not getting it right. This is just a prototype, an example, as in the main actual files I have a dozen of such entries.

    Basically I have to make sure that it inserts new rows below the first row of each section. By section I mean the zone on column A that corresponds to the same Car Name.

    The condition would be to insert rows below the 1st one of each section, copy the one above it and change just columns B and C, as the year and months should decrease until they reach the start date, as it can be seen in the picture below (please note that the "so on", was used to explain how the trend should be until it reaches the last row that should be inserted by VBA).

    This has to be done for multiple car names, so I tried doing it with a for loop but I got stuck.

    To sum it up, the condition is that it has to be within the same section (car name) then below the 1st row to insert within the section to insert multiple rows by copying the one above it and change just columns B and C (year, respectively the month) until the end date = start date on the last added row, then stop adding rows on the specific section and move on to the next one.

    Any support would be greatly appreciated!


    Gordon
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Gordon85; 02-16-2021 at 01:16 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,474

    Re: Insert rows below based on specific criteria

    Hi Gordon,

    What happens if you sort first by column C then by B then by A. If you sort your data three times in that order, I think you can avoid any VBA. You could even put new entries on the bottom of the table and have it work.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    89

    Re: Insert rows below based on specific criteria

    Hi,

    Thank you for your remark, I do appreciate it.

    I have tried it , but it just mixes up the data. At the moment I am still attempting to do it by VBA within Excel

    My input data is not highlighted it is just white as default, the expected output is highlighted with green.
    The tricky part is that after inserting rows below the 1st input row, I have to adjust columns B and C such that I go backwards in time in terms of years and months (such that months would go backwards from 12 to 1) from end date to start date until end date = start date, the rest of the cells of the inserted rows should be identical to the input row, once end date = start date that it should stop inserting rows within the region.
    Last edited by Gordon85; 02-16-2021 at 02:44 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,240

    Re: Insert rows below based on specific criteria

    Gordon,

    Attached does what you want.
    Click A2 and run ADDROWS macro, then click first "Porsche" row and run ADDROWS, etc


    Please Login or Register  to view this content.
    If you want to "automate" changes of Marque, add a line that finds when the "text" in Col A changes, reset that as the ActiveRow, and "Next" it, so the Code restarts with each change of Marque.

    Only interesting point is your "Mileage" formula which ends up at 48k on the last month?

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 02-16-2021 at 03:01 PM.

  5. #5
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    89

    Re: Insert rows below based on specific criteria

    Ochimus,

    That works like a charm, it is almost done, thank you so much!
    I guess an old fart like myself would have never done it so quick.

    Nice catch there, I guess I can impose a condition such as: if (value on column F == 0) then delete row that row.

    By the way, do you think that there is a way to run the macro at the same time for multiple entries, such as A3, A4, and so on? Such as run the macro for each row below A2? I have dozens of input data myself, around 11k.

    But so far, it is brilliant, thanks!

  6. #6
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,240

    Re: Insert rows below based on specific criteria

    As someone also in the "Home Stretch" , we Old Codgers need to stick together against these bright young Whippersnappers!

    Not quite with the point about "Multiple entries"? I assumed the Opening Set would be one Marque per row, so the Macro would deal with row 2, then go to what would have been row 3 but is now row whatever, and add the rows needed, then go to what would have been row 4, and so on?
    Am I missing something?

    Ochimus

  7. #7
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    89

    Re: Insert rows below based on specific criteria

    Hi there Orchimus,
    Thank you for your quick reply, it is much appreciated, and I also do appreciate your sense of humour.
    In order to get rid of the 0 value I added this bit (please see below) in the module, and it does work, so again, thanks for the catch

    Please Login or Register  to view this content.

    What you did is perfectly fine. Now, regarding your question, I am trying to run this for multiple input data at the same time if possible, so the question is: would the macro be able to run for the "Porsche" entry and generate new rows the same way as it does for the Ford Mustang for example? I have quite a few of data inputs like those 2, around 11k, so that's why I'm asking, otherwise I would have to run the macro for each input individually, like one at a time, like run for Ford Mustang, then wait to add the rows, and move on to the next input and so on. So I'd be more than grateful if you could let me know if there is any possibility to run the macro for the whole input data from row A2 onwards at the same time, such as not having to run it manually for each input separately.
    Last edited by Gordon85; 02-16-2021 at 03:38 PM.

  8. #8
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,240

    Re: Insert rows below based on specific criteria

    Gordon,

    Yes you can "automate" it, and I'd suggest doing it as follows.


    1. How many Marques are there in Col A?
    Assuming your Brands are not repeated in the list, get the Code to count "uniques" in Col A (y=Number of Unique Brands). Then set the Code to how many times it has to "cycle" (For b=1 to y), and at the END of the Code add NEXT.
    It will now run as many times as you have unique names.

    Next challenge is to prevent it "re-adding" rows that have been done already, so for each "loop" it has to start when Col A changes. So it looks at the row below. If it's a different name (e.g. A2 is Ford and A3 is Fiat), it can add rows.
    If it's the same name, look down the Column, find the row where the Name in Col A changes, make THAT the new "active row", and then start the adding.
    And if the "row below" is Blank, then tell the Code it's reached the end of the list, and ends.

    Hope that is clear?

    Ochimus
    Last edited by Ochimus; 02-16-2021 at 04:04 PM.

  9. #9
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    89

    Re: Insert rows below based on specific criteria

    Orchimus,

    Thank you for your reply, it is much appreciated. Yes it is clear, I am trying to follow the guidelines, but I got stuck at the challenge.
    I got the gist of it, but I feel I am going bonkers. I am still trying to do this for this example I posted here such to do it for the Porsche as well, then I'll apply it at a bigger scale.
    Last edited by Gordon85; 02-16-2021 at 04:03 PM.

  10. #10
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    89

    Re: Insert rows below based on specific criteria

    Orchimus, I tried automating the code. I got 11358 unique values, so I should execute the code from 1 to 11358 for the full scale Excel. I am trying to run the macro at the same time for the example posted here, such as executing the code for both the Ford and for the Porsche at the same time, but I got stuck with that. My plan was to attempt for this example and then go for the big scale. I gave you the deserved Rep+ for the Code provided in Post#4, it is much appreciated, thanks for helping so far! I still tried to automate it, but I failed miserably..

    Edit;
    This is what I've tried, but now it just gets stuck in an endless loop, probably due to the For, probably due to adding extra rows...where not necessary
    Yeah, I just tried it again, and it just overlaps extra rows where not necessary, so I got something wrong in this endless loop

    Please Login or Register  to view this content.
    Last edited by Gordon85; 02-16-2021 at 05:49 PM.

  11. #11
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,240

    Re: Insert rows below based on specific criteria

    Sorry for delay in getting back, but dinner called, and She Who Must Be Obeyed takes few prisoners if ignored!

    Attached creates a "virtual list" of the different names in Col A, which tells the Code how many times it has to run.

    Starting at row 2, it adds as many rows as there are months, resets the Years and Months where necessary.

    It then "resets" the Active row as the first row of the next Brand, repeats the cycle, resets and repeats until it has added as many cycles as there are Brands.

    Obviously if it finds the "next row" is blank, it stops the Macro.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Ochimus; 02-16-2021 at 06:21 PM.

  12. #12
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,240

    Re: Insert rows below based on specific criteria

    Just realised I didn't acknowledge that the "Unique Count Scripting Dictionary" came from an MVP on www.mrexcel.com wayyyyy back in 2013!

    Ochimus

  13. #13
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    89

    Re: Insert rows below based on specific criteria

    Orchimus,

    Cheers mate! No sweat at all, I fully understand, am in the same situation myself, haha!
    I tried the new code, works well, but the only tweaking that has to be done to it, is that I have some Marques that are still not kind of unique.
    For example I tried for 100 Rows in the bigger sample and ended up with like 5k rows output, some Marques were something like this (please see the picture attached)
    I guess the issue arises as exposed with the yellow highlight due to the name, it might scramble the output so I guess there must be a differentiation or such, otherwise it might get caught in an infinite loop due to the input having similar marque
    Attached Images Attached Images
    Last edited by Gordon85; 02-16-2021 at 07:01 PM.

  14. #14
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,240

    Re: Insert rows below based on specific criteria

    1. Size.
    If you have 11k of different individual records, and an average two-year span per record, that's 11k x 24 = 266k rows, so 100 records would produce 2.4k rows. Take it to a three year spread as you had with the Ford and it's 3.6k.
    Worksheets have just over 1m rows, but the processing can be r..a...t..h...e...r slow when you get anywhere near that? My suggestion is you create more worksheets and split the vehicles between them? (Remember to change the Sheet ref at the beginning of the Code if you do!)

    2. If you have multiple vehicles of the same Brand, as shown in the screenprint, the Dictionary approach in the Code will count them as one, Easiest solution is to add a Column, give each vehicle a Record number and use that in the Code to distinguish instantly which Porche or Ford or whatever you are talking about?


    Ochimus

  15. #15
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    89

    Re: Insert rows below based on specific criteria

    Hi,
    Thanks for the quick reply.
    Basically regarding sitution #2, if it counts them as one I'm afraid it might get faulty and caught in an endless loop, whilst sneak peaking what the VBA was running I saw that the Excel slider was going up whilst the Code was running and sometimes even a bit upwards.
    As exposed in Post #13, I guess the issue might be that for 2 identical inputs it might create an infinite loop between them.

    I just tried running the code (please see picture2) below. In yellow is the input, as you can see the names are identical and that might pose a problem, as picture 3 also below exposes the output (green means that it parsed just the 1st input, and the 2nd one that is still highlighted with yellow was not parsed, this was tested on v2.

    Is there any way that this can be tweaked, such as if having this issue with 2 similar marques to run both of them back to back instead of just running the 1st one and stopping afterwards? Such as leaving behind the 2nd entry?

    Picture 2 (the one at the top, just with yellow) = input
    Picture 3 (the one at the bottom, with yellow and green) = output

    I guess the yellow highlighted area is the problematic area as the marque is identical and the code v2 runs just the 1st one, can it run both of them eventually back to back? Because in the larger version of the documents I have some cases like that

    Yeah, I guess, you're right. Perhaps it would require something like a for loop and (if 2 consecutive input rows have the same Marque on column A, then in column J at the 1st occurrence add "marque1" in the cell and for its offset column row+1, column J add "marque2" or so, right?

    Cheers!
    Attached Images Attached Images
    Last edited by Gordon85; 02-16-2021 at 08:02 PM.

  16. #16
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,240

    Re: Insert rows below based on specific criteria

    Gordon,

    The issue with having "two of the same" isn't anything to do with an "infinite loop", it's that the Code will stop running when it gets to the end of the number of 'unique' names.

    If you have ten different vehicles from five different Marques, whatever order they are in down the worksheet, the current Code will run five times from row 2, then stop, no matter how many other vehicles are still untouched.

    As I said above, you need to add a new column, give each vehicle a GENUINELY unique value of some kind, even if just an incremented value of 1 to whatever, and point the Code at THAT in the Dictionary.

    Code will then chunter away happily until it reaches the last "unique" indicator - but with the caution that you could run out of rows very quickly if you have an extensive spread on the vehicles? On a seven year spread, you will end up with 966k rows of data!

    Which rather raises the question of WHY you need to show every monthly row on each vehicle in the first place?

    Ochimus

+ 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] How To Insert A Row After Specific Criteria
    By PaddyP in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2020, 08:16 AM
  2. [SOLVED] Match Record based on criteria and insert in specific cell on 2nd sheet
    By scouse13 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-18-2020, 03:43 PM
  3. Insert rows to the top of the sheet that meet specific criteria.
    By unmasked_crusader in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2019, 09:35 PM
  4. [SOLVED] VBA code to insert line break within a cell if specific criteria is met.
    By pipoliveira in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-19-2018, 09:28 AM
  5. Calculate subtotals based on specific criteria + put in specific order
    By msls09 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-28-2013, 12:47 AM
  6. HELP! Macro to Copy specific cells from one sheet to another based on specific criteria
    By atriscritti in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-11-2012, 11:05 AM
  7. Macro to insert data in specific cell based on criteria
    By SAsplin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-01-2011, 08:08 AM

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