2

I have a list of about 12,000 lines, with project numbers, account managers, create date, status... and so on.. Currently I am making reports every 2 weeks, as pivot tables, and then I make graphs out of them. I know that this can be automated, as I eliminated the pivot tables and replicated the result with countifs. Now I want to be able to do the same thing with VBA, to the point where a user can go in a spreadsheet, hit a button and the most current data will portray. To start with this, I want to explore a little bit of countif in vba.

Let's say that the table looks like this 

 A          |         B       |    C
proj.Number   Account Manager   Status
   123            Person 1       Won
   234            Person 2       Lost
   345            Person 3       Quoted

Currently this is my code, that works fine for countif, but it's without a loop... and I know it can be done somehow

 Dim PersonOne as Range
    Set PersonOne = Range("E2")
 Dim PersonTwo as Range
    Set PersonTwo = Range("E3") 
 Dim PersonThree as Range
    Set PersonThree = Range("E4")

        Range("D2") = "Person 1"
        Range("D3") = "Person 2"
        Range("D4") = "Person 3"

PersonOne = (WorksheetFunction.CountIf(Range("B2", Range("B2").End(xlDown)), "Person 1"))   
PersonTwo = (WorksheetFunction.CountIf(Range("B2", Range("B2").End(xlDown)), "Person 2"))  
PersonThree = (WorksheetFunction.CountIf(Range("B2", Range("B2").End(xlDown)), "Person 3"))

How do I automate this, to the point that I don't even have to write the names of the people (the part where I say range(d2) = some person Can I have a code that looks for all possible unique names, puts them in a certain range of a spreadsheet, and than count how many times that name occurs in the given range?

Thank you

M--
  • 25,431
  • 8
  • 61
  • 93
