Damimkader: Thank you! This is very close. (And yes - I'm working with Excel 07).
I should have provided more example data, and clarified that data may exist
for several years (not just 2005). It can also exist for several other age
groups (not just 1). (In fact, there are additional variables that further
define unique groups that I've excluded for simplicity.)
I attached a revised version with a new data point, and a description of
how the macro handles it.
I added a new case that introduces data for another year (06) for Country
B, age group "3".
The macro skips the group defined by B+05+1, and inserts missing rows only
for the group, B+06+3. But, if the new case was for Country A, the macro
works fine.
I suspect it's because three rows for B (for year 05) are now immediately
followed by another row for B (but for year 06). The macro doesn't realize
that this new B row is actually a new unique group (Country B for Year 06).
Any suggestions on how to revise the macro for this? Thanks again.
On Friday, March 23, 2012 10:25:57 AM UTC-4, Damimkader S. Meeran wrote:
>
> Hi,
>
> Based on the explanation you provided. I've created a workbook which is
> attached herewith. Below are some of the requirements for this to function.
>
> 1. There is a sheet called "Time Groups" which contains all the time
> groups in the data once. And they should match the time groups in the data
> exactly.
> 2. Cell B2 contains a count of the groups. It is used by the macro to
> understand the number of unique groups in Times Groups.
> 3. Column F is a column which contains a formula which checks the count of
> the "Country" + "Year" + "Age Group", based on the explanation you provided
> it is seems that the combine CYA should appear 4 times if there are 4
> groups. The program uses this logic to populate the missing information.
> 4. Run the macro "Insert_Missing_Rows".
> 5. This has been written in Excel 2007. So if it is run on an earlier or
> later version of excel this might not function as certain VBA functions
> might not be available.
>
> Please run it and let me know if this helps. Holler with questions.
>
> The Code for reference
>
> Sub Insert_Missing_Rows()
> Dim i As Long, j As Integer, k As Integer, cnt As Integer, TempSheetName
> As String
> cnt = 0
>
> For i = 5 To 1000
> If Cells(i, 1) <> "" Then
> If Cells(i, 1) <> Cells(i + 1, 1) And Cells(i, 6) < Cells(1, 2)
> Then
> Sheet1.Select
> Diff = Cells(1, 2) - Cells(i, 6)
>
> For j = 1 To Diff
> Rows(i + 1 & ":" & i + 1).Select
> Selection.Insert Shift:=xlDown
> Next j
>
> Sheets("Time Groups").Select
> Sheets("Time Groups").Copy After:=Sheets(3)
> TempSheetName = ActiveSheet.Name
> Sheet1.Select
>
> For k = i - (Cells(i, 6) - 1) To i
> For j = 1 To Sheet1.Cells(1, 2)
> If Cells(k, 4) = Sheets("" & TempSheetName).Cells(j,
> 1) Then
> Sheets("" & TempSheetName).Select
> Rows(j & ":" & j).Select
> Selection.Delete Shift:=xlUp
> Sheet1.Select
> Exit For
> End If
> Next j
> Next k
>
> Sheets("" & TempSheetName).Select
> Range("A1:A" & Diff).Select
> Selection.Copy
> Sheet1.Select
> Range("D" & i + 1).Select
> ActiveCell.PasteSpecial xlPasteAll
>
> Sheets("" & TempSheetName).Select
> Application.DisplayAlerts = False
> ActiveWindow.SelectedSheets.Delete
> Application.DisplayAlerts = True
> Sheet1.Select
>
> For k = i + 1 To i + (Diff)
> Range("A" & k & ":C" & k).Select
> Selection.FillDown
> Next k
> i = i + (Diff)
> End If
> Else
> Exit For
> End If
> Next i
>
> End Sub
>
>
> Regards,
> Damimkader S. Meeran
>
>
>
>
> On Thursday, March 22, 2012 11:40:31 PM UTC+5:30, 0 1 wrote:
>>
>> I have data like so:
>>
>> Country Year AgeGroup Time Count
>> A 05 1 0-6mo 10
>> A 05 1 6-12mo 5
>> -------------------------------------
>> B 05 1 0-6mo 12
>> B 05 1 12-18mo 4
>> B 05 1 18-24mo 75
>>
>> (I added the "-----" dividing line just to help visualize things ... it's
>> not actually in the spreadsheet.)
>>
>> Every unique group - defined by unique concatenation of
>> Country+Year+AgeGroup should have four rows, one for each of these Time
>> intervals: 0-6mo, 6-12mo, 12-18mo, and 18-24mo.
>>
>> The group defined by CountryA+05+1 is missing rows for 12-18 and 18-24.
>> The group defined by CountryB+05+1 is missing rows for 6-12.
>>
>> How can I automatically detect when a unique group has < 4 rows, and then
>> insert the missing number of rows (doesn't matter where I insert them),
>> imputing the value for County, Year, AgeGroup, and Time (i.e., the Time
>> interval(s) that are missing). Count should get a value of 0.
>>
>> So the code would build and add these three rows:
>>
>> Country Year AgeGroup Time Count
>> A 05 1 12-18mo 0
>> A 05 1 18-24mo 0
>> B 05 1 6-12mo 0
>>
>> Any suggestions?
>>
>>
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
insert missing rows - revised.xlsb
Description: application/vnd.ms-excel.sheet.binary.macroenabled.12
