Scan Spreadsheet, Convert Two Values Into One

[themurph2000]

OK, I have a very ugly example, but getting the people who designed the spreadsheet to change it is not an option.

I have a list of products that are created at several plants. The people who handed me this spreadsheet place the amount on hand in one row (on the same row as the product). On the row just below that, they may or may not have a value which is the number they already shipped. What I need to do is instantly replace both numbers with the difference between the two. Oh, and as an added bonus, I have to strip letters from the ends of the numbers of in-stock products. (I can use the left function for that, I'm sure.)

Here's an example attachment. Anybody have any ideas?sample-work.xls

[mike7952]

I dont know of any formulas that would do what your wanting. But a Macro can, This should work for you.


sample-work1.xls

[themurph2000]

It works great, except I gave the wrong data to work with. The numbers in those first rows (with a letter attached) could begin OR end with a letter. (Something like "H2400N " is a possibility.) I apologize.

[mike7952]

Ok no problem Replace the code with this. It remove any thing thats not 0-9. So It Could Be N1V101A output would be 1101

Sub abc()

 Dim ws As Worksheet
 Dim LastRow As Long, ptr As Long, iCol As Long
 
 Set ws = ActiveSheet
 LastRow = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 For ptr = 2 To LastRow Step 2
    For iCol = 2 To 5
        With ws.Cells(ptr, iCol)
            If Trim(.Value) <> vbNullString Then
                If Not IsNumeric(Trim(.Value)) Then
                   .Value = RemoveNonNumeric(.Value)
                   .Value = .Value - ws.Cells(ptr + 1, iCol)
                   ws.Cells(ptr + 1, iCol) = ""
                End If
            End If
        End With
 Next iCol, ptr
 
 Set ws = Nothing
End Sub
Public Function RemoveNonNumeric(sNumberToClean As String) As String
    Const NUMERIC_CHARS = "0123456789"
    Dim lThisChar As Long
    Dim sResult As String
    For lThisChar = 1 To Len(sNumberToClean)
        If InStr(1, NUMERIC_CHARS, Mid$(sNumberToClean, lThisChar, 1)) > 0 Then
            'Found a numeric character
            sResult = sResult + Mid$(sNumberToClean, lThisChar, 1)
        End If
    Next
    'Return the result
    RemoveNonNumeric = sResult
End Function

[themurph2000]

OK, cool. I was trying to do what you just did, which is use the function I had to remove the letters from the numbers and combine it with your step-through of subtracting the values. Of course, yours actually worked.

Thanks so much!