Inputting an Array formula over a number of cells simultaneously

[doug firr]

Lets say I have months of the year from Jan to Dec and for each a formula that requires an array formula.

The formula takes a long time to run ~ 45 mins but I can live with that.

When I type the formula under Jan I must CSE then wait 45 mins for it to run. As of typing this message I have dragged the formula over to Dec and it appears to be working but I'm still waiting for it to run.

My question then, is do I have to cse each cell individually or is there a way to highlight a range of cells and run them as arrays (CSE) in a oner?

 

[shg]

No, you can't.

Are you persuaded that there's no better formula that might take a small fraction of that time?

 

[doug firr]

I wish there was but I've asked the crowd already: http://www.mrexcel.com/forum/excel-questions/820823-sumproduct-unique-records-returned.html.... unless you know of another?

 

[shg]

I generated 100,000 random dates betwee 1/1/14 and 12/31/14, and next to that, 100,000 random numbers between 1 and 10,000, then converted all to values and sorted:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

Date​

[/td][td]

Item​

[/td][/tr]

[tr][td]

2​

[/td][td]

01/01/2014​

[/td][td]

171​

[/td][/tr]

[tr][td]

3​

[/td][td]

01/01/2014​

[/td][td]

253​

[/td][/tr]

[tr][td]

4​

[/td][td]

01/01/2014​

[/td][td]

258​

[/td][/tr]

[tr][td]

5​

[/td][td]

01/01/2014​

[/td][td]

332​

[/td][/tr]

[tr][td]

6​

[/td][td]

01/01/2014​

[/td][td]

460​

[/td][/tr]

[tr][td]

7​

[/td][td]

01/01/2014​

[/td][td]

526​

[/td][/tr]

[tr][td]

8​

[/td][td]

01/01/2014​

[/td][td]

527​

[/td][/tr]

[tr][td]

9​

[/td][td]

01/01/2014​

[/td][td]

530​

[/td][/tr]

[tr][td]

10​

[/td][td]

01/01/2014​

[/td][td]

531​

[/td][/tr]

[tr][td]

11​

[/td][td]

01/01/2014​

[/td][td]

546​

[/td][/tr]

[tr][td]

12​

[/td][td]

01/01/2014​

[/td][td]

627​

[/td][/tr]

[tr][td]

13​

[/td][td]

01/01/2014​

[/td][td]

691​

[/td][/tr]

[tr][td]

14​

[/td][td]

01/01/2014​

[/td][td]

692​

[/td][/tr]

[tr][td]

15​

[/td][td]

01/01/2014​

[/td][td]

715​

[/td][/tr]

[tr][td]

16​

[/td][td]

01/01/2014​

[/td][td]

716​

[/td][/tr]

[tr][td]

17​

[/td][td]

01/01/2014​

[/td][td]

729​

[/td][/tr]

[tr][td]

18​

[/td][td]

01/01/2014​

[/td][td]

860​

[/td][/tr]
[/table]

The numbers in col B stand in for your email addresses.

Then I pivoted that, with dates grouped by month, and months duplicated down the column:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Date[/td][td]Item[/td][/tr]

[tr][td]

2​

[/td][td]Jan[/td][td]

1​

[/td][/tr]

[tr][td]

3​

[/td][td]Jan[/td][td]

3​

[/td][/tr]

[tr][td]

4​

[/td][td]Jan[/td][td]

6​

[/td][/tr]

[tr][td]

5​

[/td][td]Jan[/td][td]

9​

[/td][/tr]

[tr][td]

6​

[/td][td]Jan[/td][td]

10​

[/td][/tr]

[tr][td]

7​

[/td][td]Jan[/td][td]

11​

[/td][/tr]

[tr][td]

8​

[/td][td]Jan[/td][td]

15​

[/td][/tr]

[tr][td]

9​

[/td][td]Jan[/td][td]

16​

[/td][/tr]

[tr][td]

10​

[/td][td]Jan[/td][td]

17​

[/td][/tr]

[tr][td]

11​

[/td][td]Jan[/td][td]

19​

[/td][/tr]

[tr][td]

12​

[/td][td]Jan[/td][td]

21​

[/td][/tr]

[tr][td]

13​

[/td][td]Jan[/td][td]

23​

[/td][/tr]

[tr][td]

14​

[/td][td]Jan[/td][td]

24​

[/td][/tr]
[/table]

The item column is intrinsically de-deduplicated. Then I created a simple table to count the pivot results.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr]
[tr][td]

1​

[/td][td]

Month​

[/td][td]

Unique​

[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Jan[/td][td]

5703​

[/td][td]H2: =COUNTIF(D:D, G2)[/td][/tr]

[tr][td]

3​

[/td][td]Feb[/td][td]

5416​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]Mar[/td][td]

5760​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Apr[/td][td]

5529​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]May[/td][td]

5717​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Jun[/td][td]

5558​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Jul[/td][td]

5718​

[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]Aug[/td][td]

5650​

[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]Sep[/td][td]

5619​

[/td][td][/td][/tr]

[tr][td]

11​

[/td][td]Oct[/td][td]

5718​

[/td][td][/td][/tr]

[tr][td]

12​

[/td][td]Nov[/td][td]

5607​

[/td][td][/td][/tr]

[tr][td]

13​

[/td][td]Dec[/td][td]

5740​

[/td][td][/td][/tr]

[tr][td]

14​

[/td][td][/td][td]

67735 ​

[/td][td]H14: =SUM(H2:H13)[/td][/tr]
[/table]

The results were near instantaneous.

 

[doug firr]

Interesting, might give that a try. I wish pivot tables just had a "count distinct" values though, would make life easier. Thanks for your response.