Need help updating price list

Hi
sheets are much easier to work with than pictures

it would make it easier to help you if we had a sample of the data you are working with

Hi monkmonkey
This is rather straight forward. If you have two workbooks, post two workbooks. If you have one workbook with two (or more worksheets) post that. Let us know the LINK between the workbooks/worksheets.

in colums H, try this formula...adjust the file name if i have it the wrong way round....

=VLOOKUP(A2,'samplepricinglist2.csv(1).xlsx'!$A:$F,6,FALSE)

Hi monkmonkey
As I said earlier, this is rather straight forward. Should you desire a VBA solution give me a bit of time (couple of days). It's Family Vacation time and I have a houseful of guests.

Hi monkmonkey

Place BOTH workbooks ("Oldprices" and "Newprices") in the SAME Folder. Place this code in a General Module of your "Oldprices" workbook. "Newprices" workbook can be open or closed, no matter...the code runs from "Oldprices" and opens "Newprices" if it's not open.

Change this Constant to the actual name of your "Newprices" workbook

Please Login or Register  to view this content.

It matters not what the actual name of your "Oldprices" workbook is...just place this code in a General Module of that workbook...and, as always, test the code on a copy of your "Oldprices" workbook.

Let me know of issues.

Please Login or Register  to view this content.

Hi Derek

Open a COPY of your "Old Prices" workbook (whatever it's called).
From your keyboard press ALT---->F11 to open VBA.
In the VBA Ribbon select Insert--->Module.
Copy the Code from this Thread Post #12 and Paste into the right hand window of the VBA screen.
You'll end up with this
VBA Screen.jpg
The code is now in the workbook.
Be certain to change this line of code to the actual name of your Newprices workbook

Please Login or Register  to view this content.

Close the VBA screen and save the file.

Now you need to decide how you want to trigger the Code. You can do it from a button or you can add it to your Quick Access Toolbar.
See this link for the Button approach
http://office.microsoft.com/en-us/ex...010342137.aspx
See this link for the Quick Access Toolbar approach.
http://www.dummies.com/how-to/conten...ck-access.html
Let me know of issues.

Hi Derek

I don't know the answer to this question

how would I do this for openoffice

Perhaps you can pose the question in the Open Office Forum. Let me know how you make out.

Hi Derek

I'm uploading a stand alone "Macro Book" that contains all the code. There's two main procedures in the code:

• Clean_Old_id
• Find_Me

Procedure Clean_Old_id compares all ID Numbers in Old Prices Column B to Stock Code in New Prices Column A. If they don't exist in New Prices they're deleted from Old Prices. At this point it's a stand alone procedure that takes about 12 seconds to run on my platform.

Procedure Find_Me compares all Stock Code in New Prices Column A to all ID Numbers in Old Prices Column B. If it finds a match it updates Columns I and J of Old Prices with the values in New Prices Columns B and C respectively. At this point it's a stand alone procedure that takes about 20 seconds to run on my platform.

Your setup is as follows:
Place the attached Macro Book file, your Old Prices file and your New Prices file in the same folder. Make certain the code is updated for your File Names and Columns here

Please Login or Register  to view this content.

Open the Macro Book and click a button...I'd run the Clean Old Prices procedure first but that's your call. Then run the Update Old Prices procedure.

Please note...Old Prices is an .xlsx file (no macros in it). Also, be aware, these procedures can be run together but I currently have then running as stand alone...let's get it working first.

Let me know of issues.

Hi monkmonkey,

I need to learn a lot more about Open Office as I've standardized on Microsoft Office. I've found a few pages to answer my questions and possibly yours too at:
http://www.techsoup.org/learningcent...e/page4765.cfm
of http://answers.yahoo.com/question/in...5122252AAXdsvG or
http://www.brighthub.com/computing/w...les/70973.aspx

Hi Derek

The attached book contains code that does this

I would also like to have a macro button named "New Products" which would let me know all of the stock codes that are not in "oldprices" but are in "newprices".

It creates the list in a new worksheet in "New Prices" workbook.

Please be aware, you have 12 duplicate items in "Old Prices".