Find missing member from list and add a blank row there

[wapcoslucknow]

Hello,

I need a very special answer from you, since I am not fluent in VB, I am not able to sole it.

I have a group (slave group) in Excel like: -

HT line length	0.01
HT Poles	4
LT Line Length	0.56
LT Poles	10
DTR 25 KVA	2
BPL	118

And a master group like: -

HT line length

HT Poles

LT Line Length

LT Poles

DTR 10 KVA

DTR 16 KVA

DTR 25 KVA

DTR 63 KVA

BPL

Master group has 9 elements, whereas sub group can have any number of elements from these 9 items (maximum 9). Here, 6 members out of 9.
My requirement is arranging sub group like: -

HT line length	0.01
HT Poles	4
LT Line Length	0.56
LT Poles	10
DTR 10 KVA
DTR 16 KVA
DTR 25 KVA	2
DTR 63 KVA
BPL	118

Here, blank rows are inserted, as per master group i.e. master group has to be followed and blank row should be inserted at missing points.
Also there will be so many sub group in the same sheet (one after other downwards)
I hope you understand my question.

Thanks

 

[mumps]

Will the elements always start with "DTR"? Are the Master group and sub groups on different sheets? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheets that includes 3 or 4 sub groups so we can get an idea of how your data is organized. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[wapcoslucknow]

Query.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Master Data			Example 1 - Slave data (earlier)			Example 2 - Slave data (earlier)			Example 3 - Slave data (earlier)
2	HT line length			HT line length	0.01		HT Poles	3		HT line length	1.23
3	HT Poles			HT Poles	4		LT Line Length	0.26		DTR 63 KVA	1
4	LT Line Length			LT Line Length	0.56		LT Poles	40
5	LT Poles			LT Poles	10		DTR 16 KVA	1
6	DTR 10 KVA			DTR 25 KVA	2		DTR 25 KVA	3
7	DTR 16 KVA			BPL	118		BPL	32
8	DTR 25 KVA
9	DTR 63 KVA
10	BPL
11				Slave data (after)			Slave data (after)			Slave data (after)
12	Master data can be anywhere. There is nothing to do with location of master data, however, its sequence MUST be followed i.e. from "HT line length" to "BPL". Only 9 members will be looked in Slave data set. All slave data sets are stored in only One sheet, one after other downwards (say 100 times)			HT line length	0.01		HT line length			HT line length	1.23
13				HT Poles	4		HT Poles	3		HT Poles
14				LT Line Length	0.56		LT Line Length	0.26		LT Line Length
15				LT Poles	10		LT Poles	40		LT Poles
16				DTR 10 KVA			DTR 10 KVA			DTR 10 KVA
17				DTR 16 KVA			DTR 16 KVA	1		DTR 16 KVA
18				DTR 25 KVA	2		DTR 25 KVA	3		DTR 25 KVA
19				DTR 63 KVA			DTR 63 KVA			DTR 63 KVA	1
20				BPL	118		BPL	32		BPL
21
22
23				Here, it can be seen that data against each element (in next column) is maintained, which is the main reason for doing this exercise.
Sheet1

 

[mumps]

Master data can be anywhere.

I still need to know where the master data is located so I can compare it to the slave data. In your screen shot, it is located in the range A2:A10 in the same sheet as the slave data. Also, you say that:

All slave data sets are stored in only One sheet, one after other downwards

In your screen shot, the slave data is located in columns D to K which is not one after the other downwards. I would need to know exactly where the master data is located and if it is on a different sheet from the slave data. I would also need to see a screen shot of the slave data showing exactly how it is organized in your actual file, one after the other downwards. If the data is located on different sheets, I would also need the sheet names. Please understand that I'm not trying to be difficult. I need to have this information in order to suggest a working solution.

 

[jolivanes]

This assumes that your "Master Group" is in Column A, Cells A1 to A9
Your "slave groups" are in Column D and Column E separated By at least 1 empty cell/row
Columns G and H are free to use (empty)
After code is finished, columns D, E and F can be deleted without negatively impacting your data.

Sub AAAAA()
Dim myAreas As Areas, myAreas_2 As Areas
Dim a, i As Long, ii As Long, j As Long
Application.ScreenUpdating = False
a = Range("A1:A9").Value
Set myAreas = ActiveSheet.Columns(4).SpecialCells(2).Areas
    For i = 1 To myAreas.Count
        Cells(Rows.Count, 7).End(xlUp).Offset(2).Resize(9).Value = a
    Next i
    Cells(1, 7).Resize(2).Delete Shift:=xlUp
Set myAreas_2 = ActiveSheet.Columns(7).SpecialCells(2).Areas
    For ii = 1 To myAreas_2.Count
        With Range(myAreas_2(ii).Address)
            For j = 1 To 9
                On Error Resume Next
                    myAreas_2(ii).Cells(j).Offset(, 1).Value = Range(myAreas(ii).Address).Find(myAreas_2(ii).Cells(j)).Offset(, 1).Value
                On Error GoTo 0
            Next j
        End With
    Next ii
Range("D1:F1").EntireColumn.Delete Shift:=xlToLeft
Application.ScreenUpdating = True
End Sub

 

[wapcoslucknow]

Thanks, It is working perfectly, but it is deleting column F !. Please rewrite it so that data in column F is also retained. Thanks again, but waiting for your response.

