Transferring data from one sheet to another - errors

[honkin]

I have a sheet that has over 100,000 rows and I have a need to update data in a single column. To do this, I have the following code which looks at the Date (Column A), the Time (Column C) and the Name (Column Q). If those 3 things match, the data in Column AE in the source should end up in the target.

Here is the code

=IFERROR(INDEX('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$AE$3:$AE$103352,MATCH(1,INDEX((A3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$A$3:$A$103352)*(C3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$C$3:$C$103352)*(Q3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$Q$3:$Q$103352),0,1),0)),"No")

As I said, the sheet has just over 100,000 rows and what I am finding is that around 40,000 of the entries in Column AE now show No, instead of the required price data.

I have checked the formatting of all 3 reference columns. A is formatted as Custom mm/dd/yyyy in both source and target. C is formatted Custom as well, but as hh:mm in both source and target. Column Q is formatted as General, as it is simply a name.

Any thoughts as to what might be stopping this from performing correctly, as over 60,000 of the entries are fine?

2020 Latest Results 2021 4th Place Done.xlsb
	A	B	C																											AD	AE
2	Date	Day	Time																											P&L Win	P&L Place
3	01/01/2020	Wednesday	12:00																											270.48	52.92
4	01/01/2020	Wednesday	12:00																											-100	230.3
5	01/01/2020	Wednesday	12:00																											-100	607.6
6	01/01/2020	Wednesday	12:00																											-100	-100
7	01/01/2020	Wednesday	12:00																											-100	-100
8	01/01/2020	Wednesday	12:00																											-100	-100
9	01/01/2020	Wednesday	12:00																											-100	-100
10	01/01/2020	Wednesday	12:00																											-100	-100
11	01/01/2020	Wednesday	12:00																											-100	-100
12	01/01/2020	Wednesday	12:00																											-100	-100
13	01/01/2020	Wednesday	12:00																											-100	-100
14	01/01/2020	Wednesday	12:00																											-100	-100
15	01/01/2020	Wednesday	12:00																											-100	-100
16	01/01/2020	Wednesday	12:00																											-100	-100
17	01/01/2020	Wednesday	12:05																											695.8	No
18	01/01/2020	Wednesday	12:05																											-100	No
19	01/01/2020	Wednesday	12:05																											-100	No
20	01/01/2020	Wednesday	12:05																											-100	-100
21	01/01/2020	Wednesday	12:05																											-100	No
22	01/01/2020	Wednesday	12:10																											323.4	No
23	01/01/2020	Wednesday	12:10																											-100	No
24	01/01/2020	Wednesday	12:10																											-100	No
25	01/01/2020	Wednesday	12:10																											-100	-100
26	01/01/2020	Wednesday	12:10																											-100	No
27	01/01/2020	Wednesday	12:10																											-100	No
28	01/01/2020	Wednesday	12:10																											-100	No
29	01/01/2020	Wednesday	12:15																											333.2	No
30	01/01/2020	Wednesday	12:15																											-100	No
31	01/01/2020	Wednesday	12:15																											-100	No
32	01/01/2020	Wednesday	12:15																											-100	-100
33	01/01/2020	Wednesday	12:15																											-100	No
34	01/01/2020	Wednesday	12:15																											-100	No
35	01/01/2020	Wednesday	12:20																											313.6	No
36	01/01/2020	Wednesday	12:20																											-100	No
37	01/01/2020	Wednesday	12:20																											-100	No
2020 Latest Results 2021

Cell Formulas
Range	Formula
AE33:AE37,AE26:AE31,AE21:AE24,AE3:AE19	AE3	=IFERROR(INDEX('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$AE$3:$AE$103352,MATCH(1,INDEX((A3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$A$3:$A$103352)*(C3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$C$3:$C$103352)*(Q3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$Q$3:$Q$103352),0,1),0)),"No")

cheers

 

[Alex Blakenburg]

Time tends to be quite tricky. How you format it won't make a difference since it just masks (formats) what is under the hood, which is a fraction and can run to many decimal places.

See if using the below makes a difference. Copy it into AE3.
(The change is to round both sides of the time expression - column C - to 5 decimal places)

=IFERROR(
INDEX('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$AE$3:$AE$103353,
MATCH(1,
INDEX((A3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$A$3:$A$103353)*
(ROUND(C3,5)=ROUND('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$C$3:$C$103353,5))*
(Q3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$Q$3:$Q$103353),0,1),0)),
"No")

 

[honkin]

Time tends to be quite tricky. How you format it won't make a difference since it just masks (formats) what is under the hood, which is a fraction and can run to many decimal places.

See if using the below makes a difference. Copy it into AE3.
(The change is to round both sides of the time expression - column C - to 5 decimal places)

=IFERROR(
INDEX('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$AE$3:$AE$103353,
MATCH(1,
INDEX((A3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$A$3:$A$103353)*
(ROUND(C3,5)=ROUND('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$C$3:$C$103353,5))*
(Q3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$Q$3:$Q$103353),0,1),0)),
"No")

Cheers Alex and thanks so much for your reply

When I first pasted it into AE3, nothing happened; it just sat there as a formula, so I looked more closely. I saw that the final row references were all 103353 and not 103352, so changed them and still the same. Then I replaced all the ' as I know often in copying and pasting, they end up being slanted apostrophes or inverted commas. When I hit enter this time, it brought up and error message

No matter what I tried there, it didn't work, so I tried it another way. Excel was obviously saying there was a typo somewhere in the formula, so I tried it another way using just the change you made, which was this - (ROUND(C3,5)=ROUND('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$C$3:$C$103352,5)). I pasted that over the top of the original section and it worked. Not sure what is wrong in the full code you supplied and it doesn't really matter now. The core piece of your code was spot on and did the trick, albeit it took some time for me to try it that way.
Thanks so much for your help

 

[Alex Blakenburg]

Glad it worked for you, sorry it involved a bit of frustration to get there though. (I am thinking it might be to do with some international differences and I seem to have been a row out).
Thank you for the feedback. I appreciate you having shared the steps you needed to take to get it working at your end.

 

[honkin]

Glad it worked for you, sorry it involved a bit of frustration to get there though. (I am thinking it might be to do with some international differences and I seem to have been a row out).
Thank you for the feedback. I appreciate you having shared the steps you needed to take to get it working at your end.

Hi Alex

Just one thing further to this problem which has already been resolved is I did find that if the cell in the source is blank, a zero is copied to the target, rather than a blank. How is it possible for it to copy exactly what is in the source cell, rather than altering blanks to 0?

cheers mate

 

[Alex Blakenburg]

I think this will do it but it's not pretty.

• In addition to your original tests added the test
  (ISBLANK('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$AD$3:$AD$103353)),0,1),0))
• If this does not error out ie it is found then put ""
• Then do the original formula
• If it errors out put "No"

You might want to consider a Power Query solution.

=IF(NOT(ISERROR(
INDEX('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$AD$3:$AD$103353,
MATCH(1,
INDEX((A3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$A$3:$A$103353)*
(ROUND(C3,5)=ROUND('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$C$3:$C$103353,5))*
(Q3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$Q$3:$Q$103353)*
(ISBLANK('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$AD$3:$AD$103353)),0,1),0)))),"",
IFERROR(
INDEX('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$AD$3:$AD$103353,
MATCH(1,
INDEX((A3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$A$3:$A$103353)*
(ROUND(C3,5)=ROUND('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$C$3:$C$103353,5))*
(Q3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$Q$3:$Q$103353),0,1),0)),
"No"))

 

[honkin]

I think this will do it but it's not pretty.

• In addition to your original tests added the test
  (ISBLANK('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$AD$3:$AD$103353)),0,1),0))
• If this does not error out ie it is found then put ""
• Then do the original formula
• If it errors out put "No"

You might want to consider a Power Query solution.

=IF(NOT(ISERROR(
INDEX('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$AD$3:$AD$103353,
MATCH(1,
INDEX((A3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$A$3:$A$103353)*
(ROUND(C3,5)=ROUND('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$C$3:$C$103353,5))*
(Q3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$Q$3:$Q$103353)*
(ISBLANK('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$AD$3:$AD$103353)),0,1),0)))),"",
IFERROR(
INDEX('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$AD$3:$AD$103353,
MATCH(1,
INDEX((A3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$A$3:$A$103353)*
(ROUND(C3,5)=ROUND('[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$C$3:$C$103353,5))*
(Q3='[2020 Latest Results January 2021.xlsb]VDW-Place-Adapted-2020-01-01-20'!$Q$3:$Q$103353),0,1),0)),
"No"))

cheers Alex. It took a while to get the above working. There ended being a couple of errors in the code. Had to split it up to let Excel find and fix them. As a whole, the code just brought up the Not Trying To Type A Formula message. So I split it to the code prior to the ISBLANK and then from there on. It found errors in both sections doing it this way and fixed them, so all good in the end.

Thanks so much. It just save so much time being able to bring data in this way, though the code does labour a bit. What I mean is that I had a sheet with 36,000 rows and transferred data from 1 column in that sheet to a central record. Just to do a single column took around 20 minutes, so it isn't a speedy solution, but much faster than doing it manually

I had hoped the blank issue might have been resolved with some sort of IF<>"" then run the original code, otherwise "" The original code was lightning fast, so was surprised to see this labour so much with this blank checking. Not complaining, just an observation

cheers mate

 

[Alex Blakenburg]

Thanks for the feedback including on how its performing at your end.
Every formula I know returns zero if it finds the data that matches the criteria and the cell is blank.
In order for the blank to be substituted with "" but leave leave a zero as zero, we are having to run the formula twice which is not terribly efficient.
If we run the formula once but exclude blank then you would not be able to distinguish between not found and blank. In the current formula they would both show "No" and if you made it "" then not found would also show "".
In summary if a single formula:

• Includes blanks - how to distinguish between zero and blank (the latter is returned as zero)
• Excludes blanks - how to distinguish between not found currently "No" and blank (not found by exclusion)

You could see if running Countifs for the blank and then if not blank the Index/Match for not blank to see if it is faster but you are looking up another workbook and Countifs would only work if that other workbook is open.
PS: even for the index match formula, it is likely to run significantly faster if you have the other workbook open.