Compare Two workbooks and copy values

[DanielS]

I am working in Excel 2000. I have a main workbook named Pramon.xls which has 200 rows and columns A to P with data. I have other files which may have from 150 to 250 rows and columns from A to H. I wish to compare values in Column D of Pramon.xls with D column values of the second workbook and if the values of two files match, then place the corresponding M cell values of Pramon.xls in I column of the second workbook at appropriate cells. Could someone help me with this. I could not find similar solution elsewhere.

 

[oldbrewer]

			col D		col M						col D		col I
1	2	3	4	5	6			12	13	14	15	16
2	3	4	5	6	7			13	14	15	5	17	5
3	4	5	6	7	8			14	15	16	17	18
4	5	6	7	8	9			15	16	17	18	19
5	6	7	8	9	10			16	17	18	19	20
6	7	8	9	10	11			17	18	19	9	21	9
7	8	9	10	11	12			18	19	20	21	22
8	9	10	11	12	13			19	20	21	22	23
9	10	11	12	13	14			20	21	22	23	24
10	11	12	13	14	15			21	22	23	24	25
11	12	13	14	15	16			22	23	24	14	26	14
12	13	14	15	16	17			23	24	25	26	27
13	14	15	16	17	18			24	25	26	27	28
14	15	16	17	18	19			25	26	27	28	29
15	16	17	18	19	20			26	27	28	29	30
		formula giving 5 in col I of second table
		=IF(D9=L9,D9,"")
		put the full address for D9, something like
		=if(pramon.xls:sheet1!D9
		use help to check syntax

<colgroup><col><col span="13"></colgroup><tbody>
</tbody>

 

[DanielS]

Thanks for your answer. Unfortunately, I was not in a position to incorporate your formula into a macro as I am not quite an expert in Excel. Could someone suggest a macro to carry out the above operation?

 

[oldbrewer]

I never said a macro

 

[DanielS]

Hi Oldbrewer:

Unfortunately I could not make out much from your answer, the chart and the formula. That's why I requested if anyone from our forum could come up with some sort of a macro to do that task. Could you explain your solution in little more detail so that a layman like me can understand it? What would be the exact formula, each component of the formula, and where to place it etc, etc.

 

[DanielS]

Hi Oldbrewer:

I would like to draw your attention to my earlier post. I hope you have gone through the same. I am eagerly awaiting your detailed response.

 

[oldbrewer]

pramon_column_D	pramon_column_M			second_workbook_column_D	second_workbook_column_I
1	510			22		@@@@@
2	520			33
3	530			3	530
4	540			99
5	550			88
6	560			77
7	570			66
8	580			8	580
9	590			101
10	600			1092
formula incellmarked @@@@@
=IF(E5=A5,B5,"")
you will have to tweak this formula to something like
=if(e5=pramon.xls:sheet1!a5,pramon.xls:sheet1!b5,"")
check the exact syntax in help

<colgroup><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>
</tbody>