Forum Discussion

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