VBA - Countif from another sheet

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)

Select allOpen in new window

alternatively you can also use

=COUNTIF('SHEET 2 NAME'!Q:Q,A11)

Select allOpen in new window

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

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

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

Select allOpen in new window

Saurabh...

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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

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.

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.