comparing two columns, help!
 

Hi Everyone,

I am new to excel programming, but am experince with shell
scripting,etc. I have a problem and i'm now sure how i should go
about solving it.

I basically have a excel workbook which i use to upload to my website,
column A has model numbers, column B has description, ...column Q has
price,etc..(there's many other fields).

Then what i do is i copy an excel file from my supplier which has
about 4 columns. One is model(column AA), one is description(column
AA), inventory(column AA), and price(column AA).

I hope i explained that right. What I am trying to do, is somehow
compare model numbers (column A and column AA) and then compare the
price of the two(Column Q and AD). if the price of column Q is not 2%
higher then column AD, then the price should be adjusted. Lastly, if
a model number is in column A but not in column AA i would need it to
figure out someway to tell me there is a new product. (the model
numbers may not always be in the same order on the sheet).


I can email a sample if anyone wants to take a look.

I'm also not sure if its smart for me to copy and paste the suppliers
data into the same workbook as my actual data(i tested it and it does
not upload my site, but i'm not sure if it makes it easier or harder
for the macro).

Take care.

p.s. thanks in advance for take a look at this :-)

comparing two columns, help!
 

Mark,

Try this:

Sub TestMatch()

iLastrow = Cells(Rows.Count, "AA").End(xlUp).Row
Set Supplierrng = Range("AA2:AD" & iLastrow) ' Supplier Data



iLastrow = Cells(Rows.Count, "A").End(xlUp).Row ' find last row of inventory

For i = 2 To iLastrow

Cells(i, 1).Select ' Select Model
' Lookup model in Supplier data and return price in result
Sprice = Application.VLookup(ActiveCell.Value, _
Supplierrng, 4, False)
If Not IsError(Sprice) Then
' Model found
MsgBox ActiveCell.Value & " was found, supplier price is : " _
& Format(Sprice, "$0.00")
' Check price difference
If Cells(i, "Q") < Sprice * 1.02 Then
MsgBox "Price difference less than 2%"
End If
Else
' This model is not present in supplier data
MsgBox "Model " & ActiveCell.Value & " was not found"
End If

Next i

End Sub


HTH

"Mark" wrote:

Hi Everyone,

I am new to excel programming, but am experince with shell
scripting,etc. I have a problem and i'm now sure how i should go
about solving it.

I basically have a excel workbook which i use to upload to my website,
column A has model numbers, column B has description, ...column Q has
price,etc..(there's many other fields).

Then what i do is i copy an excel file from my supplier which has
about 4 columns. One is model(column AA), one is description(column
AA), inventory(column AA), and price(column AA).

I hope i explained that right. What I am trying to do, is somehow
compare model numbers (column A and column AA) and then compare the
price of the two(Column Q and AD). if the price of column Q is not 2%
higher then column AD, then the price should be adjusted. Lastly, if
a model number is in column A but not in column AA i would need it to
figure out someway to tell me there is a new product. (the model
numbers may not always be in the same order on the sheet).


I can email a sample if anyone wants to take a look.

I'm also not sure if its smart for me to copy and paste the suppliers
data into the same workbook as my actual data(i tested it and it does
not upload my site, but i'm not sure if it makes it easier or harder
for the macro).

Take care.

p.s. thanks in advance for take a look at this :-)

comparing two columns, help!
 

Mark,
Please try this macro. Right click the worksheet tab and select View Code.
Copy and paste this code into it.

Dim ILoopA As Integer
Dim ILoopAA As Integer
Dim NumRowsColA As Integer
Dim NumRowsColAA As Integer
Dim Found As Boolean
Sub Mark()
Columns("A").Interior.ColorIndex = xlNone
NumRowsColA = Range("A65536").End(xlUp).Row
NumRowsColAA = Range("AA65536").End(xlUp).Row
For ILoopA = 1 To NumRowsColA
Found = False
For ILoopAA = 1 To NumRowsColAA
If Cells(ILoopA, 1) = Cells(ILoopAA, 27) Then
Found = True
If Cells(ILoopA, 17) < 1.02 * Cells(ILoopAA, 28) Then
Cells(ILoopA, 17) = 1.02 * Cells(ILoopAA, 28)
Exit For
End If
End If
Next ILoopAA
If Found = False Then
Range("A" & ILoopA).Interior.ColorIndex = 6
End If
Next ILoopA
End Sub

Run the macro by going to Tools Macro Macros...

After you run it, those models in column A not found in column AA will be
highlighted in yellow.

The next time you run the macro, it will first re-set column A with no
yellow highlights, then highlight the column A cells that are unique.

"Mark" wrote:

Hi Everyone,

I am new to excel programming, but am experince with shell
scripting,etc. I have a problem and i'm now sure how i should go
about solving it.

I basically have a excel workbook which i use to upload to my website,
column A has model numbers, column B has description, ...column Q has
price,etc..(there's many other fields).

Then what i do is i copy an excel file from my supplier which has
about 4 columns. One is model(column AA), one is description(column
AA), inventory(column AA), and price(column AA).

I hope i explained that right. What I am trying to do, is somehow
compare model numbers (column A and column AA) and then compare the
price of the two(Column Q and AD). if the price of column Q is not 2%
higher then column AD, then the price should be adjusted. Lastly, if
a model number is in column A but not in column AA i would need it to
figure out someway to tell me there is a new product. (the model
numbers may not always be in the same order on the sheet).


I can email a sample if anyone wants to take a look.

I'm also not sure if its smart for me to copy and paste the suppliers
data into the same workbook as my actual data(i tested it and it does
not upload my site, but i'm not sure if it makes it easier or harder
for the macro).

Take care.

p.s. thanks in advance for take a look at this :-)