honkin

Active Member
Joined
Mar 20, 2012
Messages
371
Office Version
  1. 2016
Platform
  1. MacOS
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:-

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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
This should work:

Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top