Distinct Values

[oz74]

Hello all,

I need to produce a template for a table which shows me the number of distinct customers I have in each country each month.

I have a list of global sales transactions with Country, Customer ID and Sales ID:

• Each customer has a distinct Customer ID
• Each customer may have more than one purchase, ie. more than one Sales ID
• There is at least one distinct customer in each country.


I need a formula (rather than pivot table) that I can insert in a template to produce the following table:

Country No. of Distinct Customers
Argentina 23
Australia 54
Austria 3
Bahrain 17
Bangladesh 5
Belgium 33
etc.


I will have a monthly database and the template that I’m looking to create will be populated from this dataset. The dataset looks like:

Country Customer ID Sales ID
Argentina A515 B58748
Belgium D874 Z87489
Australia S597 J98194
Argentina A522 C59845
Belgium R784 R11165
Austria F487 Q87458
Australia M877 H98748
Argentina A515 B22598
Bahrain W874 U89859
Bangladesh Y874 K85974
Australia S597 H48712
Belgium D874 R88872
etc.

The number of rows in this dataset varies each month and I have other formulas in my template reference a few hundred rows below the average number of rows – this ensures I don’t have to re-reference the formulas each month. I will need the formula for the number of distinct Customer IDs to allow for this as well ie. be able to handle blank cells at the bottom of the dataset.

I have researched online but haven’t found anything yet. Some of the possibilities I’ve found have a division calculation within them which won’t work with blank cells.

Any thoughts and help greatly appreciated.

 

[oz74]

I could imagine your test data to be in A1:C13 and further down if need be where the 1st row are headers:

=LET(x,A2:INDEX(B:B,COUNTA(A:A)),REDUCE({"Country","No. Of Distinct Customers"},SORT(UNIQUE(TAKE(x,,1))),LAMBDA(a,b,VSTACK(a,HSTACK(b,COUNTA(UNIQUE(FILTER(TAKE(x,,-1),TAKE(x,,1)=b))))))))

Hi JvdV, this is great, thank you very much! Is there any way of removing the column headings entirely when the list is produced? I also have another column which has whether each customer is Retail or Corporate - is there any way of incorporating the ability to pick up only Corporate customers for example?

It's very useful to have a list produced but if I could challenge you one more time? Is it possible to create a formula similar to a SUMIFS or COUNTIFS whereby I have the parameters within the formula and I stipulate the country and it produces the result for just that country rather than an entire list of all countries?

Thanks again, much appreciated

 

[JvdV]

As to the 1st part, yes: you can use DROP():

=LET(x,A2:INDEX(B:B,COUNTA(A:A)),DROP(REDUCE(0,SORT(UNIQUE(TAKE(x,,1))),LAMBDA(a,b,VSTACK(a,HSTACK(b,COUNTA(UNIQUE(FILTER(TAKE(x,,-1),TAKE(x,,1)=b)))))),-1))

I hope I placed the paranthesis correctly now since I just modified my previous answer without actually testing.

For the 2nd part, it sounds like it may be a good new query/post here on the forum? Let others think along too!

 

[oz74]

If you look at the data you posted it's all jumbled up together & therefore virtually unusable.
Can yo just do a straight copy/paste from Excel into the post.

Hi Fluff, here's a screenshot of the data plus pasted in this post as text if that helps. Thanks in advance for your insights!


Country Cust ID Sales ID
Argentina A515 B22598
Australia M877 H98748
Bangladesh Y874 K85974
Argentina A515 B58748
Belgium D874 Z87489
Australia S597 H48712
Australia S597 J98194
Austria F487 Q87458
Bahrain W874 U89859
Argentina A522 C59845
Belgium D874 R88872
Belgium R784 R11165

 

[oz74]

As to the 1st part, yes: you can use DROP():

=LET(x,A2:INDEX(B:B,COUNTA(A:A)),DROP(REDUCE(0,SORT(UNIQUE(TAKE(x,,1))),LAMBDA(a,b,VSTACK(a,HSTACK(b,COUNTA(UNIQUE(FILTER(TAKE(x,,-1),TAKE(x,,1)=b)))))),-1))

I hope I placed the paranthesis correctly now since I just modified my previous answer without actually testing.

For the 2nd part, it sounds like it may be a good new query/post here on the forum? Let others think along too!

Hi JvdD, unfortunately Excel doesn't accept it, it highlights the last 1 as an issue.

Great idea re another post, watch out for it!

Thanks again!

 

[JvdV]

You are right, the correct syntax should be:

=LET(x,A2:INDEX(B:B,COUNTA(A:A)),DROP(REDUCE(0,SORT(UNIQUE(TAKE(x,,1))),LAMBDA(a,b,VSTACK(a,HSTACK(b,COUNTA(UNIQUE(FILTER(TAKE(x,,-1),TAKE(x,,1)=b))))))),-1))

 

[oz74]

Thank you all for your help on this, it's much appreciated