Array formula that takes too long to calculate (Hours & Hours & Hours)

[CasualDabbler]

See table below for examples
I have a data set with customer order dates Col (1,2,3) - Values
I need to analyse the weekly order patterns over a 3 month period, this can be 60,000+ rows of data
The array formula looks at these 3 columns and returns a binary pattern for that week.
Example

Customer 2 in week 190429, ordered Wednesday & Saturday
Customer 2 in week 190617, only ordered on the Wednesday​

My array formula is as follows (Cant remember where I got this from its not my own work) Range from 2:100000 as the data is often over 60,000 rows
{=TEXT(SUM(IF($Q$2:$Q$100000=$Q2,IF($B$2:$B$100000=$B2,10^(7-WEEKDAY($C$2:$C$100000,2))))),"0000000")}
It takes several hours to calculate this and often doesn't even complete successfully is there a more efficient way of doing this?

ID

<tbody>
[TD][C]Date[/TD]
[TD][Q]W/C (yymmdd)[/TD]
[TD][Array]Daily Pattern[/TD]

[TR]
[TD]2[/TD]
[TD]Wed 01/05/19[/TD]
[TD]190429[/TD]
[TD]0010010
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sat 01/06/19[/TD]
[TD]190429[/TD]
[TD]0010010[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]Mon 01/07/19[/TD]
[TD]190701[/TD]
[TD]1000100[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD]Tue 23/04/19[/TD]
[TD]190422[/TD]
[TD]0100101[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD]Fri 26/04/19[/TD]
[TD]190422[/TD]
[TD]0100101[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD]Sun 28/04/19[/TD]
[TD]190422[/TD]
[TD]0100101[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Wed 19/06/19[/TD]
[TD]190617[/TD]
[TD]0010000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>

Hope someone can help !

 

[Steve_]

See table below for examples
I have a data set with customer order dates Col (1,2,3) - Values
I need to analyse the weekly order patterns over a 3 month period, this can be 60,000+ rows of data
The array formula looks at these 3 columns and returns a binary pattern for that week.
Example

Customer 2 in week 190429, ordered Wednesday & Saturday
Customer 2 in week 190617, only ordered on the Wednesday​

My array formula is as follows (Cant remember where I got this from its not my own work) Range from 2:100000 as the data is often over 60,000 rows
{=TEXT(SUM(IF($Q$2:$Q$100000=$Q2,IF($B$2:$B$100000=$B2,10^(7-WEEKDAY($C$2:$C$100000,2))))),"0000000")}
It takes several hours to calculate this and often doesn't even complete successfully is there a more efficient way of doing this?

ID

<tbody>
[TD][C]Date[/TD]
[TD][Q]W/C (yymmdd)[/TD]
[TD][Array]Daily Pattern[/TD]

[TR]
[TD]2[/TD]
[TD]Wed 01/05/19[/TD]
[TD]190429[/TD]
[TD]0010010[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sat 01/06/19[/TD]
[TD]190429[/TD]
[TD]0010010[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]Mon 01/07/19[/TD]
[TD]190701[/TD]
[TD]1000100[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD]Tue 23/04/19[/TD]
[TD]190422[/TD]
[TD]0100101[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD]Fri 26/04/19[/TD]
[TD]190422[/TD]
[TD]0100101[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD]Sun 28/04/19[/TD]
[TD]190422[/TD]
[TD]0100101[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Wed 19/06/19[/TD]
[TD]190617[/TD]
[TD]0010000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>

Hope someone can help !

Use VBA to load it into an array, run your calculations on the VBA array, and then dump the results of that array to a sheet.

Or..I am not sure if it would affect it or not, but maybe adjust your ranges in your array formula you are using now to fit the data you are processing. You said it is often 60,000 rows, yet you are allocating for 100,000. Again, not sure if that would affect runtime or not, but i would guess likely so.

 

[CasualDabbler]

Thanks Steve. This array is beyond my understanding of Excel. I wouldn't know how to use VBA. I have tried adjusting it to the last row of the data, but it doesn't make a significant difference

 

[jmacleary]

The part of your formula that will be taking up a lot of time is the 10 to the power of.... If you are able to add some columns as helper columns, I am sure we could do away with that part (and probably also the fact its an array formula). If you can add columns let me know and I will cook up something that should do the trick.

 

[trunten]

I'm not sure if this will help you, but I added in a little helper column to try and speed things up.
Seems to work...

https://www.dropbox.com/s/dupselyhqwzoijq/order_pattern.xlsx?dl=1

 

[Fluff]

@trunten
Can you post your solution to the board, so that people can see it without having to download workbooks.
Thanks

 

[CasualDabbler]

No issue with using helper columns (I'll take any "Help" I can get) . **Cant access drop box from work

 

[trunten]

Sorry guys. Ok so,

ID

<tbody>
[TD][C]Date[/TD]
[TD][Q]W/C (yymmdd)[/TD]
[TD]weekday[/TD]
[TD][Array]Daily Pattern[/TD]

[TR]
[TD]2[/TD]
[TD]Wed 01/05/2019[/TD]
[TD]43584[/TD]
[TD]10000[/TD]
[TD]0010000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sat 01/06/2019[/TD]
[TD]43612[/TD]
[TD]10[/TD]
[TD]0000010[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]Mon 01/07/2019[/TD]
[TD]43647[/TD]
[TD]1000000[/TD]
[TD]1000000[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD]Tue 23/04/2019[/TD]
[TD]43577[/TD]
[TD]100000[/TD]
[TD]0100101[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD]Fri 26/04/2019[/TD]
[TD]43577[/TD]
[TD]100[/TD]
[TD]0100101[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD]Sun 28/04/2019[/TD]
[TD]43577[/TD]
[TD]1[/TD]
[TD]0100101[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Wed 19/06/2019[/TD]
[TD]43633[/TD]
[TD]10000[/TD]
[TD]0010000[/TD]
[/TR]
</tbody>

where the formula in column D is the following, auto-filled down:
=VALUE(SUBSTITUTE("0000000","0","1",WEEKDAY(B2,2)))

and the formula in column E, auto-filled is:
=TEXT(SUMIFS(D:D,A:A,A2,C:C,C2),"0000000")

 

[CasualDabbler]

I need to validate the outputs, but at first glance they look correct and it only took 2-3 mins to calc everything - THANKYOU !!!

 

[trunten]

No problem