IF THEN STATEMENTS
 

I have a spreadsheet that contains vendor information for our
different locations. In column B have the vendor P/N listed and in
column A we have our P/N's listed for each vendor P/N. We have
different P/N's that have the same vendor P/N. I need to find out
where the vendor number is the same in column B and the P/N's are
different in column A. Here is an example of what I'm talking about.
The vendor P/Ns in cells B2 & B3 are the same, but our P/N's in cells
A2 & A3 are different. I have about 3,000 entries to go through.

A B
1 Our P/N Vend P/N
2 365181 11003
3 518136 11003
4 119706 11007
5 706119 11007
6 76873 13005
7 73768 13005
8 119546 23114
9 645911 23114
10 357501 27518
11 357501 27518
12 100722 27685
13 357587 27685
14 122690 27714
15 122690 27714
16 137710 28655
17 137710 28655

IF THEN STATEMENTS
 

Try storing the first PN in an Array and then searching the other
columns. i.e.
dim MyArray(3000) as integer
dim Myarray2() as integer
dim i, i2 as integer

for i = 0 to 2999
MyArray(i) = sheet1.cells(i, 3).value
next i

for i = 1 to 3000
if myarray(i + 1) = sheet1.cells(i, 3).value then
redim preserve myarray(i2)
myarray(i2, 0) = sheet1.cells(i, 3).value
myarray(i2, 1) = sheet1.cells(i, 1).value
i2 = i2 + 1
end if
next irow

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

IF THEN STATEMENTS
 

Make a copy of your data and work with that.

select you data in A and B and do Data=Filter=Advanced Filter, Select copy
to another location and select D1, click the Unique box in the bottom left
and click OK. This will give you a unique list in D:E. Now sort the list
on Vend P/N (column E). In F2 put in the formula

=countif(E:E,E2)

then drag fill down the column

select D1 and do Data=Filter=Autofilter. In the dropdown in F1 select 1
Highlight all the data in D:F (except row 1) and do Edit =Delete and select
entire row

Now do Data=Filter=Autofilter to remove the filter

Delete Columns A:C and F

Now you have your list.

--
Regards,
Tom Ogilvy





"Tom" wrote in message
om...
I have a spreadsheet that contains vendor information for our
different locations. In column B have the vendor P/N listed and in
column A we have our P/N's listed for each vendor P/N. We have
different P/N's that have the same vendor P/N. I need to find out
where the vendor number is the same in column B and the P/N's are
different in column A. Here is an example of what I'm talking about.
The vendor P/Ns in cells B2 & B3 are the same, but our P/N's in cells
A2 & A3 are different. I have about 3,000 entries to go through.

A B
1 Our P/N Vend P/N
2 365181 11003
3 518136 11003
4 119706 11007
5 706119 11007
6 76873 13005
7 73768 13005
8 119546 23114
9 645911 23114
10 357501 27518
11 357501 27518
12 100722 27685
13 357587 27685
14 122690 27714
15 122690 27714
16 137710 28655
17 137710 28655

IF THEN STATEMENTS
 

As long as they are ranked - firstly by B and secondly by A -
like your example, this can be done on the worksheet.
Put in C2: =IF(AND(A2=A1,B2=B1),"dup")
Then copy down. Those with FALSE in column C will be
unique; those with "dup" will be duplicates.

HTH,
Merjet