We help IT Professionals succeed at work.

VBA - Countif from another sheet

Hello,

I have this sheet 1 that i have multiple duplicated names in column Q.

In my Sheet2 column A starting at row 11, i have a single list of those names.

I need to put the number of time each names are showing in sheet 1 column Q.

How can i do that.

Thanks again for helping.

Sheet1
COUNT-sheet-1.jpg
Sheet2
Result-countif-sheet2.jpg
Comment
Watch Question

enter the following in cell B11 in sheet two. Note you will have to change the names of the sheets in this formula to the names of the sheets in your workbook.
=COUNTIF('SHEET 2 NAME'!Q:Q,'SHEET 1 NAME'!A11)

Open in new window


alternatively you can also use
=COUNTIF('SHEET 2 NAME'!Q:Q,A11)

Open in new window

Is it possible to do this with a Macro? I'm already using the formula in the example i gave.
NorieAnalyst Assistant

Commented:
Use a pivot table?
Sorry Norie but for this analysis file, i cannot use the pivot table option. But you are right, that could of been a very good option.
In my project, the name may be changing and also, i may have up to 65000 records of names in column Q from Sheet 1
Top Expert 2015

Commented:
Wilder,

You can use the following code to do what you are looking for..

Sub counters()

    Dim rng As Range, cell As Range
    Dim r As Range
    Dim lrow As Long, lr As Long
    Dim ws As Worksheet, ws1 As Worksheet

    Set ws = Sheets("Sheet1")
    Set ws1 = Sheets("Sheet2")

    lr = ws.Cells(Cells.Rows.Count, "Q").End(xlUp).Row
    lrow = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set rng = ws.Range("Q2:Q" & lr)
    Set r = ws1.Range("A11:A" & lrow)

    For Each cell In r
        If Trim(cell.Value) <> "" Then cell.Offset(0, 1).Value = Application.WorksheetFunction.CountIf(rng, cell.Value)

    Next cell

End Sub

Open in new window


Saurabh...
NorieAnalyst Assistant

Commented:
Why can't you use a pivot table?

Is it the changing amount of data?
No, The excel file structure is changing from time to time.

I'm almost with the final result.

So far, i have:
Private Sub CommandButton2_Click()
    Dim rng As Range, cell As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
  
        Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")

     lr = ws1.Cells.Find(what:="*", searchorder:=xlRows, searchdirection:=xlPrevious).Row
     Set rng = ws1.Range("Q2:Q" & lr)

    For Each cell In rng
       ws2.Range("B" & cell.Row) = Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("Q2:Q1000"), ws2.Range("A" & cell.Row))
    Next cell
 End Sub

Open in new window

Top Expert 2015

Commented:
Alternatively you can use the following code as well..If you don't want to apply loop...

Sub counters()

    Dim rng As Range, cell As Range
    Dim r As Range
    Dim lrow As Long, lr As Long
    Dim ws As Worksheet, ws1 As Worksheet

    Set ws = Sheets("Sheet1")
    Set ws1 = Sheets("Sheet2")

    lr = ws.Cells(Cells.Rows.Count, "Q").End(xlUp).Row
    lrow = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set rng = ws.Range("Q2:Q" & lr)


    ws1.Range("B11:B" & lrow).Formula = "=COUNTIF(" & ws.Name & "!" & rng.Address & ",A11)"
    ws1.Range("B11:B" & lrow).Value = ws1.Range("B11:B" & lrow).Value


End Sub

Open in new window

Rodney EndrigaData Analyst

Commented:
You can try this code:
Sub ee_CountIf()
Dim rng As Range, rng2 As Range, l As Long, ws As Worksheet, ws2 As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")
l = ws.Cells(Rows.Count, "Q").End(xlUp).Row
Set rng = ws.Range("Q2:Q" & l)

Set ws2 = ActiveWorkbook.Sheets("Sheet2")
l = ws2.Cells(Rows.Count, "D").End(xlUp).Row
Set rng2 = ws2.Range("D11:D" & l)

For Each cell In rng2
    ws2.Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Value = Application.WorksheetFunction.CountIf(rng, cell.Value)
Next cell
End Sub

Open in new window

pretty quick, both looks good. let me test them both and i'll be back shortly.
Top Expert 2015

Commented:
Wilder1626,

Basis of my experience in large data sets where i have applied macro's... The second one will be slow..Comparatively the first one...since in the second one i'm triggering excel calculations of all the range in 1 go and excel at times go crazy or takes good amount of time depending upon your computer configuration and other things as well when you do that..

In first macro i take each cell at a time..and then apply  a formula so i don't trigger all the calculations in 1 go..

Saurabh...
I get what you are saying. Make sense!

I just found out that i'm missing 1 small detail on my end, that i should of talked about at the beginning.
I need to also take column K from sheet 1 + Column Q.

Ex:
Column K                     Column Q
F2345                             CARL
F2345                             CARL


Since column K and Q are the same, it should count only 1 for Carl.
Top Expert 2015
Commented:
You can use the following code..What it will do it will check unique values basis of Column-K and Column-Q and will give you a count of only unqiue values....

Sub counters()

    Dim rng As Range, cell As Range
    Dim r As Range
    Dim lrow As Long, lr As Long
    Dim ws As Worksheet, ws1 As Worksheet

    Set ws = Sheets("Sheet1")
    Set ws1 = Sheets("Sheet2")

    lr = ws.Cells(Cells.Rows.Count, "Q").End(xlUp).Row
    lrow = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set rng = ws.Range("Q2:Q" & lr)
    Set r = ws1.Range("A11:A" & lrow)

    For Each cell In r
        If Trim(cell.Value) <> "" Then cell.Offset(0, 1).Value = countunqiue(rng, -6, cell.Value)

    Next cell

End Sub

Function countunqiue(r2 As Range, k As Long, st As String)
    Dim c As Range
    Dim col As New Collection

    On Error Resume Next
    For Each c In r2

        If UCase(Trim(c.Value)) = UCase(Trim(st)) Then

            col.Add c.Offset(0, k).Value, CStr(c.Offset(0, k).Value)
        End If

    Next c

    countunqiue = col.Count


End Function

Open in new window

Hi everyone

I did the test last night with about 124000 records in the sheet and i must say that the execution time was very impressive with Saurabh Singh Teotia.  Since i will always have huge files to work with, i must go with that solution.

Thanks again to all for your time.