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

[DocAElstein]

row problem , (ii)
We can overcome the row problem , (ii) , by fixing the ranges used in some parts of the formula. In the practice we might know the maximum rows, or we could estimate the largest most likely. ( If we were using VBA to put the formula in, then we could dynamically determine it ( http://www.excelfox.com/forum/showth...ll=1#post11466 Making Lr dynamic ( using rng.End(XlUp) for a single column. ) )
For simplicity of this analysis we will limit the rows to rows 2 – 7
This formula example solves the row problem , (ii)

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

Further adjustments/Corrections
The OP actually wants the results in macro.xlsm, not 1.xls. ( As the OP did not produce a before and after, it is easy to get confused…. – half the time, this OP, Avinash doesn’t know himself what he wants )
The formula is already quite flexible in that it can be placed in any cell in 1.xls. We can make it further flexible by changing the cell references for cells in 1.xls to include the workbook and first worksheet of 1.xls, ( '[1.xls]1_xlsSh1efJ20'! ) This will then make the formula work equally well in either 1.xls or macro.xlsm

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

For example, I put this formula in R2 of macro.xlsm and drag it across and down. Here for example just shown 4 of the formulas: ( Note Excel has removed the workbook and worksheet referrence for 1.xls - this is just a strange habit of Excel : - Excel removes the bits of the cell referrence that it does not need ... )
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

Row\Col	R	S
2	=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"")	=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"")
3	=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$E3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$F3),AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$F3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"")	=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$E3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$F3),AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$F3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"")

Worksheet: macro_xlsmSh1efJ20


I get for the full range giving the following results.

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

Row\Col	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
2	1	2	3	4
3	1	2	3
4
5	1	2	3	4	5	6	7
6	1	2	3	4	5	6	7	8	9	10	11
7

Worksheet: macro_xlsmSh1efJ20

So it looks like the formula is “working” … but it is not giving us the correct results :
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

Row\Col	C	D	E	F	G	H	I	J	K	L	M	N
2	1	2	3	4	5
3	1	2	3	4
4	1
5	1	2	3	4	5	6	7	8
6	1	2	3	4	5	6	7	8	9	10	11	12
7

Worksheet: macro_xlsmSh1efJ20


We will investigate further in the next posts