This assumes that your "Master Group" is in Column A, Cells A1 to A9
Your "slave groups" are in Column D and Column E separated By at least 1 empty cell/row
Columns G and H are free to use (empty)
After code is finished, columns D, E and F can be deleted without negatively impacting your data.

Sub AAAAA()
Dim myAreas As Areas, myAreas_2 As Areas
Dim a, i As Long, ii As Long, j As Long
Application.ScreenUpdating = False
a = Range("A1:A9").Value
Set myAreas = ActiveSheet.Columns(4).SpecialCells(2).Areas
    For i = 1 To myAreas.Count
        Cells(Rows.Count, 7).End(xlUp).Offset(2).Resize(9).Value = a
    Next i
    Cells(1, 7).Resize(2).Delete Shift:=xlUp
Set myAreas_2 = ActiveSheet.Columns(7).SpecialCells(2).Areas
    For ii = 1 To myAreas_2.Count
        With Range(myAreas_2(ii).Address)
            For j = 1 To 9
                On Error Resume Next
                    myAreas_2(ii).Cells(j).Offset(, 1).Value = Range(myAreas(ii).Address).Find(myAreas_2(ii).Cells(j)).Offset(, 1).Value
                On Error GoTo 0
            Next j
        End With
    Next ii
Range("D1:F1").EntireColumn.Delete Shift:=xlToLeft
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

Change this

Range("D1:F1").EntireColumn.Delete Shift:=xlToLeft

to this

Range("D1:E1").EntireColumn.Delete Shift:=xlToLeft

Or better yet, use this instead. No deleting Columns and no worries about data outside of Columns D and E.
It puts the reconfigured data into Columns D and E.
This assumes that your "Master Group" is in Column A, Cells A1 to A9
Your "slave groups" are in Column D and Column E separated By at least 1 empty cell/row

Sub AAAAA_2()
Dim myAreas As Areas, myAreas_2 As Areas
Dim a, i As Long, ii As Long, j As Long
Dim lr As Long, lc As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
Application.ScreenUpdating = False
a = Range("A1:A" & lr).Value
Set myAreas = ActiveSheet.Columns(4).SpecialCells(2).Areas
    For i = 1 To myAreas.Count
        Cells(Rows.Count, lc + 100).End(xlUp).Offset(2).Resize(lr).Value = a
    Next i
    Cells(1, lc + 100).Resize(2).Delete Shift:=xlUp
Set myAreas_2 = ActiveSheet.Columns(lc + 100).SpecialCells(2).Areas
    For ii = 1 To myAreas_2.Count
        With Range(myAreas_2(ii).Address)
            For j = 1 To lr
                On Error Resume Next
                    myAreas_2(ii).Cells(j).Offset(, 1).Value = Range(myAreas(ii).Address).Find(myAreas_2(ii).Cells(j)).Offset(, 1).Value
                On Error GoTo 0
            Next j
        End With
    Next ii
With Range(Cells(1, lc + 100), Cells(Cells(Rows.Count, lc + 100).End(xlUp).Row, lc + 100)).Resize(, 2)
    .Copy Cells(1, 4)
    .ClearContents
End With
Application.ScreenUpdating = True
End Sub

 

[wapcoslucknow]

Change this

Range("D1:F1").EntireColumn.Delete Shift:=xlToLeft

to this

Range("D1:E1").EntireColumn.Delete Shift:=xlToLeft

Or better yet, use this instead. No deleting Columns and no worries about data outside of Columns D and E.
It puts the reconfigured data into Columns D and E.
This assumes that your "Master Group" is in Column A, Cells A1 to A9
Your "slave groups" are in Column D and Column E separated By at least 1 empty cell/row

Sub AAAAA_2()
Dim myAreas As Areas, myAreas_2 As Areas
Dim a, i As Long, ii As Long, j As Long
Dim lr As Long, lc As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
Application.ScreenUpdating = False
a = Range("A1:A" & lr).Value
Set myAreas = ActiveSheet.Columns(4).SpecialCells(2).Areas
    For i = 1 To myAreas.Count
        Cells(Rows.Count, lc + 100).End(xlUp).Offset(2).Resize(lr).Value = a
    Next i
    Cells(1, lc + 100).Resize(2).Delete Shift:=xlUp
Set myAreas_2 = ActiveSheet.Columns(lc + 100).SpecialCells(2).Areas
    For ii = 1 To myAreas_2.Count
        With Range(myAreas_2(ii).Address)
            For j = 1 To lr
                On Error Resume Next
                    myAreas_2(ii).Cells(j).Offset(, 1).Value = Range(myAreas(ii).Address).Find(myAreas_2(ii).Cells(j)).Offset(, 1).Value
                On Error GoTo 0
            Next j
        End With
    Next ii
With Range(Cells(1, lc + 100), Cells(Cells(Rows.Count, lc + 100).End(xlUp).Row, lc + 100)).Resize(, 2)
    .Copy Cells(1, 4)
    .ClearContents
End With
Application.ScreenUpdating = True
End Sub

Sir, in revised script, it disturbs second column i.e. column F.
Data of F column is not properly aligned with E column.

 

[jolivanes]

Did you try the revised code from Post #7?
And don't quote whole posts. Just a bunch of not needed clutter.

 

[wapcoslucknow]

Yes, I tried but data in column F is shifted upward, hence I always run your script twice (by overwriting column E) to achieve desired result. If you can check and rectify it, it will be nice otherwise I almost got right coding. Thanks again.