ptaylor
New member
- Joined
- Jun 11, 2015
- Messages
- 16
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 2016
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.
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":
Export Date | Name | Asset IP Address | Solution |
05/22/17 | John Doe 1 | 10.126.128.30 | Solution 1 |
05/22/17 | John Doe 1 | 204.99.35.118 | Solution 1 |
05/22/17 | John Doe 1 | 192.168.249.100 | Solution 1 |
05/22/17 | John Doe 2 | 192.168.249.73 | Solution 2 |
05/22/17 | John Doe 2 | 10.123.98.135 | Solution 3 |
05/22/17 | John Doe 3 | 10.18.70.29 | Solution 2 |
05/29/17 | John Doe 3 | 10.18.70.29 | Solution 2 |
05/29/17 | John Doe 3 | 10.18.70.29 | Solution 2 |
05/29/17 | John Doe 1 | 10.18.70.29 | Solution 3 |
05/29/17 | John Doe 1 | 10.18.70.29 | Solution 3 |
05/29/17 | John Doe 1 | 10.18.70.29 | Solution 3 |
05/29/17 | John Doe 4 | 10.18.70.29 | Solution 3 |
05/29/17 | John Doe 4 | 10.18.70.29 | Solution 3 |
05/29/17 | John Doe 4 | 10.18.70.29 | Solution 3 |
Worksheet "contents":
5/22/2017 | 5/29/2017 | |||
NAME | FIXES | IP's | FIXES | IP's |
John Doe 1 | 1 | 6 | 1 | 1 |
John Doe 2 | 2 | 2 | 0 | 0 |
John Doe 3 | 1 | 1 | 1 | 2 |
John Doe 4 | 0 | 0 | 1 | 1 |
Any help in the right direction is appreciated. Please and thank you.