Find a unique value with a condition across 2 lists, add it to the less complete list

[jamesg]

Hi Excellent Excel Folks,

Sassora has been good enough to help me out by proving this code. I'm no programmer so I am at a loss to know how to modify this code to make it do the last little bit of magic I need.

The objective is to….
1. Compare the index numbers in the range Sheets("Portfolio Listing").Range("A5:A2005") against Sheets("Pricing Submission").Range("A5:A2005").
2. Identify any value that is unique to Sheets("Portfolio Listing").Range("A5:A2005") WHICH ALSO has the words "List" or "Pour” in the adjacent cell of Column C.
3. Copy that unique value which meets the other col C condition and place it in the 1st blank cell in the range Sheets("Pricing Submission ").Range("A5:A2005").



SASSORA’S ORIGINAL CODE (slightly modified)


Option Explicit


Sub UpdateSubmissionAddNewSKU()
Dim cellrange As Range
Dim lastrowSh2 As Long


lastrowSh2 = Sheets("Pricing Submission").Range("A" & Rows.Count).End(xlUp).Row


For Each cellrange In Sheets("Portfolio Listing").Range("A1:A500").SpecialCells(2)
If IsError(Application.Match(cellrange, Sheets("Pricing Submission").Range("A1:A500"), 0)) Then
lastrowSh2 = lastrowSh2 + 1
Sheets("Pricing Submission").Range("A" & lastrowSh2) = cellrange.Value
End If
Next cellrange
End Sub


Many thanks for any help provided.

James G

[Bob Phillips]

Sub UpdateSubmissionAddNewSKU()
Dim cellrange As Range
Dim lastrowSh2 As Long


lastrowSh2 = Sheets("Pricing Submission").Range("A" & Rows.Count).End(xlUp).Row


For Each cellrange In Sheets("Portfolio Listing").Range("A1:A500").SpecialCells(2)

If IsError(Application.Match(cellrange, Sheets("Pricing Submission").Range("A1:A500"), 0)) Then

If cellrange.Offset(0, 2).Value = "List" Or cellrange.Offset(0, 2).Value = "Pour" Then

lastrowSh2 = lastrowSh2 + 1
Sheets("Pricing Submission").Range("A" & lastrowSh2) = cellrange.Value
End If
End If
Next cellrange
End Sub