Average

[Blake 7]

Hi all,

I need to find an average - The question I have to answer is "What is the avg number of unique outlets visited by customers in a year"

Normally this would be a simple task, however, the quantity of data is making this a toughy and i'm struggling!

In Col A I have 600,000 Customer ID's
In Col C I have the names of the Outlets they visited
In Col G I have a list of 192,000 Unique Customer ID's (uniqued from Col A)

I need to find the number of unique sites visited by Custome ID then divide this by the number of customers.

Given this quantity of data - any suggestions for achieving this.

Attached is a v small sample of anonymised data

Thanks

[NBVC]

In the sample you give, what are some expected results... as far as I can see there are no duplicate outlets for specific customer.

[Blake 7]

Hiya

Cell D2 000002658X, this cust id appears 6 times in Col A - what I need is how many unique outlets he visited, Col B - in this case 6.

Does that help?

What i need to arrive at is the number of Unique outlets visited by cust id and then divide this by the number of cust ids to get the avg number of outlets visited!

[daddylonglegs]

I'm assuming that by "unique" you mean different, e.g. for each customer get a total of the different outlets visited.

Try a helper column, e.g. in C2 copied down

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,1,"")

Then in E2 copied down

=SUMIF(A:A,D2,C:C)

Now average column E....

[Blake 7]

Hey DLL - thanks for that. I'll give it a shot but what im finding is that with this many cols excel blows up!!

I'll give it a shot now and report back.

Thanks for your help.

[daddylonglegs]

Yes, I'm hoping that those formulas are simple enough to use over thousands of rows - I didn't test that........

[Blake 7]

i'll report back! its calcing now!

[daddylonglegs]

If that isn't fast enough then possibly use 2 helper columns - concatenate A and B in column G by using this formula in G2 copied down

=A2&"-"&B2

then in C2 copied down

=IF(ISNUMBER(MATCH(G2,G$1:G1,0)),"",1)

[Blake 7]

I'm assuming that by "unique" you mean different, e.g. for each customer get a total of the different outlets visited.

Try a helper column, e.g. in C2 copied down

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,1,"")

Then in E2 copied down

=SUMIF(A:A,D2,C:C)

Now average column E....

Hiya - some fb. I tried this way and waited 35 mins !!! then had to Ctrl alt del to stop process! will try your other method now!!