Insert Rows After Match

mrroland

Active Member
Joined
Apr 29, 2002
Messages
294
Hi,

I have a list of Bill of materials but some of them only include the upper level. I now have a separate sheet which includes the secondary levels. My aim is to match the upper levels and then insert the rows underneath.
Not all items in Sheet 2 do match the items in Sheet 1. The numbers of rows of the secondary levels can vary.
I hope the attached example makes the above more clear.
Book1
BCDEFGH
2Sheet 1Desired Result
3TagUpper LevelQtyTagB.O.M.Qty
4Item 1856382-0011Item 1856382-0011
5Item 2828195-0011Item 1Part 11
6Item 1Part 21
7Sheet 2Item 1Part 36
8Upper LevelSecondary LevelQtyItem 1Part 41
9856382-001Part 11Item 1Part 52
10856382-001Part 21Item 2828195-001
11856382-001Part 36Item 2Part 121
12856382-001Part 41Item 2Part 133
13856382-001Part 52Item 2Part 141
14828195-001Part 121Item 2Part 153
15828195-001Part 133Item 2Part 161
16828195-001Part 141Item 2Part 171
17828195-001Part 153
18828195-001Part 161
19828195-001Part 171
Sheet1


Your help is greatly appreciated as this is a very long list and to do it manualy will take the better part of my remaining working week.

Cheers,
Roland
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
this code will sum up all data the way you stated from Blad1 and Blad2 and add it to Blad3. (This means when you run it twice, the data is entered twice too!) Past it into a module and run it.

Code:
Sub SumTotals()

    Dim Upperlevel As Range
    Dim Secondarylevel As Range
    Dim TmpRange As Range
    Dim rij As Integer
    
    For Each Upperlevel In Range("Blad1!A2:A10")
        Range(Upperlevel, Upperlevel.Offset(0, 2)).Copy
        rij = Application.WorksheetFunction.CountA(Range("Blad3!A:A")) + 1
        Set TmpRange = Range("Blad3!A" & rij)
        Range(TmpRange, TmpRange.Offset(0, 2)).PasteSpecial
        For Each Secondarylevel In Range("Blad2!A2:A10")
            If Secondarylevel.Value = Upperlevel.Offset(0, 1).Value Then
                Range(Secondarylevel.Offset(0, 1), Secondarylevel.Offset(0, 2)).Copy
                rij = Application.WorksheetFunction.CountA(Range("Blad3!A:A")) + 1
                Set TmpRange = Range("Blad3!A" & rij)
                Range(TmpRange.Offset(0, 1), TmpRange.Offset(0, 2)).PasteSpecial
                Upperlevel.Copy
                TmpRange.PasteSpecial
            End If
        Next Secondarylevel
    Next Upperlevel

End Sub
 

mrroland

Active Member
Joined
Apr 29, 2002
Messages
294
Harvey,

Thanks for the reply and your help. I tried your code and although it runs without problems the outcome is not matching my desired result. Never mind. I got the IT department to deliver me a different layout which made my life a bit easyer and have concurred the problem now within half an hour with some labour and formulas. Though I think VBA is the best solution for this. Bought a book about it and am now learning.

Thanks again for your help.

Cheers,
Roland
 

Forum statistics

Threads
1,147,623
Messages
5,742,209
Members
423,714
Latest member
ftp2jz

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