SOLVED

Adding a unique customer # after every 5th row

Copper Contributor

Hi All,

I've a list of 200 customers which I'd like to insert after every 5 rows. Entry beginning with ALL1221, ALL1225 is a unique customer # and the entries starting with T are item codes, the adjacent columns include quantity and cost per item, respectively. The item codes stay the same for all 200 customers.

I'd appreciate if someone can suggest an easier way to do this rather than copying and inserting customer #s in every 5th row. Thanks.

 

Gurpreet20_0-1712000305265.png

 

5 Replies
best response confirmed by GrahmSchneider13 (Microsoft)
Solution

@Gurpreet20 

Sub items()

Dim i, j As Long

Range("A:C").Clear

j = 1
For i = 1 To 1200

If i Mod 6 = 1 Then
Cells(i, 1).Value = Cells(j, 11).Value
j = j + 1
Else

Select Case i Mod 6

Case Is = 2
Cells(i, 1).Value = "T40015"
Case Is = 3
Cells(i, 1).Value = "T40016"
Case Is = 4
Cells(i, 1).Value = "T40017"
Case Is = 5
Cells(i, 1).Value = "T40018"
Case Is = 0
Cells(i, 1).Value = "T40019"


End Select

Cells(i, 2).Value = 6
Cells(i, 3).Value = 5


End If

Next i

End Sub

In the attached file you can click the button in cell E2 to run the macro which enters the unique customer id's from range K1:K200 in column A.

@OliverScheurichThank you so very much. This is so helpful :stareyes: It would usually take me over an hour to do this whereas now I can do this in seconds. Amazing!!

I wanted to ask if each item has different cost and quantity, how would I go about updating in the code?

 

Also, is it possible to make the following changes to the code/file when I run macro?

  • Insert a new column to the left where word "HEADER" is assigned to each customer # and word "LINE" is assigned to each item #. This will be column A
  • Move all customer #s to column C just above the first quantity for each item
  •  Add word "NEXT" for each customer # in column B which would be adjacent to customer # and the header column A.

 

The website is not letting me attach the file so, I've copied the screenshot of changes (highlighted in yellow) that I'd like to see in the file. Thanks again :smile:

Gurpreet20_0-1712068525580.png

 

 

 

 

@Gurpreet20 

Sub items()

Dim i, j, k As Long

Range("A:D").Clear

j = 1
k = 0
For i = 1 To 1200

If i Mod 6 = 1 Then

k = k + 1

Cells(i, 1).Value = "HEADER"
Cells(i, 2).Value = "NEXT"
Cells(i, 3).Value = Cells(j, 11).Value
j = j + 1

Else

Cells(i, 1).Value = "LINE"

Select Case i Mod 6

Case Is = 2
Cells(i, 2).Value = "T40015"
Cells(i, 3).Value = Cells(k, 12).Value
Cells(i, 4).Value = Cells(k, 13).Value

Case Is = 3
Cells(i, 2).Value = "T40016"
Cells(i, 3).Value = Cells(k, 14).Value
Cells(i, 4).Value = Cells(k, 15).Value

Case Is = 4
Cells(i, 2).Value = "T40017"
Cells(i, 3).Value = Cells(k, 16).Value
Cells(i, 4).Value = Cells(k, 17).Value

Case Is = 5
Cells(i, 2).Value = "T40018"
Cells(i, 3).Value = Cells(k, 18).Value
Cells(i, 4).Value = Cells(k, 19).Value

Case Is = 0
Cells(i, 2).Value = "T40019"
Cells(i, 3).Value = Cells(k, 20).Value
Cells(i, 4).Value = Cells(k, 21).Value

End Select

End If


Next i

End Sub

You are welcome. I'm glad you found my suggestion helpful.

 

You can run the adjusted macro in the attached file by clicking the button in cell F2. In this example the different cost and quantity for an item are in the adjacent columns to the customer # in column K. For example for the customer in K1 the cost and quantity for item T40015 are in cells L1 and M1 and the cost and quantity for item T40016 are in cells N1 and O1 and so on.

@Gurpreet20 

As described this far, an array formula would do the job

= LET(
    n,     ROWS(customerList),
    items, TRANSPOSE(CHOOSECOLS(itemList, SEQUENCE(1,n,1,0))),
    TOCOL(HSTACK(customerList, items))
  )

 image.png

Once you want a mix of formula output and input cells within a region then it becomes a non-starter.

@OliverScheurich  wow...thank you so much. It works like a charm :smile:

 

 

1 best response

Accepted Solutions
best response confirmed by GrahmSchneider13 (Microsoft)
Solution

@Gurpreet20 

Sub items()

Dim i, j As Long

Range("A:C").Clear

j = 1
For i = 1 To 1200

If i Mod 6 = 1 Then
Cells(i, 1).Value = Cells(j, 11).Value
j = j + 1
Else

Select Case i Mod 6

Case Is = 2
Cells(i, 1).Value = "T40015"
Case Is = 3
Cells(i, 1).Value = "T40016"
Case Is = 4
Cells(i, 1).Value = "T40017"
Case Is = 5
Cells(i, 1).Value = "T40018"
Case Is = 0
Cells(i, 1).Value = "T40019"


End Select

Cells(i, 2).Value = 6
Cells(i, 3).Value = 5


End If

Next i

End Sub

In the attached file you can click the button in cell E2 to run the macro which enters the unique customer id's from range K1:K200 in column A.

View solution in original post