vlookup

[Legacy 5232]

I have a set of product names in COLUMN A and another set in COLUMN B. Column A is the old inventory and COLUMN B is the new inventory. What's the formula to do a vlookup (or a lookup, not sure) to compare the two columns. I need to find out what's the difference between the two columns. Like what's new in COLUMN B that's not in COLUMN A and what's old in COLUMN A that's not in COLUMN B.

 

[just_jon]

In column C -

=MATCH(B1,A:A,0)

Will generate #N/A if B1 is not in column A, or return the row number of the 1st match in A if there is one.

Copy down as needed.

 

[Legacy 5232]

how would i

here's my next question....

How would I setup a macro to compare the data between COLUMN A and COLUMN B. COLUMN A is old inventory, COLUMN B is new inventory.


First function:
What I need ot know is what's in the old inventory (A) that's not in the new inventory (B), and display that product name from COLUMN A into column C with the word DELETE next to it. For example, if Widget1 is in column A but not column B, the output in column c should be Widget1-DELETE.

Second function:
I also need to know what's in the new inventory (B) that's not in the old inventory (A) and output in column D the product name in that cell with the word ADD next to it. Example, if Widget 2 is in COLUMN B but not COLUMN A then the output in COLUMN D should be, Widget2 - ADD.


Any ideas on this one? Thanks

 

[just_jon]

Formula solution -

Book1.xls
	A	B	C	D
1	Widget1	Widget4
2	Widget2	Widget5
3	Widget3	Widget1	Widget3-DELETE
4	Widget4	Widget2
5	Widget5	Widget23		Widget23-ADD
6	Widget6	Widget10	Widget6-DELETE
7	Widget22	Widget8	Widget22-DELETE
8	Widget8
9	Widget9		Widget9-DELETE
10	Widget10
Sheet2