20219

VBA: loop through rows, sum up certain rows if condition is met

Question:

I have been using the forums for a couple of weeks now while learning VBA. But now I am stuck and need help. So here is my problem:

I have a workbook with several worksheets in it. For simplicity let's assume that I have two worksheets. One with a lot of raw data material and one which is nicely formatted and will be the receiving sheet.

The raw data table looks like this: I have 8 columns and I need to check for two conditions in each row (columns B and E) and sum up the values in column H. Column B contains different names and column E contains a working status, e.g. ready or talking. I do have about 3.000 rows and a set of about 20 different names with different status. So overall I have, let's say 100 rows with the name "Smith" and the status "talking" in no particular order. I now need to sum up all the numbers (column H) in each of those rows where the name "Smith" and the status "talking" are put in. After that, I would like to have the name (column B), the status (column E) and the sum (column H), to be written into cells of the same workbook in a dynamic range, starting at I3:K3. I have to admit that I am totaly in the dark on this one.. I really hope that you guys can help me with some code and/or point me into the right direction. Most of all I hope that you could also explain what your code does, so I can understand it properly and learn. Thanks in advance guys!

Here is my code so far:

Private Sub SumNumbers() Dim tbl As Worksheet Dim x As Integer Dim lrow As Long Dim lastname As String Dim astatus As String Dim number As Integer Dim counter As Integer Set wb = ThisWorkbook Set tbl = wb.Sheets("Sheet1") lrow = tbl.Cells(Rows.Count, "B").End(xlUp).Row lastname = tbl.Cells("C2:C" & lrow).Text astatus = tbl.Cells("E2:E" & lrow).Text number = tbl.Cells("H2:H" & lrow) For x = 2 To lrow If lastname = "Smith" And astatus = "Talking" Then counter = counter + number End If Next x End Sub

Answer1:

You're close, but you need to test each cell individually inside your loop. Something like this:

Sub Macro1() Dim tbl As Worksheet Dim x As Integer Dim lrow As Long Dim lastname As String Dim astatus As String Dim number As Integer Dim counter As Integer: counter = 3 Set wb = ThisWorkbook Set tbl = wb.Sheets("Sheet1") lrow = tbl.Cells(Rows.Count, "B").End(xlUp).Row For x = 2 To lrow If tbl.Range("C" & x) = "Smith" And tbl.Range("E" & x) = "Talking" Then Range("I" & counter).Value = Range("C" & x) Range("J" & counter).Value = Range("E" & x) Range("K" & counter).Value = Range("H" & x) counter = counter + 1 End If Next x End Sub <hr />

Per @Tim comment you can use SumIfs to return the total of 'H' column that meet multiple criteria like this:

=SUMIFS(H:H,C:C,"=Smith",E:E,"=Talking")

Answer2:

I found a way that does the job for me so far, but this seems pretty messed up since I have to do it for every case manually. So now I am looking for a simpler and more elegant way to do this. If you could provide me with some code or point me into the right direction, I would highly appreciate it.

Here is my code so far:

For Each cell In Range("H2:H" & lrow) cell.Offset(0, 1).Value = cell.Value + cell.Offset(-1, 1).Value If cell.Offset(0, -6).Value = "Smith" And cell.Offset(0, -3).Value = "Talking" Then cell.Offset(0, 2).Value = cell.Value End If If cell.Offset(0, -6).Value = "Smith" And cell.Offset(0, -3).Value = "Ready" Then cell.Offset(0, 3).Value = cell.Value End If If cell.Offset(0, -6).Value = "Smith" And cell.Offset(0, -3).Value = "Not Ready" Then cell.Offset(0, 4).Value = cell.Value End If Next

As mentioned before, it is a workaround which I would like have to be simplified. It looks for the condition "Smith" and the status "Talking", "Ready" and "Not Ready" and puts the value of the H Column into the respective cells of the columns to it's right to be summed in the later process. So, is there any way to make that more elegant?

Answer3:

Sure thing Portland Runner! My logic is simple. I've got list of recorded activities for each name with certain statusses. e.g. "Smith" (column B) and "Talking" (column C) and a number (column H). And I need to sum up the numbers in column H for every row where the conditions name and status are equal. Let's say I have 10k rows. 2k of them have the name "Smith" in it. But only 500 of them have the name "Smith" and the status "Talking". So I need to figure out a simpler way to sum up those numbers. There are also rows where the name is "Smith" but the status is "Ready". The difficulty here is, that those lines are basically in a random order.

So the table would look like this:

Row Name Status Number 2 Smith Talking 105 3 Smith Talking 67 4 Smith Ready 75 5 Smith Talking 94 6 Jones Ready 89 7 Jones Talking 224 8 Jones Not Ready 75 9 Jones Talking 99

So in this case row number 1 would be blocked for the headline and the data would start in row number 2. For "Smith" and "Talking" the code would need to sum the numbers of rows 2,3 and 5 for the condition "Smith" and "Talking". Rows 4 for "Smith" and "Ready". Rows 7 and 9 for "Jones" and "Talking" and so on.

What I can do, is create a new worksheet and put a list of all names in column A and a list of all statusses into column B and reference to it. That would save me the trouble of using the exact names and statusses in the code, but wouldn't solve my problem.

Answer4:

A. Get all unique Name and place in a new column: this will be your array used to search each row.

B. Set up another array for Status since you know what the status could be.

C. Set up outer loop for Name Array<br /> For each item in array

D. Nested loop look at each row and search on each status

C. When status matches then another nested loop to add time and place in a new column called total time.

Recommend

  • Search contents of node and delete parent node in xml file using vba
  • Vba code for adding information to the right place
  • Excel VBA - Copying Splitted Cell Strings into a new Sheet
  • how to append data to existing excel file using vb.net?
  • VBA - Split cell values and put it into the row below
  • VBA: loop through rows, sum up certain rows if condition is met
  • How to fill out the userform by the product name you typed
  • Subscript out of range error (Error 9): .FormatConditions
  • VBA Multiple loops match conditions
  • Sort macro and data validation macro
  • Sorting parallel arrays in javascript
  • How to specify columns in Swagger
  • What's a better way to swap two argument values?
  • How to use a decaying learning rate with an estimator in tensorflow?
  • Connect Node.js with Oracle on Windows platform
  • Zend framework 2 : Add different authentication adapter for two different modules
  • Lua: Line breaks in strings
  • Is there a way to save the selected text and highlight it again once the page is refreshed?
  • Is it possible to open regedit and navigate to straight to a specific key using process.start?
  • MonoTouch: How to download pdf incrementally as indicated in the Apple slides “Building Newsstand Ap
  • Installing Apache MyFaces 2 on WildFly 8.2.0
  • D3 nodes and links from JSON with nested arrays of children
  • Using jQuery closest() method with class selector
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Array.prototype.includes - not transformed with babel
  • output of program is not same as passed argument
  • Javascript + PHP Encryption with pidCrypt
  • R: gsub and capture
  • How to format a variable of double type
  • Benchmarking RAM performance - UWP and C#
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • Getting Messege Twice Using IMvxMessenger
  • How can i traverse a binary tree from right to left in java?
  • Net Present Value in Excel for Grouped Recurring CF
  • How can I use threading to 'tick' a timer to be accessed by other threads?