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
LVL 11
Wilder1626Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony BerenguelCommented:
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

Wilder1626Author Commented:
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?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Wilder1626Author Commented:
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.
Wilder1626Author Commented:
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
Saurabh Singh TeotiaCommented:
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?
Wilder1626Author Commented:
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

Saurabh Singh TeotiaCommented:
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 AnalystCommented:
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

Wilder1626Author Commented:
pretty quick, both looks good. let me test them both and i'll be back shortly.
Saurabh Singh TeotiaCommented:
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...
Wilder1626Author Commented:
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.
Saurabh Singh TeotiaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wilder1626Author Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.