Jun 05 2019 02:00 AM
I've attached a basic example of the spreadsheet I'm trying to build (as the real version is sensitive material)
This is an order form in which one person will paste in the addresses as copied from a CSV (exported from our CRM program), as I have done in addresses tab
This person will also on the order tab state how many of each of the products each recipient will receive (for this example I have said that everyone will receive C,G,I and J, and it is most likely that it will always be the case that everyone will receive the same products)
What I'm trying to do is set a formula that will spit out the data into Output in the way I have put in the example, the correct number of lines of addresses as pulled from tab 1, with the orders as pulled from tab 2.
Does anyone have any suggestions for a workaround?
Jun 05 2019 02:32 AM - edited Jun 05 2019 02:37 AM
Hi,
If you are familiar with the Power Query (aka Get & Transform Data), you can use it to get the output that you want.
Please check out the attached file to see the solution.
Hope that helps
Jun 05 2019 03:16 AM
Hi, with help of little macro you can do this..see attached file,
Sub ListOrders()
Dim lngRow As Long
Dim lngRowMax As Long
Dim lngZ As Long
Dim lngzMax As Long
Dim lngFreeRow As Long
With Tabelle2
lngzMax = Tabelle1.Range("A" & Tabelle1.Rows.Count).End(xlUp).Row
lngRowMax = .Range("A" & .Rows.Count).End(xlUp).Row
For lngRow = 2 To lngRowMax
If .Range("B" & lngRow).Value = 1 Then
For lngZ = 2 To lngzMax
lngFreeRow = Tabelle4.Range("A" & Tabelle4.Rows.Count).End(xlUp).Row + 1
Tabelle4.Cells(lngFreeRow, 1).Value = .Range("A" & lngRow).Value
Tabelle4.Cells(lngFreeRow, 2).Value = Tabelle1.Range("A" & lngZ).Value
Tabelle4.Cells(lngFreeRow, 3).Value = Tabelle1.Range("B" & lngZ).Value
Tabelle4.Cells(lngFreeRow, 4).Value = Tabelle1.Range("C" & lngZ).Value
Tabelle4.Cells(lngFreeRow, 5).Value = Tabelle1.Range("D" & lngZ).Value
Tabelle4.Cells(lngFreeRow, 6).Value = Tabelle1.Range("E" & lngZ).Value
Tabelle4.Cells(lngFreeRow, 7).Value = Tabelle1.Range("F" & lngZ).Value
Next lngZ
End If
Next lngRow
End With
End Sub
Regards from germany
Bernd
Jun 05 2019 03:32 AM