Count Unique based on two criteria VBA

-=NO=-

New Member
Joined
May 9, 2011
Messages
27
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello Dears

Please if anyone can support in that;

I need to convert the below formula to VBA code,

{=SUM(IF(("City"=Receiving[BRANCH])*('Build Dashboard'!I12=Receiving[CATEGORY LEVEL]), 1/COUNTIFS(Receiving[BRANCH],"City",Receiving[RECEIPT NUMBER],Receiving[RECEIPT NUMBER],Receiving[CATEGORY LEVEL],'Build Dashboard'!I12)), 0)}

The issue that I need a code to make a count for unique values depending on two different criteria, the count will be added in a table according to the input criteria

As per table below; the two criteria are (City & Category) and the unique count range is (Receipt no.).

CityCategoryReceipt no.
XXX222110130683
XXX222110130683
YYX2310124850
YXX2110123413
XXX2110123415
YYX2110123419

Then, the data developed will be copied in the below table
CityCategoryCount
XXX??
YYX??
 
I just make a simple example, If I have many columns in source sheet (not only these 3 columns), how can I edit in your code to fit my table.


Show a sample of data that is truly representative of your actual data.
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Replace with
VBA Code:
  Cells(12, 12).Resize(.Count) = Application.Index(Application.Transpose(Application.Transpose(.Items)), 0, 3)
Thanks Mr. Mohadin but it's stll not working

I made a sample sheet with the correct column numbers, If I may request from you to adjust the code after opening it.

 
Upvote 0
Norton has blocked me from opening your file as it has deemed the site to be unsafe.
 
Upvote 0
Norton has blocked me from opening your file as it has deemed the site to be unsafe.
Try this please

 
Upvote 0
Latest when I try to open file

1616137348141.png
 
Upvote 0
New Link

 
Upvote 0
Hi
Would you please show the result for the uploaded file
Same RECEIPT NUMBER for x & y !!??
Sorry I'm confused
 
Upvote 0
Sorry mohadin for confusing

I shall create a combobox contains all cities, once I select one like X, it shall get the number of transaction done with this city based on its category as (table in Target sheet) which will be "1"
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top