IF THEN STATEMENTS

T

Tom

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
 
C

Calligra

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!
 
T

Tom Ogilvy

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.
 
M

merjet

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
 
Top