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

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.

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.

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

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.

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

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.

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.

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.

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

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!