Adding a new row based on comparing two cells

[CamOne]

Hello,

Nice to meet everyone. Thank you in advance for taking a look at my post.

I have a large number of products in spreadsheets that are cataloged using item numbers. The item numbers are made of 3 parts, the design that will be printed on an item, the product type it will be printed on, and the size. Here are two example product numbers: PPC100-PFFH-1 and PPC100-PF-1. They both have "PPC100" because they both use the same design but the second set of characters is different because they will be printed on two separate product types (t-shirt, hoodie, etc.) The last part of the product number "-1" refers to the size of the garment (small, medium, large)

What I want to do:

1. I want to automatically add a new row every time there is a new product
2. In the item number cell of the new row, I want to add the item number minus the size for the item numbers below it

I have attached a couple of screenshots and have used XL2BB to copy the data from Excel. If anyone needs clarification, please let me know. Thanks in advance for looking at my post!

item list tester.xlsx
	A	B	C	D
1	Internal ID	Item Number	UPC Code	Description
2	1	PPC100-PFFH-1	8799523897	property name/design name-ADULT PULL-OVER HOODIE-CHARCOAL-SM
3	2	PPC100-PFFH-2	8799523898	property name/design name-ADULT PULL-OVER HOODIE-CHARCOAL-MD
4	3	PPC100-PFFH-3	8799523899	property name/design name-ADULT PULL-OVER HOODIE-CHARCOAL-LG
5	4	PPC100-PFFH-4	8799523900	property name/design name-ADULT PULL-OVER HOODIE-CHARCOAL-XL
6	5	PPC100-PFFH-5	8799523901	property name/design name-ADULT PULL-OVER HOODIE-CHARCOAL-2X
7	6	PPC100-PFFH-6	8799523902	property name/design name-ADULT PULL-OVER HOODIE-CHARCOAL-3X
8	7	PPC100-PF-1	8799523903	property name/design name - L/S ADULT 18/1 - CHARCOAL - SM
9	8	PPC100-PF-2	8799523904	property name/design name - L/S ADULT 18/1 - CHARCOAL - MD
10	9	PPC100-PF-3	8799523905	property name/design name - L/S ADULT 18/1 - CHARCOAL - LG
11	10	PPC100-PF-4	8799523906	property name/design name - L/S ADULT 18/1 - CHARCOAL - XL
12	11	PPC100-PF-5	8799523907	property name/design name - L/S ADULT 18/1 - CHARCOAL - 2X
13	12	PPC100-PF-6	8799523908	property name/design name- L/S ADULT 18/1 -CHARCOAL-3X
14	13	PPC100-SF-1	8799523909	property name/design name - ADULT CREWNECK SWEATSHIRT - CHARCOAL - SM
15	14	PPC100-SF-2	8799523910	property name/design name - ADULT CREWNECK SWEATSHIRT - CHARCOAL - MD
16	15	PPC100-SF-3	8799523911	property name/design name - ADULT CREWNECK SWEATSHIRT - CHARCOAL - LG
17	16	PPC100-SF-4	8799523912	property name/design name - ADULT CREWNECK SWEATSHIRT - CHARCOAL - XL
18	17	PPC100-SF-5	8799523913	property name/design name - ADULT CREWNECK SWEATSHIRT - CHARCOAL - 2X
19	18	PPC100-SF-6	8799523914	property name/design name - ADULT CREWNECK SWEATSHIRT - CHARCOAL - 3X
20	19	PPC100-TF-1	8799523915	property name/design name - S/S ADULT 18/1 - CHARCOAL - SM
21	20	PPC100-TF-2	8799523916	property name/design name - S/S ADULT 18/1 - CHARCOAL - MD
22	21	PPC100-TF-3	8799523917	property name/design name - S/S ADULT 18/1 - CHARCOAL - LG
23	22	PPC100-TF-4	8799523918	property name/design name - S/S ADULT 18/1 - CHARCOAL - XL
24	23	PPC100-TF-5	8799523919	property name/design name - S/S ADULT 18/1 - CHARCOAL - 2X
25	24	PPC100-TF-6	8799523920	property name/design name - S/S ADULT 18/1 - CHARCOAL - 3X
26	25	PPC100-TF-7	8799523921	property name/design name-S/S ADULT 18/1 - CHARCOAL - 4X
27	26	PPC100-TF-8	8799523922	property name/design name-S/S ADULT 18/1 - CHARCOAL - 5X
28	27	PPC100-TFF-4	8799523923	property name/design name-S/S ADULT TALL-CHARCOAL-XL
29	28	PPC100-TFF-5	8799523924	property name/design name-S/S ADULT TALL-CHARCOAL-2X
30	29	PPC100-TFF-6	8799523925	property name/design name-S/S ADULT TALL-CHARCOAL-3X
31	30	PPC100-VF-1	8799523926	property name/design name - S/S ADULT V-NECK - CHARCOAL - SM
32	31	PPC100-VF-2	8799523927	property name/design name - S/S ADULT V-NECK - CHARCOAL - MD
33	32	PPC100-VF-3	8799523928	property name/design name - S/S ADULT V-NECK - CHARCOAL - LG
34	33	PPC100-VF-4	8799523929	property name/design name - S/S ADULT V-NECK - CHARCOAL - XL
35	34	PPC100-VF-5	8799523930	property name/design name - S/S ADULT V-NECK - CHARCOAL - 2X
36	35	SR138-ST-1	8799523931	property name/design name-S/S JUVENILE 18/1-ATHLETIC HEATHER-SM(4)
37	36	SR138-ST-2	8799523932	property name/design name-S/S JUVENILE 18/1-ATHLETIC HEATHER-MD(5/6)
38	37	SR138-ST-3	8799523933	property name/design name-S/S JUVENILE 18/1-ATHLETIC HEATHER-LG(7)
39	38	SR138-TF-1	8799523934	property name/design name-S/S TODDLER TEE-ATHLETIC HEATHER-SM(2T)
40	39	SR138-TF-2	8799523935	property name/design name-S/S TODDLER TEE-ATHLETIC HEATHER-MD(3T)
41	40	SR138-TF-3	8799523936	property name/design name-S/S TODDLER TEE-ATHLETIC HEATHER-LG(4T)
42	41	SR138-SFFH-1	8799523937	property name/design name-YOUTH PULL-OVER HOODIE - HEATHER - SM
43	42	SR138-SFFH-2	8799523938	property name/design name-YOUTH PULL-OVER HOODIE - HEATHER - MD
44	43	SR138-SFFH-3	8799523939	property name/design name-YOUTH PULL-OVER HOODIE - HEATHER - LG
45	44	SR138-SFFH-4	8799523940	property name/design name-YOUTH PULL-OVER HOODIE - HEATHER - XL
46	45	SR138-FL-1	8799523941	property name/design name-YOUTH LONG SLEEVE TEE-ATHLETIC HEATHER-SM
47	46	SR138-FL-2	8799523942	property name/design name-YOUTH LONG SLEEVE TEE-ATHLETIC HEATHER-MD
48	47	SR138-FL-3	8799523943	property name/design name-YOUTH LONG SLEEVE TEE-ATHLETIC HEATHER-LG
49	48	SR138-FL-4	8799523944	property name/design name-YOUTH LONG SLEEVE TEE-ATHLETIC HEATHER-XL
50	49	SR138-TF-1	8799523945	property name/design name-S/S YOUTH 18/1-ATHLETIC HEATHER-SM
51	50	SR138-TF-2	8799523946	property name/design name-S/S YOUTH 18/1-ATHLETIC HEATHER-MD
52	51	SR138-TF-3	8799523947	property name/design name-S/S YOUTH 18/1-ATHLETIC HEATHER-LG
53	52	SR138-TF-4	8799523948	property name/design name-S/S YOUTH 18/1-ATHLETIC HEATHER-XL
DTG All Styles A - B

 

