VBA count the number of unique values
excel-vba count unique values based on criteria
excel vba get unique values from column
excel vba count unique values in range
excel count unique values
excel vba number of unique values
excel vba sum unique values
excel assign number to unique values
I looked into the forum and I cannot really find the right answer to my problem. I have a list which looks like this:
SS_ID 1 T1 SS_ID 1 T2 SS_ID 1 T2 SS_ID 1 T3 SS_ID 4 T1 SS_ID 4 T5
There is the 1st column which has IDs. The thing is that in the second column I have also ID numbers which are related with the 1st column.
So the list is in a sheet in Excel and I am trying to find with the help of VBA the number of Users from the 2nd column who are related to the 1st column. If by any chance there is a solution without VBA please feel free to help me. But as far as I can see it I only see the solution in VBA.
FYI I generate the list from a macro.I also found a way to remove the duplicates without deleting the rows which is necessarily in my case.
To Sum up, I need the sum of "unique" values from the 2nd column which are related to the 1st column. It is like a countif function but a little more complicated.
The result should look like this:
SS_ID 1 = 3 SS_ID 4 = 2
I am completely new to VBA.
No need for vba unless you absolutely require it.
=SUMPRODUCT((A$2:INDEX(A:A, MATCH("Z", A:A))=D2)/(COUNTIFS(B$2:INDEX(B:B, MATCH("ZZZ", A:A)), B$2:INDEX(B:B, MATCH("ZZZ", A:A)), A$2:INDEX(A:A, MATCH("Z", A:A)), D2)+(A$2:INDEX(A:A, MATCH("Z", A:A))<>D2)))
Count Unique Values Using VBA in Microsoft Excel 2010, In this example, we want to count the number of unique numbers in each row. ArrowRaw. We have created “CountUniqueValues” custom function Sub CountUnique() Dim Count, x, a, lastRow, Values(), StringValues a = ActiveCell.Column a = GetLetterFromNumber(a) lastRow = Range(a & Rows.Count).End(xlUp).row Count = 0 For Each c In Range(Range(a & "1"), Range(a & Rows.Count).End(xlUp)) If c.row = 1 Then ReDim Values(lastRow) Values(Count) = c.Value Count = Count + 1 End If StringValues = Join(Values, "#") StringValues = "#" + StringValues If InStr(1, StringValues, c.Value) = 0 Then Values(Count) = c.Value Count = Count + 1 End If Next c
Solution without VBA
You can use something like that: =SUMIFS(C1:C6;A1:A6;"=SS_ID 4")
The excel funtion "SOMMA.PIU.SE" in the attached image is "SUMIFS" in english. The SUMIFS function adds all of its arguments (A1:A6) that meet multiple criteria (A1:A6 => "=SS_ID 4").
How to Count Unique Values in Excel [6 Methods] using Formulas + , In Excel, you can use different methods to get a count of unique values. How to Count Unique Values in Excel [Formulas + VBA] You want to count unique values from that list so that you can get the actual numbers of values which are Distinct Values Function. A VBA Function that will return an array of the distinct values in a range or array of input values. Excel has some manual methods, such as Advanced Filter, for getting a list of distinct items from an input range.
Here is a macro-based solution:
Sub UniqueTable() Dim i As Long, N As Long, c As Collection, M As Long Dim j As Long, vd As String, va As String Columns("A:A").Copy Range("D1") ActiveSheet.Range("D:D").RemoveDuplicates Columns:=1, Header:=xlYes Range("E1").Value = "# of unique values" N = Cells(Rows.Count, "D").End(xlUp).Row M = Cells(Rows.Count, "B").End(xlUp).Row For i = 2 To N Set c = Nothing Set c = New Collection vd = Cells(i, "D").Value For j = 2 To M va = Cells(j, "A").Value If va = vd Then On Error Resume Next c.Add Cells(j, "B").Value, CStr(Cells(j, "B").Value) On Error GoTo 0 End If Next j Cells(i, "E").Value = c.Count Next i End Sub
You can easily modify the code to place the little results table anywhere you like. If you add/remove/modify items in cols A and B, just rerun the code.
Counting Unique Values in a Column (with VBA), I'm looking for a way that I can tell Excel to look through G:G (the total number of rows needs to be dynamic) and return the number of unique 2 issues. #1 - this is pasting data into sheet, as opposed to saving it in a VBA variable. #2 - It is looking at formulas instead of actual values (for me, instead of pasting unique values in the column, it only pasted one common formula into one cell).
Count the number of unique values in a column with VBA, Excel > VBA > User Defined Function > Count unique distinct values in a This article describes how to count unique distinct values in list. To count the number of unique or distinct values in a range of cells (named "data" in the example below), you can use a formula based on the COUNTIF and SUMPRODUCT functions. How the formula works Working from the inside out, COUNTIF looks inside the data range and counts the number of times that each individual value appears in data .
Count unique distinct values in a large dataset [UDF], Sometimes you need to know the number of unique values in a range of cells. For instance, suppose that an instructor was teaching the Count the number of unique values in a column with VBA. How to use the VBA Dictionary. Quickly find the number of unique values. Code Sub count_unique() Dim Customer_Num As Range Dim List As
Counting Unique Values (Microsoft Excel), excel-vba Count Unique values in Range. Example#. Function countUnique(r As range) As Long 'Application.Volatile False
- What would be the output you want to get? I mean, You explained your problem and also you posted some data as example. What would be the correct answer with the data you posted?
- Hey I edited my post. i hope it helps to understand it a little bit more
- Are you sure
SS_ID 1 = 2? should not it be
SS_ID 1 = 3? I ask these because
SS_ID 1is related to
T3. Am I right?
- @DavidMorocutti Would not it be
SS_ID 1 = 3then? Since it has
- Yes sorry so stupid of me......
- Thank you very much i will try to implement it into my macro :)