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

xtrasico

New Member
Joined
Jul 1, 2014
Messages
7
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!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
This was a nice little challenge.
You are probably going to have to come back for further assistance judging by your remark about coding.

I built this table in a couple of phases so as to reflect what happened when the macro ran.

Firstly Col A is the repeated series that you required.
Col B is that same series with various CSRS cells deleted. The Green represents where rows have been "lost".
Now Col C is what has occurred after running the macro. It shows in red those rows which were added. As you look at Col B, the first Green CSRS3 has really been moved down to Row 8 so that your series is valid. The last 3 items in that column have been relegated to rows off the shown table.

In the macro is an advice as to what needs to be changed so that the full missing row rather than the CSRSx cell is filled.

Feed back is welcomed.
Codes:
Code:
Sub Ret_Series_Build()
Dim LastRw
LastRw = 55
Dim a, b As Long
b = 1
For a = 1 To LastRw

If Range("C" & a).Value <> "CSRS" & b And b < 5 Then
Rows(a & ":" & a).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C" & a).Value = "CSRS" & b
' [COLOR=#ff0000][B]above row needs to be changed so that it copies and pastes required data[/B][/COLOR] and then the following block can be deleted
Range("C" & a).Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
'[COLOR=#006400]The above block show which CSRS rows have been added - they appear in[/COLOR] [B][COLOR=#ff0000]red[/COLOR][/B].
Else
If b = 5 Then
Range("C" & a).Value = ""
End If
End If
b = b + 1
If b = 6 Then b = 1

Next
End Sub


Table:Excel 2013/2016
ABC
CSRS1CSRS1CSRS1
CSRS2CSRS2CSRS2
CSRS3CSRS3
CSRS4CSRS3CSRS4
CSRS4
CSRS1CSRS1CSRS1
CSRS2CSRS2CSRS2
CSRS3CSRS3
CSRS4CSRS4CSRS4
CSRS1CSRS1CSRS1
CSRS2CSRS2CSRS2

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

</tbody>
Sheet1

Obviously I am not in a position to know from where that/those missing rows are to come so the code has NOT been written to address that, just identify where to insert.
 
Last edited:
Upvote 0
Hello BrianJN1!

It worked PERFECTLY!!!
I only modified the total of rows (LastRw = 56)
My fault, because I told you it was 55, but that was data.
In total there were 56 lines because the first line has the field names (headers).
Hence, I needed to modify "a" value to start at row 2 (For a = 2 To LastRw).

Thank you so much for your help!!!
I really APPRECIATE it!!!

To tell you a bit about this project being programmed in IDEA: It is performed every year and it has around 3.6-3.8 million records (rows) with over 200 fields (columns). Previously it took around 4 months to do all the project calculations and worsheets because, even though the data was analyzed in seconds, the team did not know how to summarize all the work. For example, last year for one section they prepared 66 spreadsheets with 11 worksheets each, for a total of 726 worksheets with all the calculations for just ONE procedure. It took like a month for a couple of auditors to prepare everything. I joined the team last year, after the project was finished, and I did not know about it. I did some scripts that analyzed 100% of something (not a sample) and my team leader liked it, so I am assigned now to every data analysis phase of the projects, which I love. For this year's project, I reduced those 726 sheets to 2 spreadsheets with 2 worksheets each. So, from 726 to 4. I get that finished in about 15 seconds in IDEA and then in about 15 seconds in Excel. Let's say it takes 5 minutes... 10 minutes... anything is going to be better than what we had.

To sum up, we are talking about reducing the time of the project calculations from 4 months to maybe 1 hour. Maybe 2, because I have to load all the IDEA scripts and then Excel macros, to run them carefully.

This section you just programmed is going to eliminate at least 20-30 minutes of manual worksheet formatting, because now I can complete the macro in advance because without this section, formatting and finishing the worksheet automatically was impossible.

Thank you, thank you, thank you! Thank you very much!
 
