Forum Discussion

Geo-3llen_'s avatar
Geo-3llen_
Copper Contributor
Jun 05, 2019

Automatic duplicate cells by X

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?

excel
Formulas and Functions
Macros and VBA

3 Replies

  • ron S.'s avatar
    ron S.
    Brass Contributor
    Jun 05, 2019
    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.
  • Berndvbatanker's avatar
    Berndvbatanker
    Iron Contributor
    Jun 05, 2019

    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

     http://www.vba-Tanker.com