Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :-) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :-) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing 3 columns | Excel Worksheet Functions | |||
Comparing two columns of information with 2 new columns of informa | Excel Discussion (Misc queries) | |||
comparing two columns | Excel Discussion (Misc queries) | |||
comparing columns | Charts and Charting in Excel | |||
Comparing columns | Excel Programming |