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

**DocAElstein** · 06-08-2020, 08:39 PM

General formula construction / relation to OP requirement
It is helpful at this stage to make an initial attempt understand the formula.

We can see that we have an “outer” If , pseudo like

If ( criteria met resulting in having a column I value from 1.xls which needs to be looked for (matched) to a value in column B of macro.xlsm ,
____Then do some stuff to get the values 1 , 2 , 3 … etc ,
_______Else "" )

Here we can see the cause of the missing 1 in row 4 . This is the situation where the original data, unchanged should be present. So replacing "" with a reference to the original data , [macro.xlsm]macro_xlsmSh1efJ20!C2, should overcome this problem.
However, we will see a small problem that across the row we will have 0s instead of nothing, since , by default, Excel is evaluating like “nothing” number, which is zero
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	R	S	T	U
4	1	0	0	0

Worksheet: macro_xlsmSh1efJ20

This can be overcome with a trick to make it show “nothing text” by adding a &"" to the cell value, [macro.xlsm]macro_xlsmSh1efJ20!C2&""

So finally we have

Code:

 =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)));[macro.xlsm]macro_xlsmSh1efJ20!C2&"")

We have now solved the problem to put data in unchanged for the unmatched case. We need to modify somehow the first part of the outer If to get the correct results in.
We need to have values of 1 greater than the found values , and offset by one to the right

This part of the formula would normally, for the first cell return us the value of 1
INDEX(macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))
This part actually is present twice in an If, pseudo
If ( This is blank , then "" , else This )

I can see a way to do this to get the results for all but the first column, as follows: We would look for a match in the previous column and the result would be added by 1

For example, this formula…_

Code:

=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!B$2:B$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7)));"";INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))+1);C2&"")

_.. almost gives us what we want, for example in the second row
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	R	S	T	U	V	W
2	23	2	3	4	5

Worksheet: macro_xlsmSh1efJ20

One way to get the first column correct, could be to have a check on the part giving the result to see if the column first column is under consideration , in which case , we then need again to see if it is empty ( If(C2="";"";1) ) in which case a "" will be returned, else a 1

If(Column(C2)=3;If(C2="";"";1);INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))+ 1)

Finally, a working formula seems to be ( For in , for example R2 and copied across and down ) ,
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col

R

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!B$2:B$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(C2)=3,IF(C2="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),C2&"")

Worksheet: macro_xlsmSh1efJ20