Damjan
  • 47
  • 1
  • 8
  • 1
    any reason for not using PivotTable and PivotChart? https://support.office.com/en-us/article/Create-a-PivotChart-c1b1e057-6990-4c38-b52b-8255538e7b1c – Slai Apr 28 '17 at 18:26
  • I am the only one making the reports, and about 40 people only view them. If I can make this available for anyone at any time (without them building pivots and charts), it would be easier for everyone. And I like to mess around with things like this, I'm just not too good at it although I have a basic idea of what the code needs to do. I have hard time with loops though... thus the reason why I'm here – Damjan Apr 28 '17 at 18:28
  • What are you doing with the data after you do your Count? You could create an array to pick up all the names then just loop through that. [This question](http://stackoverflow.com/questions/10951687/how-to-search-for-string-in-an-array) has a function in the first answer that checks if an array already contains a string. I can write it all out in an answer if you need it. – BerticusMaximus Apr 28 '17 at 18:31
  • Usually I just make pivot tables out of it (there is more columns, but not relative to the question I asked). Then I make pivot charts that shows certain data. But right here, I just want to know how to have all the names in certain cells, without me manually typing the names in the code. For example the loop goes through column B, finds 5 or 6 names and puts each name in certain cell (lets say A1:A6 on another spreadsheet, while in B1:B6 puts the count of the occurrences). If I understand how to do that, I think I can deal with the other things. – Damjan Apr 28 '17 at 18:35
  • Just set the range of pivot table data till the last row of the sheet. Ignore blanks. And hit RefreshAll in Data tab every time new data comes in. – M-- Apr 28 '17 at 18:38
  • Would this work if the data source is a dynamic data coming out of a CRM? – Damjan Apr 28 '17 at 18:41
  • As long as that data sits in the workbook (at the end, no matter how it got imported) you can just refresh and .. excel does the trick. – M-- Apr 28 '17 at 18:43
  • All right fair enough. Side note, is it possible to do what I asked? And I thought of doing it because so far I don't have a CRM import connection (but I plan to ask for one), so every time I have to export the data and start from scratch. Now that I did it with formulas, I just copy and past the data where the formulas are referenced to, and the same graphs are produced on another spreadsheet. That's why I was thinking of VBA, so people wouldn't mess around with the spreadsheet where the formulas are – Damjan Apr 28 '17 at 18:51
  • 1
    You can automate importing the data and at the end of that you can just say `ActiveWorkbook.RefreshAll` and it does refresh the pivot table. Also you can unlock all the cells, lock those that you don't want the users to mess with and protect the sheet. – M-- Apr 28 '17 at 19:10
  • 1
    And Just one thing: Avoid writing a code which can be already done in excel (or any software) itself. It's gonna be slower and sometimes wrong and of course time consuming. – M-- Apr 28 '17 at 19:12

3 Answers3

1

That's how you can apply it only using Pivot Table:

First: Insert Pivot Table/Chart

Insert a pivot table with all the columns that you want:

enter image description here

Second: Filter Blanks

Then you can add your desired field to the pivot table. Add one of the columns as filter to ignore blanks:

enter image description here

Third: Append Data

Later you can append data at the bottom of your table:

enter image description here

Fourth: Refresh

Refresh the workbook:

enter image description here

And you're all set:

enter image description here

M--
  • 25,431
  • 8
  • 61
  • 93
  • I know how to use pivot tables, and refresh them... but the question that I asked is not regarding pivot tables... I am trying to stay away from them. What you provided is not the answer of my question – Damjan Apr 28 '17 at 19:10
  • @Damjan Don't get offended. I am not implying that you dunno. Other people would have similar question and they will use this. Again, you don't need vba for this task. But if you insist on using it, write up a code and share it with us and will be happy to help you if you ran into an error. – M-- Apr 28 '17 at 19:19
  • thanks for helping. I didn't get offended,.. I'm just frustrated.. sorry about that. Can a code write all the names in cells A1:A3 on another spreadsheet (assuming that this list was long it would have more than 3 names and there will be duplicates, but I need only one of each names), and next to it in column B, it would show the number of occurrences. That's all I'm asking – Damjan Apr 28 '17 at 19:33
1

The below code and function should do what you need. Although it currently prints to column D and E on the same page but you can easily change that if you want it somewhere else. 

Sub CountIF()

    Dim wbk As Workbook
    Dim ws As Worksheet
    Dim myNames() As String
    Dim lRow As Long, x As Long
    Dim Cell As Range
    Dim Test As Boolean

    Set wbk = Workbooks("Book1.xlsm") 'Change this to your workbook name
    Set ws = wbk.Worksheets("Sheet1") 'Change this to your worksheet name

    ReDim myNames(0 To 0) As String

    With ws
        lRow = .Range("B" & .Rows.Count).End(xlUp).Row
        'Loop through Column B and populate array
        For Each Cell In .Range(.Cells(2, "B"), .Cells(lRow, "B"))
            'Check if Name is already in array
            Test = IsInArray(Cell.Value, myNames) > -1
            If Test = False Then
                'Insert name into array
                myNames(UBound(myNames)) = Cell.Value
                ReDim Preserve myNames(0 To UBound(myNames) + 1) As String
            End If
        Next Cell

        ReDim Preserve myNames(0 To UBound(myNames) - 1) As String
        'Print title in D and value in E
        For x = LBound(myNames) To UBound(myNames)
            'Use x + 1 because our array starts at 0
            .Cells(x + 1, "D").Value = myNames(x)
            .Cells(x + 1, "E").Value = WorksheetFunction.CountIF(.Range(.Cells(2, "B"), .Cells(lRow, "B")), myNames(x))
        Next x
    End With

    Erase myNames

End Sub

The code uses this function so be sure to include it

Function IsInArray(stringToBeFound As String, arr As Variant) As Long
'http://stackoverflow.com/questions/10951687/how-to-search-for-string-in-an-array
'Boolean = (IsInArray(StringToFind, ArrayToSearch) > -1)
    Dim i As Long
    ' default return value if value not found in array
    IsInArray = -1

    For i = LBound(arr) To UBound(arr)
        If StrComp(stringToBeFound, arr(i), vbTextCompare) = 0 Then
            IsInArray = i
            Exit For
        End If
    Next i
End Function
BerticusMaximus
  • 705
  • 5
  • 16
0

A bit late, but if you Record Macro of Data > Consolidate, you get something like:

Range("E1").Consolidate Sources:=Range("B:C").Address(, , xlR1C1, 1), Function:=xlCount, _
                        TopRow:=True, LeftColumn:=True, CreateLinks:=False

enter image description here

Slai
  • 22,144
  • 5
  • 45
  • 53