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 1^st 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