Filter out entire rows if group value is below 10
I am trying to remove rows from a spreadsheet in VBA if the sum total of value exceeds a specific amount.
For example, if I have the following data, names in A1 down and values in A2 down:
I would like to remove all rows where the total sum of the value in row A does not reach 10 or above in row B, this would leave the following results:
Thomas = 18 and John = 15 so all rows with Thomas and John are kept.
All other rows would be deleted.
Please note that I will always know that the data is in row A and B but I do not know how many rows there will be and need to execute until the first blank cell.
It worked. You can see this here:
Sub run() Dim rowIndex, countSameRow, sumSameRow As Integer sumSameRow = Cells(1, 2) rowIndex = 2 countSameRow = 1 While IsEmpty(Cells(rowIndex, 1)) = False If (Cells(rowIndex, 1) = Cells(rowIndex - 1, 1)) Then sumSameRow = sumSameRow + Cells(rowIndex, 2) countSameRow = countSameRow + 1 Else If (sumSameRow < 10) Then Rows(rowIndex - 1 & ":" & rowIndex - countSameRow).Delete rowIndex = rowIndex - countSameRow End If countSameRow = 1 sumSameRow = Cells(rowIndex, 2) End If If IsEmpty(Cells(rowIndex + 1, 1)) Then If (sumSameRow < 10) Then Rows(rowIndex & ":" & rowIndex - countSameRow + 1).Delete End If End If rowIndex = rowIndex + 1 Wend End Sub
Filter by using advanced criteria - Excel, If the Excel data you want to filter requires complex criteria you can filter by using The Sort & Filter group on the Data tab Multiple criteria, multiple columns, all criteria true When two values are compared by using these operators, the result is a logical Using the example, click any cell in the range A6:C10. On� (5) Check the Select entire rows option. Note: If the list you will filter in original table has different title from the title of given list, please do not select the titles in both Find values in box and According to box. 3. Click the Ok button in the Compare Ranges dialog box. And then a dialog box pops out to show how many rows have been
Totally agree you should write your own code first, but I couldn't help but write some starting code for you. See if the below fits your purpose:
Sub Test() Dim lr As Long, x As Long Dim arr As Variant, rng As Range Dim dict1 As Object: Set dict1 = CreateObject("Scripting.Dictionary") Dim dict2 As Object: Set dict2 = CreateObject("Scripting.Dictionary") With Sheet1 'Change according to your sheets CodeName 'Get all of your data form A:B in memory (array) lr = .Cells(.Rows.Count, 1).End(xlUp).Row arr = .Range("A1:B" & lr) 'Step through the array and fill up our two dictionaries For x = LBound(arr) To UBound(arr) If dict1(arr(x, 1)) <> "" Then dict1(arr(x, 1)) = Join(Array(dict1(arr(x, 1)), x & ":" & x), ",") Else dict1(arr(x, 1)) = x & ":" & x End If dict2(arr(x, 1)) = dict2(arr(x, 1)) + arr(x, 2) Next x 'Step through our second dictionary and check if value < 10 For Each Key In dict2.keys If dict2(Key) < 10 Then If Not rng Is Nothing Then Set rng = Union(rng, .Range(dict1(Key))) Else Set rng = .Range(dict1(Key)) End If End If Next Key 'If any where below 10, this Range object has been filled, so delete it. If Not rng Is Nothing Then rng.Delete End If End With End Sub
How can I extract values that are less than 10 from different values in , If I have values in column A in a spreadsheet starting from A1 to A5 as for example 1, 2, 3.5, 4, 6 and I need to extract all values less than 5 in a new column, what is the best method? After that you can remove the zero containing rows using filters. I want to lookup the gene expression btw these groups, compared with� You can apply custom Date Filters and Text Filters in a similar manner. To clear a filter from a column. Click the Filter button next to the column heading, and then click Clear Filter from <"Column Name">. To remove all the filters from a table or range. Select any cell inside your table or range and, on the Data tab, click the Filter button.
Here is another method that uses Autofilter and SUMIF to delete the lines.
This assumes there is a header row, if not then add a row first. It adds a sumif in column C and filters all that is less than 10, then deletes them. Then removes column C again.
Sub filter() Range("C1").Value = "Sum" Lastrow = Range("A" & Rows.Count).End(xlUp).Row Range("C2:C" & Lastrow).Formula = "=sumif(A:A,A2,B:B)" Range("A2").AutoFilter ' add a filter to table ActiveSheet.Range("$A$1:$C$" & Lastrow).AutoFilter Field:=3, Criteria1:="<10", Operator:=xlAnd ' filter all below 10 ActiveSheet.Range("A2:C" & Lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete ' delete them Range("A1").AutoFilter ' remove filter again Columns("C:C").EntireColumn.Delete ' remove column C End Sub
Delete Rows Based on a Cell Value (or Condition) in Excel [Easy , When working with large data sets, you may have the need to quickly delete rows Below are the steps to delete rows based on the value (all Mid-West records): Select any cell in the data; Click on the Data tab; In the 'Sort & Filter' group, in Excel – such as less than/greater than, equal/does not equal, between, top 10,� This tutorial will focus on two easy ways to filter a Dataframe by column value. The following example is the result of a BLAST search. In bioinformatics, BLAST (basic local alignment search tool) is an algorithm for comparing primary biological sequence information, such as the amino-acid sequences of proteins or the nucleotides of DNA and/or
How to Filter Data in a Pivot Table in Excel, You can filter data based on value, label, or using the search box. or item that contains a specific text) or the values (such as filter top 10 items by value or items with a value greater than/less than a specified value). The last field lists all the different values listed in the value area. Group Dates in Pivot Tables in Excel. We can use Pandas notnull() method to filter based on NA/NAN values of a column. # filter out rows ina . dataframe with column year values NA/NAN >gapminder_no_NA = gapminder[gapminder.year.notnull()] 4. How to Select Rows of Pandas Dataframe Based on a list? Also in the above example, we selected rows based on single value, i.e. year == 2002.
5 Reasons why your Excel filter may not be working, It's much better to manually select to be sure you have all of the data included. Note: if you would like to remove blank rows from the filter area just turn on filter, trying to filter on the 'Top 10', 'Above Average' or 'Below Average' values in your list If you can see 'Your file name' - Group you currently have� Hover the cursor over the Number Filters option. This will show you all the number related filter options in Excel. Click on the ‘Less than’ option. In the ‘Custom Autofilter’ dialog box that opens, enter the value ‘200’ in the field.
Filtering and Sorting Data - Using Microsoft Excel, With filtered data, you can then copy, format, print, etc., your data, without below the Sort & Filtering icon in the Editing group and choose Filter. you want to filter, and you will see a list of all the unique values in that column. As pointed out in the comments, slice may be preferred here as per @RoyalITS' answer below if you strictly only want 1 row per group. This answer will return multiple rows if there are multiple with an identical maximum value.