Search product codes, find manual entry and add to same product code for multiple entries

[Todd Myers]

Hi all, I have a data set that contains 1000 rows of similar data with various products. The 10 different products are repeated multiple times for different batches, but in some cases there is a manual entry which I need to erase, but before erasing I must take the value of the manual entry and add it to the same product name quantity. Column C is noted with ay manual entries Sample data below

Col A Col B Col C
Product 1 500
Product 2 300
Product 3 350
Product 4 400
Product 5 525
Product 6 700
Product 7 600
Product 8 650
Product 9 700
Product 10 100
Product 6 28 Manual Entry

The aim is to add he manual entry of Product 6 (28) to the Product 6 inline 6 so as to give qty of 700+ 28 = 728 and then erase the entry on line 11, only he data needs to be erased not the line deleted.

The product codes are repeat may times in the spreadsheet. Thus when a manual entry if found in Col C then can only search up 10 rows to find match.
I was hoping to d with an array formula but think it might need a vba

Thanks in advance Todd

 

[mse330]

Hi Todd,

Give the below code a try ... Let me know if it works or not & if it works, how's the speed? Is it very slow ?

Sub ManualEntryDelete()
Dim Product As String, ProdValue As Double
For x = 1 To ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    If Cells(x, 3) = "Manual Entry" Then
        Product = Cells(x, 1): ProdValue = Cells(x, 2)
        If x < 10 Then
            For y = x To 1 Step -1
                If Cells(y, 1) = Product Then
                    Cells(y, 2) = Cells(y, 2) + ProdValue
                    Range(Cells(x, 1), Cells(x, 3)).ClearContents
                End If
            Next y
        Else
            For y = 10 To 1 Step -1
                If Cells(y, 1) = Product Then
                    Cells(y, 2) = Cells(y, 2) + ProdValue
                    Range(Cells(x, 1), Cells(x, 3)).ClearContents
                End If
            Next y
        End If
    End If
Next x
End Sub