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
Book1.xlsb
Description: application/vnd.ms-excel.sheet.binary.macroenabled.12
