On 24 Feb, 12:24, StuartBisset <stuartbis...@gmail.com> wrote:
> On Feb 24, 11:33*am, Shaheed <shaheed.fa...@gmail.com> 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
|
Similar Threads
