71727

EXCEL VBA: how to count scenarios?

Question:

I am finishing a project and seems like the last part is the most difficult.

I have 7 (6 + 1 optional) columns that have this sort data (some of them have only earlier/later/na, and some of them earlier/later/equals/na). For example three rows:

OK OK No Yes Earlier Earlier N/A OK OK No Yes Earlier Earlier Earlier OK Missed Yes Yes Later Later Earlier

These can end in 13 different scenarios (if it's "ok ok no yes earlier earlier n/a" would come to for example "a = a + 1"). What I need is to actually count how many of each scenario happened (from "a" to "m"). Also for example if the first three columns are "OK OK OK" I dont need to consider the following conditions and straight add it to f.e. b = b + 1 and go to the next row.

My question here is how efficiently I can do that having in mind that I will have more than 50,000 rows? I understand that I could do that with IF, but I would just get lost in all the if's and I believe this will take a lot of time for macro to run through all the scenarios.

I appreciate all your help and support.

Answer1:

OK this is a starter using the Excel SubTotal function within VBA

It has assumptions built-in to the code which you may want to change, including that the 'solution' is currently in the same sheet as the data (currently in a sheet called "Scenarios", starting in col A and row 7). This works with a limited amount of data but to 50k rows worth! You can add code to summarise the statistics as you wish and remove the subtotals. It leaves the original data intact.

Sub scenarios() Dim ws As Worksheet Dim strow As Long, endrow As Long, stcol As Long, endcol As Long Dim r As Long, c As Long Dim newstr As String Dim cl As Range, rng As Range, drng As Range Dim strArr() As String strow = 7 stcol = 1 'Col A endcol = 7 '7 variables Set ws = Sheets("Scenarios") With ws 'find last data row endrow = Cells(Rows.Count, stcol).End(xlUp).Row 'for each data row For r = strow To endrow newstr = "" 'produce concatenated string of that row For c = stcol To endcol newstr = newstr & .Cells(r, c) Next c 'put string into array ReDim Preserve strArr(r - strow) strArr(r - strow) = newstr Next r 'put array to worksheet Set drng = .Range(.Cells(strow, endcol + 4), .Cells(endrow, endcol + 4)) drng = Application.Transpose(strArr) 'sort newly copied range drng.Sort Key1:=.Cells(strow, endcol + 4), Order1:=xlAscending, Header:=xlNo 'provide a header row for SubTotal .Cells(strow - 1, endcol + 4) = "Header" 'resize range to include header drng.Offset(-1, 0).Resize(drng.Rows.Count + 1, drng.Columns.Count).Select 'apply Excel SubTotal function Selection.Subtotal GroupBy:=1, Function:=xlCount, Totallist:=Array(1) End With End Sub

Recommend

  • Regular expression from font to span (size and colour) and back (VB.NET)
  • How far back can you go back with the Twitter API
  • How to find the first character in a given string, that appears only once [closed]
  • Fixed Background Works in Chrome but Not Firefox?
  • Basic defensive programming [duplicate]
  • Responsive Form on top of Responsive Image? - Bootstrap
  • Is there a way to set up a fallback for the formAction attribute in HTML5?
  • How to open html table in xls on click of a button
  • jquery validation - waiting for remote check to complete
  • Can I use AllJoyn Framework for Wifi Direct in iOS?
  • Enabling DTD support in Sql Server
  • How do I display a dialog that asks the user multi-choice questıon using tkInter?
  • Suppressing passwd when calling sqlplus from shell script
  • Installing Apache MyFaces 2 on WildFly 8.2.0
  • Hardware Accelerated Image Scaling in windows using C++
  • jQuery .attr() and value
  • Azure Cloud Service Web Role web pages do not load
  • Excel - Autoshape get it's name from cell (value)
  • Check if a string to interpolate provides expected placeholders
  • WinForms: two way TextBox problem
  • Perl system calls when running as another user using sudo
  • Can I make an Android app that runs a web view in Chrome 39?
  • Timeout for blocking function call, i.e., how to stop waiting for user input after X seconds?
  • Calling of Constructors in a Java
  • SVN: Merging two branches together
  • RestKit - RKRequestDelegate does not exist
  • Hibernate gives error error as “Access to DialectResolutionInfo cannot be null when 'hibernate.
  • Traverse Array and Display in markup
  • Transpose CSV data with awk (pivot transformation)
  • Data Validation Drop Down Box Arrow Disappearing
  • How to include full .NET prerequisite for Wix Burn installer
  • Revoking OAuth Access Token Results in 404 Not Found
  • Proper folder structure for lots of source files
  • Acquiring multiple attributes from .xml file in c#
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • How can I remove ASP.NET Designer.cs files?
  • java string with new operator and a literal
  • Net Present Value in Excel for Grouped Recurring CF