Help With Compare & Insert Macro

[gaspower]

Hello,
First thank you as always,

Need help with a macro. I have sheet1, column A is identifier (model number), sheet1 column B has number. Sheet2 column A is identifier (model number). I need to compare sheet1 column A with sheet2 column A. If identifier matches in both sheets, need to copy data sheet1 column B to matching row sheet2 and insert data to column S.

Thanks JR

Sheet1

Sheet2

 

[VoG]

VLOOKUP should work for that http://www.contextures.com/xlFunctions02.html

 

[gaspower]

Hello,

Thank you for the quick reply. I tried,

=VLOOKUP(A9,Sheet1!A$1:B$21,2,FALSE)

Did not seem to work.

JR

 

[VoG]

It should work

Excel Workbook
	A	B
9	ED109	20.95
Sheet2

Perhaps you have trailing spaces in the code numbers in one sheet and not the other. Try selecting each list of codes then running the TrimAll macro to remove trailing spaces http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[gaspower]

Hello VoG,

Does not seem to work.

sheet1

Sheet2 (what it should output

What the output of sheet2 with vlookup (wrong insert in S column)

Thanks JR

 

[VoG]

I cannot copy those images into Excel (except as pictures). Please use Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[gaspower]

Sheet1

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>E0104</td><td style="text-align: right;">1.5</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>E0105</td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>E0101</td><td style="text-align: right;">9.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>E0102</td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td>E0103</td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td>E0106</td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td>E0107</td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td>E0119</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td>E0120</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td>E0121</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>E0108</td><td style="text-align: right;">15.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td>E0109</td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td>E0110</td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td>E0111</td><td style="text-align: right;">3.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td>E0112</td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td>E0116</td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td>E0113</td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td>E0114</td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td><td>E0115</td><td style="text-align: right;">5.55</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td><td>E0117</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td><td>E0118</td><td>
</td></tr></tbody></table>

Sheet2

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 44px;"><col style="width: 34px;"><col style="width: 34px;"><col style="width: 34px;"><col style="width: 34px;"><col style="width: 34px;"><col style="width: 34px;"><col style="width: 34px;"><col style="width: 34px;"><col style="width: 34px;"><col style="width: 42px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>N</td><td>O</td><td>P</td><td>Q</td><td>R</td><td>S</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>E0101</td><td>Data</td><td>Data</td><td>Data</td><td>Data</td><td>Data</td><td>Data</td><td>Data</td><td>Data</td><td>Data</td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>E0102</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>E0103</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">3.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>E0104</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td>E0105</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td>E0106</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td>E0107</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">5.55</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td>E0108</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">20.95</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td>E0109</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>#N/A</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td>E0110</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>#N/A</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>E0111</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>#N/A</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td>E0112</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>#N/A</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td>E0113</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>#N/A</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td>E0114</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>#N/A</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td>E0115</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>#N/A</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td>E0116</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>#N/A</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>S1</td><td>=VLOOKUP(A9,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S2</td><td>=VLOOKUP(A10,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S3</td><td>=VLOOKUP(A11,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S4</td><td>=VLOOKUP(A12,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S5</td><td>=VLOOKUP(A13,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S6</td><td>=VLOOKUP(A14,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S7</td><td>=VLOOKUP(A15,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S8</td><td>=VLOOKUP(A16,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S9</td><td>=VLOOKUP(A17,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S10</td><td>=VLOOKUP(A18,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S11</td><td>=VLOOKUP(A19,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S12</td><td>=VLOOKUP(A20,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S13</td><td>=VLOOKUP(A21,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S14</td><td>=VLOOKUP(A22,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S15</td><td>=VLOOKUP(A23,Sheet1!A$1:B$21,2,FALSE)</td></tr><tr><td>S16</td><td>=VLOOKUP(A24,Sheet1!A$1:B$21,2,FALSE)</td></tr></tbody></table></td></tr></tbody></table>

 

[VoG]

Formula in S1 copied down

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	E0101																		9.95
2	E0102																		20.95
3	E0103																		20.95
4	E0104																		1.5
5	E0105																		20.95
6	E0106																		20.95
7	E0107																		20.95
8	E0108																		15.95
9	E0109																		20.95
10	E0110																		20.95
11	E0111																		3.95
12	E0112																		20.95
13	E0113																		20.95
14	E0114																		20.95
15	E0115																		5.55
16	E0116																		20.95
Sheet2

 

[gaspower]

Hello,

I see my error. In Vlookup I had S1 not set to A1. It works.

Thanks JR