Forum Discussion

Gable18's avatar
Gable18
Copper Contributor
Dec 14, 2020

VBA Solution - Add rows and replace/add data

Hi Excel Community,

 

I would need help, I have my daily export of our daily sales data in .xlsx format.  I have my sales listed on two levels - sales of product and bundle level. I would like to have it on one level only , on product level. In order to do that I would like to change data of bundles to the product level. I already have database which products are part of each bundle and what is the quantity of single products (Bundles_Database). For example Bundles are named BL-xxxx, firts bundle is BL-0001 and it consist of 5 units of Product with ID 0536, 1 unit of Product with ID 0490 and 1 unit of Product with ID 1199. 

 

I was thinking if it is possible to create VBA code which would find bundle id and replace (add columns) it with data of the products which are part of this bundle. Data like invoice id, date of invoice, partner would be copied from the bundle level entry. So for example for every entry in SalesData of bundle 1 excel would need to create three new rows with data for product id 0536, 0490 and 1199 with correct quantities.

 

Since my data consist of sensitive data, I simplified my exports and database and attached them bellow.

 

Thank you for your help, I hope it is understandable 🙂

 

excel
Formulas and Functions
Macros and VBA

3 Replies

  • MindreVetande's avatar
    MindreVetande
    Iron Contributor
    Dec 15, 2020

    Gable18 

     

     

    Always use an excel function if it exists.  This example uses a formula like this in G2

    =FILTER(Bundles_Database.xlsx!tbl_bundle[#Data],
(Bundles_Database.xlsx!tbl_bundle[ID]=$D2)*
(Bundles_Database.xlsx!tbl_bundle[Bundle name]=$D2)
,"""")

    Adjust the formula until it does what you want

     

    This is the VBA-code

    Sub example()

Dim shTargetSheet As Worksheet
Dim rTarget As Range

' Set the name of the target sheet (i use Sheet1 since my language settings dont like the name you used)
Set shTargetSheet = Workbooks("SalesData.xlsx").Worksheets("Sheet1")

' Set target to the first column to the right of your Quantity-column (G)
Set rTarget = shTargetSheet.Range("F2", Range("F2").End(xlDown)).Offset(0, 1)

'test if you want
rTarget.Select

' clear any data in those cells (well, acvualy everything to the right. You might want to use a limited offset instead)
Range(rTarget, rTarget.End(xlToRight)).Clear

'Fill target with filter formula
       
 rTarget.Formula2R1C1 = _
"=FILTER(Bundles_Database.xlsx!tbl_bundle[#Data],(Bundles_Database.xlsx!tbl_bundle[ID]=RC4)*(Bundles_Database.xlsx!tbl_bundle[Bundle name]=RC5),"""")"
    
    
'Kill the formula by Filling the cells with the value in the cells
    Range(rTarget, rTarget.End(xlToRight)).Value = _
    Range(rTarget, rTarget.End(xlToRight)).Value

'Fill the headers in some way
shTargetSheet.Range("G1:T1").Value = _
Workbooks("Bundles_Database.xlsx").Worksheets("BundleContent").Range("A1:N1").Value

End Sub

     

    • Gable18's avatar
      Gable18
      Copper Contributor
      Dec 16, 2020

      MindreVetande 

       

      thank you very much for your reply, I already tried to do something similar what you proposed, but this is just copy paste of the data from database to main data.

       

      I would like to achieve this:

      Now:

      Final output: 

      I did the example only for first order, Order ID-42. So I need to replace for Bundle 1 from one row to three rows.

       

      Can you help?

       

      Thank you 🙂

       

       

      • MindreVetande's avatar
        MindreVetande
        Iron Contributor
        Dec 16, 2020

        Gable18 

        Hi. the correct way is probably a smart use of Un-pivot in power query. But that is out of my comfort zone.

         

        Use the filter-function and insert a new row below. Copy the current row and remove the first 2 columns of new (filter)data. Continue until you are out of data. Then go down one row and Insert a new Filter formula. Test. Use a lot of [F8] to see what hapends ...

         

        Sub example()

Dim shTargetSheet As Worksheet
Dim rTarget As Range


Set shTargetSheet = ThisWorkbook.Worksheets("Sheet1")
Set rTarget = shTargetSheet.Range("G2")
      
'loop until there i no more invocce-id 
Do While rTarget.Offset(1, -4) <> ""

'Fill target with one-row filter formula
rTarget.Formula2R1C1 = _
"=FILTER(Bundles_Database.xlsx!tbl_bundle[[Product1-ID]:[Product6-Quantity]],(Bundles_Database.xlsx!tbl_bundle[ID]=RC4)*(Bundles_Database.xlsx!tbl_bundle[Bundle name]=RC5),"""")"
   'Kill the formula by Filling the cells with the value in the cells
rTarget.Resize(1, 12).Value = rTarget.Resize(1, 12).Value

        Do While rTarget.Offset(0, 2) <> 0
            rTarget.Offset(1).EntireRow.Insert
            rTarget.Offset(1).EntireRow.Value = rTarget.EntireRow.Value
            rTarget.Offset(1).Resize(1, 2).Delete
            Set rTarget = rTarget.Offset(1)
        Loop

Set rTarget = rTarget.Offset(1, 0)
Loop

shTargetSheet.Columns("I:R").ClearContents
End Sub

         

Resources