Counting unique values across multiple sheets

[Equin0x]

Hi all

I have a workbook that contains 5 worksheets, each has the same layout and structure. I need to give a grand total of unique names in E7:E1000 across all sheets and not count any blank cells.

Sheet1 Summary
Sheets 2-6 The 5 sheets with data

I have this formula

=SUMPRODUCT((Sheet2!E7:E1000<>"")/COUNTIF(Sheet2!E7:E1000,Sheet2!E7:E1000&""))

which works fine for one sheet, but I'm stuck on how I can expand this to all 5 sheets. Using Sheet2:Sheet6!E7:E1000 to create a sheet range didn't work for me.

Ideas and tips appreciated.

Thanks

 

[Eric W]

What version of Excel do you have? (Please update your user profile.)

If you have Excel 365, you can do this:

Extract Unique Value from List More Than 1 Million

Hi, I have a few sheets with a long list of duplicate values and the total list is more than 2.5 million. Example is 5 sheets and each sheets have about 500,000 of values. I need to produce a final list of all 5 sheets unique values , meaning there should not be any duplicates with the total...

www.mrexcel.com

If you have an older version, you may need to use VBA or Power Query. Is that acceptable?

 

[Equin0x]

It's Excel 2016 and yes that is fine.

 

[Joe4]

What version of Excel do you have? (Please update your user profile.)

Here is how to update your profile: Account details (or click your user name at the top right of the forum).
Updating this will allow helpers to know what Excel version(s) & platform(s) you are using from all posts/threads,
This is helpful, as the best solution often varies by version.

 

[Eric W]

I wrote this up as a UDF (User Defined Function). I don't believe that 5000 cells will significantly slow down your sheet, but if it does let me know and I'll rewrite it using a different way of calling it.

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. In the sheet that opens, paste this code:

Public Function UniqueCount(MyRange As String, SumSht As String)
Dim MyDic As Object, sh As Variant, MyDat As Variant, i As Long

    Application.Volatile
    Set MyDic = CreateObject("Scripting.Dictionary")
    
    For Each sh In Worksheets
        If sh.Name <> SumSht Then
            MyDat = sh.Range(MyRange).Value
            For i = 1 To UBound(MyDat)
                If MyDat(i, 1) <> "" Then MyDic(MyDat(i, 1)) = 1
            Next i
        End If
    Next sh
    
    UniqueCount = MyDic.Count
    
End Function

Press Alt-Q to close the editor. Now you can call this function like any other built-in function. Enter

=UniqueCount("E7:E1000","Summary")

where E7:E1000 is the range to check, and Summary is the sheet to ignore. Let us know how this works.

 

[Equin0x]

That works brilliantly, thank you.

 

[Manbjorngris]

I wrote this up as a UDF (User Defined Function). I don't believe that 5000 cells will significantly slow down your sheet, but if it does let me know and I'll rewrite it using a different way of calling it.

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. In the sheet that opens, paste this code:

Public Function UniqueCount(MyRange As String, SumSht As String)
Dim MyDic As Object, sh As Variant, MyDat As Variant, i As Long

    Application.Volatile
    Set MyDic = CreateObject("Scripting.Dictionary")
   
    For Each sh In Worksheets
        If sh.Name <> SumSht Then
            MyDat = sh.Range(MyRange).Value
            For i = 1 To UBound(MyDat)
                If MyDat(i, 1) <> "" Then MyDic(MyDat(i, 1)) = 1
            Next i
        End If
    Next sh
   
    UniqueCount = MyDic.Count
   
End Function

Press Alt-Q to close the editor. Now you can call this function like any other built-in function. Enter

=UniqueCount("E7:E1000","Summary")

where E7:E1000 is the range to check, and Summary is the sheet to ignore. Let us know how this works.

Is there a way to ignore more than one sheet? This code works perfectly otherwise.

Thanks!

 

[Eric W]

Welcome to the MrExcel forum!

This probably is overkill, but try:

Public Function UniqueCount(MyRange As String, ParamArray SumSht() As Variant)
Dim MyDic As Object, IgnSheets As Object, sh As Variant, MyDat As Variant, i As Long, y As Variant

    Application.Volatile
    Set MyDic = CreateObject("Scripting.Dictionary")
    Set IgnSheets = CreateObject("Scripting.Dictionary")
    
    For i = LBound(SumSht) To UBound(SumSht)
        If TypeOf SumSht(i) Is Range Then
            For Each y In SumSht(i).Cells
                IgnSheets(CStr(y.Value)) = 1
            Next y
        ElseIf IsArray(SumSht(i)) Then
            For Each y In SumSht(i)
                IgnSheets(CStr(y)) = 1
            Next y
        Else
            IgnSheets(CStr(SumSht(i))) = 1
        End If
    Next i
        
    
    For Each sh In Worksheets
        If Not IgnSheets.exists(sh.Name) Then
            MyDat = sh.Range(MyRange).Value
            For i = 1 To UBound(MyDat)
                If MyDat(i, 1) <> "" Then MyDic(MyDat(i, 1)) = 1
            Next i
        End If
    Next sh
    
    UniqueCount = MyDic.Count
    
End Function

You can now call this in several ways:

Book2
	A	B	C
1	11	11	11
2
3
4	Sheet3
5	Sheet4
6	Sheet5
7
Sheet5

Cell Formulas
Range	Formula
A1	A1	=uniquecount("C1:C10",A4:A6)
B1	B1	=uniquecount("C1:C10","Sheet3","Sheet4","Sheet5")
C1	C1	=uniquecount("C1:C10",{"Sheet3","Sheet4","Sheet5"})

 

[Manbjorngris]

Thank you so much! It worked perfectly but alas I now realize I need to have all the workbooks be saved in xlsm format.

I just wish there was a simple existing formula that would allow me to count unique values across multiple sheets in the same cell columns.

But thank you so much for taking the time and this function will absolutely save me huge amounts of time in other applications.