Unique Cell Values from different worksheets

[darkmidget]

Hi,

Here is what I am hoping to accomplish...

One worksheet has a list of product numbers that are currently in inventory. The 2nd worksheet has a list of available product numbers from the vendor.(This gets updated daily)

On the third worksheet I would like to compare the product numbers from inventory to the product numbers available from the vendor. If the vendor has a product number not listed in my inventory then I would like it to show up on a list in the 3rd worksheet.

Example...

Worksheet 1 (Inventory) Column A
BLK-ADA-16
JACK25-35
30A3-07200
68TA-C501-2MF
5511A001-001-LF

Worksheet 2 (Vendor) Column A
10H1-38201
30A3-07200
11W3-08315
ADP2535S-BULK
800A-RS232
30A3-01400
68TA-C501-2MF
ATX-SATA-ADAPTER-4


Worksheet 3 should list in Column A (No particular order is needed)
10H1-38201
11W3-08315
ADP2535S-BULK
800A-RS232
30A3-01400

Note: Worksheet 3 should have All new items from vendor that are not listed in the inventory worksheet.

I have access to both Excel 07 or 03 (not sure if it makes a difference) for what I am hoping to try.

Thanks in advance for any direction!
- Matt

 

[robwong1]

It may be easier to see this all in a single tab. In column A, as you have currently, show your Inventory Available Product #s. In column B, show your Vendor Available Product #s. In column C, do a match function:

=MATCH(B1,$A$1:$A$36,0)

The formula shown, above, matches what's in B1 to the entire column of data in Column A. Anything that's not a match shows up as an error. Anything that is a match gives a row #

You can be a little fancier by using this formula:

=NOT(ISERROR(MATCH(B1,$A$1:$A$36,0)))

Now, anytime there's a match, you get a "TRUE" and when there isn't a match, you get a "FALSE".

To get a list of just the FALSE responses, either Data Filter, or my preference is to run a Pivot and just filter on the FALSE responses.

 

[Domenic]

Place the following macro in a regular module, and run 'ListUniqueVals'...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] ListUniqueVals()

    [color=darkblue]Dim[/color] MyArray() [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] WS1 [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] WS2 [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] WS3 [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] SearchRng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] FoundCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] LR1 [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LR2 [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] WS1 = Worksheets("Sheet1")
    [color=darkblue]Set[/color] WS2 = Worksheets("Sheet2")
    [color=darkblue]Set[/color] WS3 = Worksheets("Sheet3")
    
    [color=darkblue]With[/color] WS1
        LR1 = .Cells(.Rows.Count, "A").End(xlUp).Row
        [color=darkblue]Set[/color] SearchRng = .Range("A1:A" & LR1)
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    Cnt = 0
    [color=darkblue]With[/color] WS2
        LR2 = .Cells(.Rows.Count, "A").End(xlUp).Row
        [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] LR2
            [color=darkblue]Set[/color] FoundCell = SearchRng.Find(what:=.Cells(i, "A").Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
            [color=darkblue]If[/color] FoundCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                Cnt = Cnt + 1
                [color=darkblue]ReDim[/color] [color=darkblue]Preserve[/color] MyArray(1 [color=darkblue]To[/color] Cnt)
                MyArray(Cnt) = .Cells(i, "A").Value
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] i
    [color=darkblue]End[/color] [color=darkblue]With[/color]
            
    WS3.Range("A1").Resize(UBound(MyArray)) = Application.Transpose(MyArray)
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[darkmidget]

Thank you for the quick response and help!

It worked extremely well; however, I didn't realize that there may end up being some blank cells in the 2nd worksheet (the vendor list) Can it be incorporated into the macro to leave blank cells out of the final destination (3rd worksheet)?

Thanks!

 

[Domenic]

Try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] MyArray() [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] WS1 [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] WS2 [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] WS3 [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] SearchRng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] FoundCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] LR1 [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LR2 [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] WS1 = Worksheets("Sheet1")
    [color=darkblue]Set[/color] WS2 = Worksheets("Sheet2")
    [color=darkblue]Set[/color] WS3 = Worksheets("Sheet3")
    
    [color=darkblue]With[/color] WS1
        LR1 = .Cells(.Rows.Count, "A").End(xlUp).Row
        [color=darkblue]Set[/color] SearchRng = .Range("A1:A" & LR1)
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    Cnt = 0
    [color=darkblue]With[/color] WS2
        LR2 = .Cells(.Rows.Count, "A").End(xlUp).Row
        [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] LR2
            [color=darkblue]If[/color] .Cells(i, "A").Value <> "" [color=darkblue]Then[/color]
                [color=darkblue]Set[/color] FoundCell = SearchRng.Find(what:=.Cells(i, "A").Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
                [color=darkblue]If[/color] FoundCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                    Cnt = Cnt + 1
                    [color=darkblue]ReDim[/color] [color=darkblue]Preserve[/color] MyArray(1 [color=darkblue]To[/color] Cnt)
                    MyArray(Cnt) = .Cells(i, "A").Value
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] i
    [color=darkblue]End[/color] [color=darkblue]With[/color]
            
    WS3.Range("A1").Resize(UBound(MyArray)) = Application.Transpose(MyArray)
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[darkmidget]

Thank you!
Worked wonderfully