Comparing Prices in two excel sheets

S

Shaheed

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
 
J

Joel

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)
 
S

StuartBisset

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
Click to expand...

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
 
S

Shaheed

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
Click to expand...

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

Thanks for your advice.

Shaheed
 
Top