Price Lists

[SD23]

Hi,

I have serveral price lists that i would like to compare the prices.

I have a spreadsheet with the compare table, sheet 1 with a price list and sheet 2 with a price list.

I have got the sheet 1 price list working but i am unable to get the second price list to work as the product name is slightly different.

All of this has to work automatically as i want to be able to copy and paste new price lists in every month.

I can send a sample of the spreadsheet as not sure how to attach it

I am very greatful for any help in advance

 

[rallcorn]

MUCH better solution than mine!!!

 

[scottylad2]

Excel Workbook
	B	C	D	E	F	G	H
73	Table One			Table Two		Price from Table one	% Difference
74	cream1 5% 15	566.00		Cream1 5%	653.00	566	-15.37%
75	cream2 5% 16	625.00		Cream2 5%	873.00	625	-39.68%
76	cream3 5% 17	351.00		Cream3 5%	118.00	351	66.38%
77	cream4 5% 18	405.00		Cream4 5%	231.00	405	42.96%
78	cream5 5% 19	260.00		Cream5 5%	757.00	260	-191.15%
79	cream6 5% 19	355.00		Cream6 5%	573.00	355	-61.41%
80	cream7 5% 20	133.00		Cream7 5%	24.00	133	81.95%
81	cream8 5% 20	139.00		Cream8 5%	338.00	139	-143.17%
82	cream9 5% 21	798.00		Cream9 5%	495.00	798	37.97%
83	cream10 5% 21	901.00		Cream10 5%	5.00	901	99.45%
84	cream11 5% 25	98.00		Cream11 5%	644.00	98	-557.14%
Sheet1

Excel Workbook
	H
74	-15.37%
Sheet1

Had the vlookup looking at the same cell, but this would work. It will lookup the lesser text strin, adds on the wild card and looks for one text string within another

 

[rallcorn]

Except, Scottylad2, your solution won't work for his specific examples of
Price list 1

Aceclofenac 100mg tablets

Amiodarone 100mg tablets 28

Baclofen 5mg/5ml oral solution 300ml

Price list 2

Aceclofenac 100mg Tabs

Amiodarone 100mg Tabs

Baclofen 5mg/ml Liquid

 

[scottylad2]

Except, Scottylad2, your solution won't work for his specific examples of
Price list 1

Aceclofenac 100mg tablets

Amiodarone 100mg tablets 28

Baclofen 5mg/5ml oral solution 300ml

Price list 2

=Vlookup(Aceclofenac 100mg&"*" Tabs

=Vlookup(Amiodarone 100mg&"*" Tabs

=Vlookup(Baclofen 5mg&"*"/ml Liquid

yes it would, in your Vlookup you would only need to lookup part of the text string you don't need to have the words in i've left black, just those in red and you don't really need all of that text either. Just the medicine name. In your table three you could have a table of just the first names of your list and return a value for comparrison from that

 

[rallcorn]

I agree it would work, but wouldn't he type in the portion of the text he's looking for in each case? I was trying to find a way to automate stripping the text down to a "matchable" piece.

 

[scottylad2]

I agree it would work, but wouldn't he type in the portion of the text he's looking for in each case? I was trying to find a way to automate stripping the text down to a "matchable" piece.

Could be done usng the text to column function without the need for formulas, then a vlookup as said in my earlier post

 

[rallcorn]

That would work as long as there is only one product & "strength/doseage".

If there was a situation like Aceclofenac 100mg & Aceclofenac 200mg he wouldn't be matching "like" items & if concatenate was used he'd be back to square one.