Look for series cells with specific text and add row if series not complete

[xtrasico]

Hi everyone!

I have been a mrexcel user for a long time, but I registered on 2014 and I have never posted because I always found what I needed or something close that I could modify to get the results I needed. Having said that, I need your help because I can't find something close to what I need. I use IDEA for big data analysis for my audits. I am working with a database of 3.7 million records (rows) with over 200 fields (columns). Since Excel can't handle that, I use IDEA and then my results / findings / summaries are exported to Excel.

I have a simple table that I need to add some rows when a specific text is not in a place. For example: let's say that my audit has a summary of information of 1 million records, summarized by certain groups and the end table is supposed to have 55 rows. This is literally what I have in column C:

Retirement Type (FIELD1 )
CSRS1
CSRS2
CSRS3
CSRS4
Empty cell (because there is no info in it and not needed, but the other columns HAVE data)
CSRS1
CSRS2
CSRS3
CSRS4
Empty cell
CSRS1
CSRS2
CSRS3
CSRS4
Empty cell

This is suposed to go on for 55 rows (eleven groups of 5 cells). My problem is that some groups do not have, for example CSRS2 and CSRS4, or CSRS1 and CSRS2, randomly, so the cell series would look like:

Retirement Type (FIELD1 )
CSRS1
CSRS3
Empty cell
CSRS1
CSRS3
CSRS4
Empty cell

So, I need to add CSRS2 and CSRS4 (or CSRS1 and CSRS2) to the cell series by adding additional rows, and typing the cell contents. I know this can be done manually because it is not that much, but I want to automate it to use it in each project / analysis I found I can do it.

To summ up:
I need Excel to look if the cell series is complete. If it is, continue down column C until the end of the data. If it is not complete, Excel should stop, add the row and insert the correct data in the cell that it is supposed to go next to look literally like this:
CSRS1
CSRS2
CSRS3
CSRS4
Empty cell
CSRS1
CSRS2
CSRS3
CSRS4
Empty cell
CSRS1
CSRS2
CSRS3
CSRS4
Empty cell

I do not have the skills to produce this kind of code. Heck... any code at all.
Thank you so much!

 

[xtrasico]

Um? If you want consistency of all macros you might also like to replace that code with that above and use the original "CSRS" names of the Col C titles for that sheet.

Hi BrianJN1!

This worked PERFECTLY! You are AWESOME! You have NO IDEA how much this is going to bring our time down in this project. The CSRS is only one spreadsheet with 3 worksheets, but the FERS is 3 x 3.

Manually CSRS is like 15-20 minutes per worksheet, we are talking one hour or less. But with your code added to my macro, less than 5 seconds.

FERS is more complicated. I would dare to say like 10 hours manually, but with your code, again like 5 seconds per 3 worksheets. It takes more time to load Excel than to run the macro and get the results. haha

Thank you! Thank you! Thank you!

 

[BrianJN1]

That is most gratifying to hear.
Thank you for the reply.