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

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,344
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

-=NO=-

New Member
Joined
May 9, 2011
Messages
27
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,344
Office Version
  1. 365
Platform
  1. Windows
Norton has blocked me from opening your file as it has deemed the site to be unsafe.
 

-=NO=-

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

ADVERTISEMENT

UP please
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,344
Office Version
  1. 365
Platform
  1. Windows
Latest when I try to open file

1616137348141.png
 

-=NO=-

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

-=NO=-

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

 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,092
Office Version
  1. 2013
Platform
  1. Windows
Hi
Would you please show the result for the uploaded file
Same RECEIPT NUMBER for x & y !!??
Sorry I'm confused
 

-=NO=-

New Member
Joined
May 9, 2011
Messages
27
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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"
 

Forum statistics

Threads
1,136,323
Messages
5,675,092
Members
419,549
Latest member
EliteBeat

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
Top