Matching cells in 2 columns & adding info within the matched row.

[gdeaks27]

Hi there,
We get a price list once a week which include 2 columns, column 1 is product code & column 2 is sell price.

eg.
<TABLE style="WIDTH: 216pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=288><COLGROUP><COL style="WIDTH: 108pt" width=144><COL style="WIDTH: 108pt" width=144><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 108pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=144 align=right>700417207</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 108pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=144 align=right>428.75</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>700417215</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 align=right>91.88</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>700417231</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 align=right>606.38</TD></TR></TBODY></TABLE>

These product codes come under 3 different categories, SYSTEM, HANDSET, or CM on each of the 3 types we get different purchase discounts based on selling price so for example if we had 50% discount on SYSTEM 75% on HANDSET & 25% on CM then I would like to add a 3rd column in the price list so it looked like:
<TABLE style="WIDTH: 324pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=432><COLGROUP><COL style="WIDTH: 108pt" width=144><COL style="WIDTH: 108pt" span=2 width=144><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 108pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=144 align=right>700417207</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 108pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=144 align=right>428.75</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 108pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=144 align=right>214.38</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>700417215</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 align=right>91.88</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 align=right>22.97</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>700417231</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 align=right>606.38</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 align=right>454.78</TD></TR></TBODY></TABLE>

I've done a list in Excel with each product code in A and what type it is in B

eg
<TABLE style="WIDTH: 185pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=246><COLGROUP><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 102pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=136 align=right>700417207</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=110>

SYSTEM​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>700417215</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>

HANDSET​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>700417231</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>

CM​

</TD></TR></TBODY></TABLE>

So what I want to do is match the product code, look at the type & either add the calculation to column C in the pricelist or just add the type into C.

I do hope this makes sense?

Thanks
Garry

 

[jim may]

Maybe something like this... H2 should be copied Down

Excel Workbook
	A	B	C	D	E	F	G	H
1	Pcode	Category	Disc			Pcode	Price	Net Price
2	700417207	System	50%			700417207	428.75	214.38
3	700417215	Handset	75%			700417215	91.88	22.97
4	700417231	CM	25%			700417231	606.38	454.79
Sheet1

Excel 2007

Cell Formulas
Range	Formula
H2	=ROUND(G2*(1-VLOOKUP(F2,$A$2:$C$4,3,0)),2)

 

[gdeaks27]

Hi Jim,

Would this work if the Product code in the price list, & the other list weren't in the same row? From what I can see when the suppliers send the file they can be in different orders. and also may have a new product slap bang in the middle of the list, so If I did a sort ascending on them both there could be a case where the lists are not identical.

Cheers
Garry

 

[jim may]

The present Column A data list out UNIQUE Values

The present Column F data can include duplicates

Gotta run... Good luck..

 

[gdeaks27]

Thanks for your help Jim, Enjoy wherever your running to

 

[gdeaks27]

I've tried your suggestion & it works a treat! Thanks Jim your a star.