Formula or Macro Highlight/Look Up SKUS that are Out of Stock from all Inventory Warehouse Locations

[abarratt]

I have a spreadsheet that has SKUs listed in first column and then the warehouse location and then a column for inventory (qty on hand, qty allocated, qty on order, qty backordered, qty future, qty available). Some skus are spread out among multiple warehouse locations so those SKUs are repeated. I need a formula or macros that will find and return all skus that out of stock in all columns and in all locations. Is there a way to do that? There are about 15,000+ Skus.
[TABLE="width: 793"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Warehouse[/TD]
[TD]Prefix[/TD]
[TD]Qty On Hand[/TD]
[TD]Qty Allocated[/TD]
[TD]Qty BO[/TD]
[TD]Qty OO[/TD]
[TD]Qty Future[/TD]
[TD]Qty Available[/TD]
[/TR]
[TR]
[TD]1015-0021-TW[/TD]
[TD]East Coast 3PL[/TD]
[TD]3PE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]1015-0021-TW[/TD]
[TD]Lakawana1[/TD]
[TD]LK1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]1015-0021-TW[/TD]
[TD]3 PLUS LOGISTICS[/TD]
[TD]3PL[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Perpa]

I have a spreadsheet that has SKUs listed in first column and then the warehouse location and then a column for inventory (qty on hand, qty allocated, qty on order, qty backordered, qty future, qty available). Some skus are spread out among multiple warehouse locations so those SKUs are repeated. I need a formula or macros that will find and return all skus that out of stock in all columns and in all locations. Is there a way to do that? There are about 15,000+ Skus.
[TABLE="width: 793"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Warehouse[/TD]
[TD]Prefix[/TD]
[TD]Qty On Hand[/TD]
[TD]Qty Allocated[/TD]
[TD]Qty BO[/TD]
[TD]Qty OO[/TD]
[TD]Qty Future[/TD]
[TD]Qty Available[/TD]
[/TR]
[TR]
[TD]1015-0021-TW[/TD]
[TD]East Coast 3PL[/TD]
[TD]3PE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]1015-0021-TW[/TD]
[TD]Lakawana1[/TD]
[TD]LK1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]1015-0021-TW[/TD]
[TD]3 PLUS LOGISTICS[/TD]
[TD]3PL[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

abarratt,
Welcome to the Forum.
This code will make a list on Sheet2 of all SKUs that have '0' or "" (blank) on Sheet1 column D for every warehouse that has that SKU.
Each SKU will only be listed once. It assumes your data is on Sheet1 with the SKU in column A, and the 'Qty On Hand' in column D, and the headers are in row 1.
You should try this on a copy of your workbook to avoid losing any data. It should be copied into a standard code module using 'Alt +F11' to access the VB Editor. After closing the Editor you should save the workbook as a 'macro-enabled' file. To run the macro press 'Alt+F8', then select 'ListOutOf Stock', then 'Run'. When it finishes, the macro posts a message box telling you it has finished. You will be looking at the new list on Sheet2.
Perpa

Sub ListOutOfStock()
Dim LastRow, LastRow2 As Long
Dim rw, n As Long
Dim cnt, cnt1 As Integer
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For rw = 2 To LastRow
cnt = 0
cnt1 = 0
    For n = rw To LastRow
If n = rw Then GoTo SkipThisRow    'Don't count this row
If Cells(n, 1) = Cells(rw, 1) Then
     cnt1 = cnt1 + 1      'cnt1 keeps total of same sku found
        If Cells(rw, 4) = "" Or Cells(rw, 4) = 0 And Cells(n, 4) = "" Or Cells(n, 4) = 0 Then
               cnt = cnt + 1    'Keeps total of each sku with zero stock
        End If
End If
SkipThisRow:
    Next n
   LastRow2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
   If cnt = cnt1 And cnt <> 0 Then Sheets("Sheet2").Cells(LastRow2, 1) = Sheets("Sheet1").Cells(rw, "A")
    Next rw
    
Sheets("Sheet2").Activate
'The  macro called below will eliminate any duplicate skus on Sheet2
Call OnlyOneSKU

Application.ScreenUpdating = True
MsgBox "Out of Stock Listing is Complete"
End Sub


Sub OnlyOneSKU()

    Dim Rng As Range
    Dim LastRow As Long
    Dim i As Long
    Dim sDupColumn As String

    sDupColumn = "A"
 
    Application.ScreenUpdating = False
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Set Rng = Range("A1:A" & LastRow)

'The following 'With' statement will sort the SKUs - I commented it out, but it can be used by removing the single quotes - Try running the rest of the code first with it commented out.
    'With Rng
       '.Sort key1:=Range(sDupColumn & 1), order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
            'Orientation:=xlTopToBottom
    'End With

    For i = LastRow To 2 Step -1
        If WorksheetFunction.CountIf(Range(Cells(2, sDupColumn), Cells(i, sDupColumn)), Cells(i, sDupColumn)) > 1 Then
            Rows(i).Delete
        End If
    Next i
    Application.ScreenUpdating = True

End Sub

 

[abarratt]

This worked perfectly! Thank you!!!!

AB

 

[Perpa]

Glad it worked for you. You are welcome.
Perpa