Vba counting unique values in two column ranges based on two criteria in other worksh

Hello world,


I'm looking for a VBA solution that will count unique values in two columns from one worksheet (worksheet "Metric") that is based on criteria from another worksheet (worksheet "Contents").


Below is a small sample of the data I'm working with (I'm unable to upload a sample workbook due to workplace restrictions). However I've done my best to explain what I'm trying to achieve both verbally and in the example below. The counts of Worksheet "contents" below in the 'Fixes' and 'IP' columns are based on the date criteria where the date matches in Worksheet "metric"; and on the 'Name' criteria where the name matches in Worksheet "metric". I need unique counts of the 'Asset IP Address' and 'Solution' columns.

I've been using CSE formulaS to get the results I need but because of the overhead involved with these formulas my workbook is taking upwards of 30 mins to re-calculate. The cell formulas I'm using for each name where a count of IP Address and Solution is required is as follows: (Please note I'm using Table Names with [column header names] in the formula for easier dissection).


=SUM(IF(FREQUENCY(IF((METRIC[Solution]<>"")*(METRIC[Export Date]=C$2)*(METRIC[VP]=$B4),MATCH(METRIC[Solution],METRIC[Solution],0)),ROW(INDIRECT("1:"&ROWS(METRIC[Solution]))))>0,1))

=SUM(IF(FREQUENCY(IF((METRIC[Asset IP Address]<>"")*(METRIC[Export Date]=$C$2)*(METRIC[VP]=$B4),MATCH(METRIC[Asset IP Address],METRIC[Asset IP Address],0)),ROW(INDIRECT("1:"&ROWS(METRIC[Asset IP Address]))))>0,1))


Worksheet "metric":



Worksheet "contents":

Any help in the right direction is appreciated. Please and thank you.

If you have Excel 2013 or later (I don't) you can do this with a straight pivot table. You would have then Name (VP?) in the row labels area and the Export Date in the column labels area, then you'd add the IP Address and Solution fields to the Values area, but for these last two fields instead of accepting the usual Sum or Count, you'd choose the new, last option of Distinct Count. And that would be it.

However, I only have Excel 2010 so:
I think you can get pretty much instant results by adding 2 more columns to the Metric table with the formulas:
=1/COUNTIFS([Export Date],[@[Export Date]],[Name],[@Name],[Asset IP Address],[@[Asset IP Address]])
and:
=1/COUNTIFS([Export Date],[@[Export Date]],[Name],[@Name],[Solution],[@Solution])
These 2 formulae are entered normally (no array-entered formulae involved at all in this offering!)

Then create a pivot table (very straightforward one - it's absolutely bog standard), the Name (VP?) in the row labels area and the Export Date in the column labels area, then you'd add the two new fields to the Values area, using Sum (which I think for numeric fields is the default anyway).

In the attached is such a solution (btw, this is really the kind of file you should have uploaded, to save us the bother of recreating it and guessing wrongly all the unknowns). I suspect 1 of your expected results (6) should be 3.

[/QUOTE]...In the attached is such a solution (btw, this is really the kind of file you should have uploaded, to save us the bother of recreating it and guessing wrongly all the unknowns). I suspect 1 of your expected results (6) should be 3.[/QUOTE]

Thanks p45cal. I believe I indicated in the beginning i wasn't able to upload from work or even email out for that matter. As always, you've come through with a good solution to another sticking point. I'll give this a shot and get back to ya.

Much obliged!

p45cal...

I opted to go with an Access Query to summarize my counts and create an ODBC into excel out of Access, something i should have considered beforehand. Thank you so much for your support and solutions. FYI, the solutions you provided for me previously are working without a hitch!

Thank you again!