Automatic duplicate cells by X

[Geo-3llen_]

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?

[#2]

@Geo-3llen_ 

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

[#3]

@Geo-3llen_

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

 www.vba-Tanker.com - a database full of excel-macros

[#4]

right now there is nothing to relate the orders to the person who ordered them in the Address table. PS: you probably will see more action on your question if you ask in the "answers" forum. This forum is intended for more technical questions. Answers in intended for users asking 'how to" questions about applications like this one. Go to this home page:https://answers.microsoft.com/en-us/ Click on the "Office" category icon Ciick on "Ask a question" at the top of the page.. Suggestion, although copy/paste of CSV will get the job done, it is better to open the CSV and parse it. It is even better yet to use the "PowerQuery" / "Get & Transform" feature to import the CSV. PowerQuery will record all of your actions done to "clean up" the incoming data. These acttions can be later repeated automatically on another CSV you import.