PDA

View Full Version : [SOLVED] Filtering SKU List to Show Items with Multiple Descriptions Only



jaydee
10-26-2017, 01:20 PM
All-

Attached is a list of 25,000 skus and descriptions. Is there a way to filter this list to show just the skus with multiple descriptions?

For example, row 4 sku 3448119346 has description ‘PLAS B349ADJ 3G ADJUSTABOX’ and ‘PLASTIC B349ADJ ADJUSTABOX’.
Rows 8-9, 12-13, have only one description which I want to exclude.

Background: Our subsidiary has 5 locations, and each location has the same sku, but they put in different descriptions causing discrepancies. We want the managers to review the skus with multiple descriptions, and pick only one for consistency.

I placed a box around the items I wanted to see, for the first dozen items. I took this data from a pivot table, so any time there is a blank in column A, it means it is related to the sku above it.

Thank you,

greyangel
10-26-2017, 02:09 PM
Here try this one out. Please let me know if this works for you. This may not be the fastest code but it should work for you purposes. Below is the code I used.


Sub fillblanks()
Application.ScreenUpdating = False
'Fills in all blanks
Range("A2").Select
SKU = ""
Do Until ActiveCell.Offset(, 1).Value = ""
If ActiveCell.Value = "" Then
ActiveCell.Value = SKU
Else
SKU = ActiveCell.Value
End If
ActiveCell.Offset(1).Select
Loop
'sorts data
Range("A2").Select
SKU = ""
Description = ""
x = 2
Do Until ActiveCell.Offset(, 1).Value = ""
If ActiveCell.Value = SKU And ActiveCell.Offset(, 1).Value <> Description Then
Sheets("Duplicates").Cells(x, 1).Value = SKU
x = x + 1
ElseIf ActiveCell.Value <> SKU Then
SKU = ActiveCell.Value
Description = ActiveCell.Offset(, 1).Value
End If
ActiveCell.Offset(1).Select
Loop
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("c2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Duplicates!C[-2],1,FALSE)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C" & Lastrow)
Application.ScreenUpdating = True
MsgBox "Filter Column ""C"" on all SKU's that don't have an #N/A. This will show you which parts have different descriptions.", exclamation, "Created by Robbie DePalma."
End Sub

SamT
10-26-2017, 05:25 PM
Bare Code, no Declarations

LastRow = Cells(Cells.Count, "B").End(xlUp).Row

Set WorkingCell = Cells(Rows.Count, "A").end(xlUp)
If WorkingCell.Row = LastRow Then WorkingCell.EntireRow.Visible = False
Set WorkingCell = WorkingCell.Offset(-1)

Do While WorkingCell.Row >= 1
If WorkingCell.Row = 1 And WorkingCel.Offset(1) <> ""Then
WorkingCell.EntireRow.Visible = False
Exit Do
End If

Do While WorkingCell = ""
Set WorkingCell = WorkingCell.Offset(-1)
Loop

If WorkingCel.Offset(1) = "" Then
Set WorkingCell = WorkingCell.Offset(-)
Else
WorkingCell.EntireRow.Visible = False
Set WorkingCell = WorkingCell.Offset(-)
End if
Loop

jaydee
10-27-2017, 01:43 PM
Thanks guys! Robbie, it works great. I dumped in my data, ran the macro button, and filtered the list as you mentioned. Then I dumped it back into a pivot table for formatting and sent it off.
It saved me a ton of time, I really appreciate your help.

SamT - I also tried your code, but I got a syntax error related to the line: Set WorkingCell = WorkingCell.Offset(-)
I do appreciate your help in looking at the file, even though I couldn't get it to work.

Thank you again everyone, love this community!
Have a great weekend.

SamT
10-27-2017, 01:46 PM
B@d Keybored... Both (-) should be (-1)

p45cal
10-27-2017, 05:45 PM
Bit late.
In the attached, button on sheet which:

Copies that sheet and works on the copy
fills in the blanks in column A
adds CountIf formulae into columns C and D (this takes 4 to 5 minutes on my machine)
converts those formula to plain values
uses the 4 columns as source data for two new sheets with pivot tables:
Pivot 1 shows SKUs with more than 1 Description
Pivot 2 shows Descriptions with more than one SKU (if it's any use!)
Converts both pivots to plain data.


It doesn't delete the copy of the original sheet.