Find missing member from list and add a blank row there

wapcoslucknow

New Member
Joined
Oct 17, 2020
Messages
13
Office Version
  1. 2007
Platform
  1. Windows
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 length0.01
HT Poles4
LT Line Length0.56
LT Poles10
DTR 25 KVA2
BPL118

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 length0.01
HT Poles4
LT Line Length0.56
LT Poles10
DTR 10 KVA
DTR 16 KVA
DTR 25 KVA2
DTR 63 KVA
BPL118

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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,146
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

New Member
Joined
Oct 17, 2020
Messages
13
Office Version
  1. 2007
Platform
  1. Windows
Query.xlsx
ABCDEFGHIJK
1Master DataExample 1 - Slave data (earlier)Example 2 - Slave data (earlier)Example 3 - Slave data (earlier)
2HT line lengthHT line length0.01HT Poles3HT line length1.23
3HT PolesHT Poles4LT Line Length0.26DTR 63 KVA1
4LT Line LengthLT Line Length0.56LT Poles40
5LT PolesLT Poles10DTR 16 KVA1
6DTR 10 KVADTR 25 KVA2DTR 25 KVA3
7DTR 16 KVABPL118BPL32
8DTR 25 KVA
9DTR 63 KVA
10BPL
11Slave data (after)Slave data (after)Slave data (after)
12Master 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 length0.01HT line lengthHT line length1.23
13HT Poles4HT Poles3HT Poles
14LT Line Length0.56LT Line Length0.26LT Line Length
15LT Poles10LT Poles40LT Poles
16DTR 10 KVADTR 10 KVADTR 10 KVA
17DTR 16 KVADTR 16 KVA1DTR 16 KVA
18DTR 25 KVA2DTR 25 KVA3DTR 25 KVA
19DTR 63 KVADTR 63 KVADTR 63 KVA1
20BPL118BPL32BPL
21
22
23Here, 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

Well-known Member
Joined
Apr 11, 2012
Messages
10,146
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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.

Code:
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

New Member
Joined
Oct 17, 2020
Messages
13
Office Version
  1. 2007
Platform
  1. Windows
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.

Code:
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Change this
Code:
Range("D1:F1").EntireColumn.Delete Shift:=xlToLeft
to this
Code:
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
Code:
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

New Member
Joined
Oct 17, 2020
Messages
13
Office Version
  1. 2007
Platform
  1. Windows
Change this
Code:
Range("D1:F1").EntireColumn.Delete Shift:=xlToLeft
to this
Code:
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
Code:
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Did you try the revised code from Post #7?
And don't quote whole posts. Just a bunch of not needed clutter.
 

wapcoslucknow

New Member
Joined
Oct 17, 2020
Messages
13
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,362
Members
414,306
Latest member
Dennis_vdw

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
Top