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.

 

[Fluff]

Hi & welcome to MrExcel.

Can you post your data using the XL2BB add-in.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[oz74]

Hi & welcome to MrExcel.

Can you post your data using the XL2BB add-in.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Fluff,

Thanks for your reply. I tried to add the XL2BB but it's a work computer and heavily secured. I've put the data in the thread, hopefully you can copy it across and work with it. Much appreciated for your help.

 

[Fluff]

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.

 

[JvdV]

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))))))))

 

[Mehmet23]

Hi I'm assuming the country names are in column A and the Customer Id is in column B. You can try typing this formula in any blank cell.

=LET(x,A1:A12,m,UNIQUE(x),IF({1,0},m,COUNTIF(x,m)))

 

[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.

=LET(x,A1:A12,m,UNIQUE(x),IF({1,0},m,COUNTIF(x,m)))

Thanks Mehmet23. Your formula is very close - it counts the total number of customer IDs in each country but not the distinct number of customer IDs. Your formula gives a result of 3 for Argentina but there are 2 distinct customer IDs in Argentina.

 

[Mehmet23]

Ok. Try This.

LET(x,UNIQUE(A1:A12),IF({1,0},x,BYROW(x,LAMBDA(y,SUM(--(y=INDEX(UNIQUE(A1:B12),,1)))))))

 

[oz74]

Ok. Try This.

LET(x,UNIQUE(A1:A12),IF({1,0},x,BYROW(x,LAMBDA(y,SUM(--(y=INDEX(UNIQUE(A1:B12),,1)))))))

Brilliant Mehmet23. Is there any way your formula could produce the result sorted by country in alphabetical order?

 

[Mehmet23]

Like This. I have to tell you this. Mr. JVDV's formula also works well. You must try too.

=LET(x,SORT(UNIQUE(A1:A12)),IF({1,0},x,BYROW(x,LAMBDA(y,SUM(--(y=INDEX(UNIQUE(A1:B12),,1)))))))