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

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

On Feb 24, 11:33*am, 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

The price lists may not be in the same format as each other but do
they arrive in the same format as the same list for the previous
month? If so you could insert, say, 4 columns into price list 1 that
you could copy and paste every time you receive a new price list. The
4 columns might contain, say "Amoxycillin", "Caps", "500mg", "100".
In this way you could then write a macro that searched for each of
these 4 things in price list 2 and had to match all 4 to be reasonably
certain of an exact match. You would have to decide exactly how many
"tag" columns you would need to be more certain of an exact match.

Certainly, there is no easy way round your problem and this is the way
I would probably do it.

Rgds

On 24 Feb, 12:24, StuartBisset wrote:
On Feb 24, 11:33*am, 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

The price lists may not be in the same format as each other but do
they arrive in the same format as the same list for the previous
month? *If so you could insert, say, 4 columns into price list 1 that
you could copy and paste every time you receive a new price list. *The
4 columns might contain, say "Amoxycillin", "Caps", "500mg", "100".
In this way you could then write a macro that searched for each of
these 4 things in price list 2 and had to match all 4 to be reasonably
certain of an exact match. *You would have to decide exactly how many
"tag" columns you would need to be more certain of an exact match.

Certainly, there is no easy way round your problem and this is the way
I would probably do it.

Rgds

I shall try both solutions on the weekend and let you guys know how it
goes.

Thanks for your advice.

Shaheed