VBA : Aggregating Excel cells whose rows contain certain text

Posted on 2004-11-03
Last Modified: 2010-04-17
In VBA, I want to write a Macro that aggregates the values of a column depending on the text values of another column.
For eaxmple, I have data like this:

Name   Salary
RON         5
TOM         6
JO            4
RON          3
RON         2
JO           10

I want to write something in VBA where I take this date and run a macro to generate:

Name      Salary
RON       10
TOM        6
JO           14

In reality, the raw file contains about 800 lines of data and the cells will not always be in the same place.

Any ideas?
Question by:ronanm1
    LVL 6

    Accepted Solution

    Assuming data is in the first 2 columns, and that the result should go to the third and fourth column :

    Public Sub Salary()
      Dim aNames(1 To 100) As String
      Dim aSalary(1 To 100) As Integer
      Dim iNext  As Integer
      Dim bFound As Boolean
      iNext = 1
      For iRow = 1 To 800
        sName = ActiveSheet.Cells(iRow, 1)
        If sName = "" Then Exit For
        iSalary = ActiveSheet.Cells(iRow, 2)
        bFound = False
        For iLoop = 1 To iNext
          If aNames(iLoop) = sName Then
            aSalary(iLoop) = aSalary(iLoop) + iSalary
            bFound = True
            Exit For
          End If
        Next iLoop
        If Not bFound Then
          aNames(iNext) = sName
          aSalary(iNext) = iSalary
          iNext = iNext + 1
        End If
      Next iRow
      For iRow = 1 To iNext - 1
        ActiveSheet.Cells(iRow, 3) = aNames(iRow)
        ActiveSheet.Cells(iRow, 4) = aSalary(iRow)
      Next iRow
    End Sub
    LVL 11

    Expert Comment

    This might be easier:

    ActiveCell.FormulaR1C1 = "=SUMIF(R[-8]C[1]:R[-2]C[1],""Ron."",R[-8]C:R[-2]C)"
    ActiveCell.FormulaR1C1 = "=SUMIF(R[-8]C[1]:R[-2]C[1],""Tom."",R[-8]C:R[-2]C)"
    ActiveCell.FormulaR1C1 = "=SUMIF(R[-8]C[1]:R[-2]C[1],""Jo."",R[-8]C:R[-2]C)"

    You just have to set the variables for the rows and columns, but that's easy!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    INTRODUCTION We all know how to code. But at times you simply want to insert a common code block into your existing code and amend it as per your requirements. This tool not only saves you time but also saves you the pain of typing it all out aga…
    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now