I have an old price list which i want to use to populate the new price list.
The new stock list has some items the same - some are missing, some new
items are added. How can i copy the price field for the items on the old
stock list that match the new list? I cant figure out where to look on help.
The lists are in excel.
Hi Julian,
you can use VLOOKUP to do this.
Say your old list items are in A1:A100
and their prices are in B1:B100
The items in column A must be in alphabetical order
if your new list of items are in D1:D100
in E1 put this formula
=VLOOKUP(D1,$A$1:$B$100,2,FALSE)
This will look for the value in D1 in the table of the old stock A1:B100
If it finds a match it will return the value found in column B and put it in column E.
If it does not find that item in the old list it will retun a #N/A.
You can then drag this down for all your values in column D.
HTH.
Greg.
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."
Julian,
Use VLOOKUP() to find a the old price for those items that previously
exisited.
Assume: Old price list has Part # in ColA, Price in ColE
Sort old price list by Part #
Assume: New price list has Part # in ColA, Price in ColG
In new price list G2 enter
=if(iserror(vlookup(A2,'Old List'!A1:E999,5,FALSE),"",vlookup(A2,'Old
List'!A1:E999,5,FALSE)))
where "'Old List'!A1:E999" is the name of the sheet and range containing the
old prices.
This will return the price if Part No exists otherwise NULL
Copy down all rows in new list - Job done.
HTH
"julan" wrote:
> I have an old price list which i want to use to populate the new price list.
> The new stock list has some items the same - some are missing, some new
> items are added. How can i copy the price field for the items on the old
> stock list that match the new list? I cant figure out where to look on help.
> The lists are in excel.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks