Blank Cells Copying As Zero

[honkin]

hi

I have some code I use to transfer huge chunks of data from a sheet in one file to another. Here is the code:-

[COLOR=#000000][FONT=Helvetica]=IF(ISERROR(MATCH(1,INDEX((INT('/Users/SOS/Documents/My Documents/Horse/Football Advisor/Research/Master Data File/2017/Additional/New Algorithm/[December.csv]December'!A$2:A$15000)=A2)*('/Users/SOS/Documents/My Documents/Horse/Football Advisor/Research/Master Data File/2017/Additional/New Algorithm/[December.csv]December'!L$2:L$15000=M2),),[/FONT][/COLOR][COLOR=#ff0000][FONT=Helvetica][B]0[/B][/FONT][/COLOR][COLOR=#000000][FONT=Helvetica])),"",INDEX('/Users/SOS/Documents/My Documents/Horse/Football Advisor/Research/Master Data File/2017/Additional/New Algorithm/[December.csv]December'!AB$2:AB$15000,MATCH(1,INDEX((INT('/Users/SOS/Documents/My Documents/Horse/Football Advisor/Research/Master Data File/2017/Additional/New Algorithm/[December.csv]December'!A$2:A$15000)=A2)*('/Users/SOS/Documents/My Documents/Horse/Football Advisor/Research/Master Data File/2017/Additional/New Algorithm/[December.csv]December'!L$2:L$15000=M2),),[/FONT][/COLOR][COLOR=#ff0000][FONT=Helvetica][B]0[/B][/FONT][/COLOR][COLOR=#000000][FONT=Helvetica])))[/FONT][/COLOR]

Basically the code compares A2 (source) with A2 (target) as well as L2 (source) and M2 (target). If they match, the result on this occasion would be AB2 (source).

Column A in the source has date and time together, thus the use in INT. Column L in the source and column M in the target are both a name. So if the dates and names match, I get the result. I simply change AB to whichever column I want to transfer and change the filename to whichever month I want to transfer, then simply copy down the column and the result happens. Once all the data is transferred, sometimes upwards of 120,000 rows, I do a copy and paste special, pasting only the values, otherwise the large file has a tendency to keep calculating etc. I only need the values anyway.

The only downside to this is that if the source cell is blank, the result is always pasted as a 0. I have tried changing the final 0 in my code (in red) to "" but that gives this as a result #VALUE! I then tried making both zeros "" (you can see them both in red) but this actually makes all the results blank.

I know it will be something to do with possibly nesting a further IF within the formula. Something which says that if AB2 = blank, result should be blank, otherwise it should be whatever is in AB2.

Does that make sense at all? The code works so well for me, but just has this one flaw which is beyond me to fix.

Thanks so much in advance

 

[william_man]

This should work:

=IFERROR(IF(INDEX('/Users/SOS/Documents/My Documents/Horse/Football Advisor/Research/Master Data File/2017/Additional/New Algorithm/[December.csv]December'!AB$2:AB$15000,MATCH(1,INDEX((INT('/Users/SOS/Documents/My Documents/Horse/Football Advisor/Research/Master Data File/2017/Additional/New Algorithm/[December.csv]December'!A$2:A$15000)=B2)*('/Users/SOS/Documents/My Documents/Horse/Football Advisor/Research/Master Data File/2017/Additional/New Algorithm/[December.csv]December'!L$2:L$15000=M2),),0))="","",INDEX('/Users/SOS/Documents/My Documents/Horse/Football Advisor/Research/Master Data File/2017/Additional/New Algorithm/[December.csv]December'!AB$2:AB$15000,MATCH(1,INDEX((INT('/Users/SOS/Documents/My Documents/Horse/Football Advisor/Research/Master Data File/2017/Additional/New Algorithm/[December.csv]December'!A$2:A$15000)=B2)*('/Users/SOS/Documents/My Documents/Horse/Football Advisor/Research/Master Data File/2017/Additional/New Algorithm/[December.csv]December'!L$2:L$15000=M2),),0))),"")

As you say, it's just about capturing the blank with an IF statement. I have also used IFERROR( instead of your IF(ISERROR to keep the formula as short as possible.