The only way I can see doing this is for you to manually make a table with
column A being the Code for Price List 1 and Column B being the PIP code for
Price List 2 for the same product. You may also want to put in column c the
description to make it easier to compare the two lists. Once you make the
table once the rest is easy because you can use VLOOKUP to get the prices

In column D put in the formula (Sheet 1 is where Price List 1 is located)

=Vlookup(Sheet1!A1:C100,A1,3,False)

In column E put in the formula (Sheet 2 is where Price List 2 is located)


=Vlookup(Sheet2!A1:E200,B1,5,False)


"Shaheed" wrote:

Hi!

I receive prices from various suppliers on a monthly basis and would
like to compare them. The problem I have is that the price lists are
not in the same format and the product descriptions differ in the way
that they are presented. See example below:

Price list 1:

Code Description
Price
2004 Amoxycillin 125mg Syrup ---- 100ml £0.39
4126 Amoxycillin 250mg Capsules ---- 21s £0.23
0126 Amoxycillin 250mg Capsules - 500s £7.32
2005 Amoxycillin 250mg Syrup ---- 100ml £0.50
0125 Amoxycillin 500mg Capsules ---- 100s £3.48
4125 Amoxycillin 500mg Capsules ---- 21s £0.38
2006 Amoxycillin SF 125mg Susp ---- 100ml £0.36
2007 Amoxycillin SF 250mg Susp ---- 100ml £0.46
2008 Amoxycillin SF Sachets 3g ---- 2s £3.30

Price list 2:

PIP Code Product Description Strength Pack Price
761-7624 Amoxycillin Caps 250mg 21 £0.25
761-0033 Amoxycillin Caps 250mg 500 £6.73
761-7897 Amoxycillin Caps 500mg 21 £0.43
761-0009 Amoxycillin Caps 500mg 100 £2.99
761-1627 Amoxycillin S/F Sachets 3g 2 £3.39
761-1221 Amoxycillin S/F Susp 125mg 100ml £0.48
761-1239 Amoxycillin S/F Susp 250mg 100ml £0.49
761-0397 Amoxycillin Syrup 125mg 100ml £0.38
761-0405 Amoxycillin Syrup 250mg 100ml £0.51

Is there some sort of VBA code that I can write to do all the hard
work for me?


Thanks very much in advance.

Shaheed Fazal