Forum Discussion

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