vba Copy Paste Conditional to put remark 1 2 3 .. etc

[DocAElstein]

The formula solution from pharmacologist ( https://www.excelforum.com/excel-pro...ml#post5353103 )

Put this in the top of column I of 1.xls and drag down and across however many columns you need.

=IF(OR(AND($D2=$E2,$D2<>$F2),AND($D2=$F2,$D2<>$E2)),IF(ISBLANK(INDEX([Macro.xlsm]Sheet1!C:C,MATCH($B2,[Macro.xlsm]Sheet1!$A:$A))),"",INDEX([Macro.xlsm]Sheet1!C:C,MATCH($B2,[Macro.xlsm]Sheet1!$A:$A))),"")

This is a nice idea, but there are a few problems.

Two minor problems:
_(i) A simple problem possibly a typo : you should not use column I of 1.xls , since that has data in. We can use any column away from the data, or we could start the formula at some convenient row down away from the data

_(ii) The formula will not work in 1.xls , since it will try to reference all the rows in a column in macro.xlsm – there are 1048576 rows in macro.xlsm, but a formula used in 1.xls will give us a problem , saying that it cannot use more than the row number for pre Excel 2007, which is 65536


Before looking further into the formula, lets simplifier/shorten/change a few names and paths to make the analysis and modification of the formula easier to follow.
I will store my files on my desktop. So the full path and file name of my files are:
C:\Users\Elston\Desktop\macro.xlsm
C:\Users\Elston\Desktop\1.xls
I will use a sheet name to help me later navigating the many duplicated / similar / cross posted questions fro the OP , Avinash. I will use
1_xlsSh1efJ20
macro_xlsmSh1efJ20 ( This is just for me to reference the workbooks as that uploaded at excelforum on June 20 ( https://www.excelforum.com/excel-pro...ml#post5352953 )
So before the use of any formula or coding, this is the situation:
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Symbol
2	ACC	22	1	2	3	4
3	ADANIENT	25	1	2	3
4	ADANIPORTS	15083	1
5	ADANIPOWER	17388	1	2	3	4	5	6	7
6	AMARAJABAT	100	1	2	3	4	5	6	7	8	9	10	11
7	ASIANPAINT	236

Worksheet: macro_xlsmSh1efJ20

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1200	1200	1173	1194.2	1194.2	22
3	NSE	ADANIENT	EQ	141.4	143.7	141.4	141.05	141.05	25
4	NSE	ADANIPORTS	EQ	309	309	309	309.25	309.25	15083
5	NSE	ADANIPOWER	EQ	33.3	33.3	32.1	32.35	32.35	17388
6	NSE	AMARAJABAT	EQ	555	578	555	572.85	572.85	100
7	NSE	ASIANPAINT	EQ	1529	1529	1529	1552.95	1552.95	236
8

Worksheet: 1-Sheet1 20 June excelforum

My version of pharmacologist’s formula to suit the workbook and worksheets name is then initially:

=IF(OR(AND($D2=$E2;$D2<>$F2);AND($D2=$F2;$D2<>$E2));IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A:$A)));"";INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A:$A)));"") 

=IF(OR(AND($D2=$E2,$D2<>$F2),AND($D2=$F2,$D2<>$E2)),IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C,MATCH($B2,[macro.xlsm]macro_xlsmSh1efJ20!$A:$A))),"",INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C,MATCH($B2,[macro.xlsm]macro_xlsmSh1efJ20!$A:$A))),"")

Note: I have given two forms. 1 is in English Excel, the other is in my German Excel , which I have change the formula default option to English from thee default German, but which still uses the ; separator rather than the English conventional ,
If I try to use this formula, in , for example, cell K2 of 1.xls , then I get the error situation mentioned in (i) associated with the row and column count:
RowsCountProblemInFormula.JPG : https://imgur.com/KZtIJ3h




In the next posts I look at corrections/ adjustments to the formula. ( Note the output required by the OP , is that produced by the working solution from ( https://excelfox.com/forum/showthrea...ll=1#post14130
https://excelfox.com/forum/showthrea...ll=1#post14142 )
This is the solution required

_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Symbol
2	ACC	22	1	2	3	4	5
3	ADANIENT	25	1	2	3	4
4	ADANIPORTS	15083	1
5	ADANIPOWER	17388	1	2	3	4	5	6	7	8
6	AMARAJABAT	100	1	2	3	4	5	6	7	8	9	10	11	12
7	ASIANPAINT	236

Worksheet: macro_xlsmSh1efJ20