Splitting excel array formula due to huge data amount

[mark888]

Hi Experts,

I previously have a formula given here that is an array formula as below where it checks if a cell detail is within 24 hours of each other but the problem is I am working with a huge data of almost 1mil rows and even for 10k lines it takes about 40mins for this formula to run.
Can anyone advice if this is something I can split the formula into or a different or better way to do it?
Attaching the sample result file.

{=SUMPRODUCT((ABS($D$2:$D$995887-D2)<1)*($C$2:$C$995887=C2))>1}


Kind Regards,
Mark.

[JohnTopley]

Would a VBA solution be acceptable? If so, can you post a larger file( against normal practice on the forum I know!) so I can get a measure of performance.

And can the data be sorted by Name and Opened time?

A test of 960,000 rows took a few seconds.

Thank you.

[kvsrinivasamurthy]

Pl see file.
Here is the code for UDF. This works faster. Pl try.
Let me know any problem. If works ok Pl tell the time difference.

Please Login or Register  to view this content.

UDF

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List

[Bo_Ry]

Try

=COUNTIFS(B:B,B2,C:C,"<"&C2+1,C:C,">"&C2-1)>1

[mark888]

Hi JohnTopley,

Would you be so kind to share the VBA code and I can run it from my end.
I tried the below solutions from kvsrinivasamurthy and Bo_ry and both works but took me 4 hours to run it but if you have something which takes seconds I am very much interested. :-)

Kind Regards,
MArk,

[mark888]

Thanks Bo_ry and kvsrinivasamurthy really appreciate your help both of the formula works but it took hours to process 1million rows of data which I assume would be normal.
Hopefully JohnTopley has something which can help to reduce the time.

Kind Regards,
MArk.

[JohnTopley]

Please Login or Register  to view this content.

The data is sorted by name then Opened Time

Sheet2 of the attached has the 96,000 rows (purely a volume test). Click RUN to run macro.

I have run it with 960,000 rows (too big to upload!)

VBA assumes data as per sheet2.

Ensure you headings in A:D of row 1

[mark888]

Hi JohnTopley,

I tried with my data and it gave an error. I assume could it be because I have spaces and characters as well?
Attaching the file with 10k lines which I used. Tried to show more data but it went over the 1MB limit. :-(

Kind Regards,
MArk.

[mark888]

It actually ran successfully now but it changed some of the values in column B.
Ran it again and it failed.

[JohnTopley]

Ensure you headings in A:D of row 1

I don't think you followed the above!

See Sheet3

[mark888]

Hi JohnTopley,

Yes I see sheet3 but the data in column B has changed from the original file. Did it overwrite the data with something else or am I wrongly following what you meant.
Apologize If I cant understand. I did the same just pasted the data from A2 onwards.

Kind Regards,
MArk.

[JohnTopley]

I explained that the data is SORTED by Name and the Opened Time to minimise the computation time.

You did not indicate this was unacceptable. The data can be "Unsorted" by numbering each row prior to the initial sort, do the computation then re-sort on the original row numbers.

I will be signing off now as its late evening here in the UK. I'll look again in the morning.

[mark888]

Ahh ok understood now, thanks for looking into this and wish you a good evening. :-)

Kind Regards,
Mark.

[kvsrinivasamurthy]

Here is macro code. Pl try

Please Login or Register  to view this content.

[JohnTopley]

Please Login or Register  to view this content.

Added column E which allows the data to be resorted (routine "Unsort")to its original sequence after the computation.

PLEASE ensure you have headings in columns A:E in row1 as in Sheet3

[mark888]

This is perfect, many thanks again for your help JohnTopley and most importantly your time. :-)

Kind Regards,
MArk.

[mark888]

Thanks kvsrinivasamurthy, this one also works so now I have 2 ways, appreciate it. :-)

[JohnTopley]

Curiosity ... how long did it take to process your file?

[mark888]

It took me 3.6mins with kvsrinivasamurthy macro and yours took about 35seconds.
Thats a great reduction from my original array formula which took 6 hours for 300k lines. I only wish I could treat you a beer!

Kind Regards,
Mark.

[JohnTopley]

Thank you for the feedback.

You're very welcome: I'll have "virtual" beer on you!

Regards,

John