Upvote 0
Maybe I should copy your comments to my CV :laugh:

Anyway I'm pleased that this has been highly beneficial for you, and it was interesting to have some understanding of how my small contribution could assist in such time saving.
 
Upvote 0
Ha,ha! Thanks. That remark was "tongue in cheek".
I'm actually comfortably retired, I use various IT projects to occupy myself.
 
Upvote 0
Ha,ha! Thanks. That remark was "tongue in cheek".

My first language is Spanish, so those kind of phrases I do not understand because I'll go literal on you. hahaha I had to Google it to understand "tongue in cheek" true meaning. I am glad you are retired.
 
Upvote 0
Maybe I should copy your comments to my CV :laugh:

Hey BrianJN1, or anyone else! I need more of your precious help.

This is for a different procedure: The same "C" column, the same 56 rows, almost the same text:

COLUMN HEADER
CSRS1 REG
CSRS2 LEO
CSRS3 OFFSET
CSRS4 OFFSET LEO
Empty cell


There are 3 more procedures that will use this code, but I guess it will be almost the same as the one above.
COLUMN HEADER
FERS1 REG
FERS2 LEO
FERS3 ATC
FERS4 RESERVE
Empty cell

COLUMN HEADER
FERSRAE1 REG
FERSRAE2 LEO
FERSRAE3 ATC
FERSRAE4 RESERVE
Empty cell

COLUMN HEADER
FERSFRAE1 REG
FERSFRAE2 LEO
FERSFRAE3 ATC
FERSFRAE4 RESERVE
Empty cell

What do you think?
Thanks!
 
Upvote 0
Hey BrianJN1, or anyone else! I need more of your precious help.

This is for a different procedure: The same "C" column, the same 56 rows, almost the same text:

COLUMN HEADER
CSRS1 REG
CSRS2 LEO
CSRS3 OFFSET
CSRS4 OFFSET LEO
Empty cell


There are 3 more procedures that will use this code, but I guess it will be almost the same as the one above.
COLUMN HEADER
FERS1 REG
FERS2 LEO
FERS3 ATC
FERS4 RESERVE
Empty cell

COLUMN HEADER
FERSRAE1 REG
FERSRAE2 LEO
FERSRAE3 ATC
FERSRAE4 RESERVE
Empty cell

COLUMN HEADER
FERSFRAE1 REG
FERSFRAE2 LEO
FERSFRAE3 ATC
FERSFRAE4 RESERVE
Empty cell

What do you think?
Thanks!
This is very similar to the original macro that I wrote except that I've used your Column C account titles as an array list. I've also left the red tinting of added rows. Actually if you was to run the macro on a blank sheet all titles would appear as red.

The code LEO is for the first of your blocks. To create the other three merely copy and paste this code into the VBA editor, change the macro name and substitute the Column C row names for thos in this block:
titles(0) = "CSRS1 REG"
titles(1) = "CSRS2 LEO"
titles(2) = "CSRS3 OFFSET"
titles(3) = "CSRS4 OFFSET LEO"
Just ensure that you keep titles(4)="" because that represents your blank row.

Code:
Sub LEO_Build()
Dim LastRw
LastRw = 56
Dim titles(0 To 4) As String
titles(0) = "CSRS1 REG"
titles(1) = "CSRS2 LEO"
titles(2) = "CSRS3 OFFSET"
titles(3) = "CSRS4 OFFSET LEO"
titles(4) = ""
Dim a, b As Long
b = 0
For a = 2 To LastRw

If Range("C" & a).Value <> titles(b) And b < 4 Then
Rows(a & ":" & a).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C" & a).Value = titles(b)
' above row needs to be changed so that it copies and pastes required data
Range("C" & a).Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
'The above block show which CSRS rows have been added - they appear in red.
Else
If b = 4 Then
Range("C" & a).Value = ""
End If
End If
b = b + 1
If b = 5 Then b = 0

Next
End Sub
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,628
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top