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