[tj4242]

The formatting of the first row is a little wonky, but that can be cleaned up manually. I found the FindN function on a different website. It finds the location of the second "-" in the Item Number so the size can be removed.

Sub newproduct()
    Dim r As Double                                     'current row
    Dim p As Integer                                    'position of second "-"
    Dim pnum As String                                  'product number without size
    
    Dim curritem As String                              'current item number without the size
    Dim nextitem As String                              'next item number without the size
    
    'the first row is a special case
    p = FindN("-", Cells(2, 2), 2)                      'find location of 2nd dash in the next row
    nextitem = Left(Cells(2, 2), p - 1)                 'set nextitem to all the chars before the 2nd dash
    Range("a2").EntireRow.Insert                        'Insert the row
    Cells(2, 2) = nextitem                              'put the item number in the cell
    Range(Cells(2, 2), Cells(2, 2)).Interior.Color = vbGreen    'favorite shade of green

    r = 2                                               'start row 2
    While Cells(r + 1, 2) <> ""                         'while there is more data in the next row
        
        p = FindN("-", Cells(r, 2), 2)                  'find location of the 2nd dash
        If p Then                                       'if there was a 2nd dash
            curritem = Left(Cells(r, 2), p - 1)         'set curritem to all of the chars before the 2nd dash
        
            p = FindN("-", Cells(r + 1, 2), 2)          'find location of 2nd dash in the next row
            nextitem = Left(Cells(r + 1, 2), p - 1)     'set nextitem to all the chars before the 2nd dash
        
            If curritem <> nextitem Then                'are the item numbers different?
                Range("a" & r + 1).EntireRow.Insert     'Insert the row
                Cells(r + 1, 2) = nextitem              'put the item number in the cell
                Range(Cells(r + 1, 2), Cells(r + 1, 2)).Interior.Color = vbGreen    'favorite shade of green
            End If
        End If

        r = r + 1                                       'move to the next row
    Wend
    
End Sub

Function FindN(sFindWhat As String, _
sInputString As String, N As Integer) As Integer
    Dim J As Integer
    Application.Volatile
    FindN = 0
    For J = 1 To N
        FindN = InStr(FindN + 1, sInputString, sFindWhat)
        If FindN = 0 Then Exit For
    Next
End Function