Remove Non-subtotal Rows
Nov 11, 2006
I have a large file and I am doing a subtotal here. I want to do further processing of the "subtotal" data (basically, I want to sort the list on the subtotalled sum values) and I would like to know if there is any method to delete all rows except the "subtotal" rows from the sheet other than the method described here, in another post in 2003.
Copy subtotals only - visible cells too complicated [SOLVED]
This works fine but I have the word Total in some of the columns I am sub-totalling.
Also, some of the entries in that column are very long strings to start with and when the subtotal is made by Excel, the details are truncated (probably to 255 characdters) and the word Total is not there.
View 7 Replies
ADVERTISEMENT
Apr 8, 2014
Is it possible to remove duplicates from each separate subtotal group, I have the same value through the spread sheet, but only want it to appear once in each subtotal group.
View 1 Replies
View Related
Jan 27, 2014
I know that there is a way to remove or add subtotal for different fields, how to add subtotal only for the fields that have more than 1 value? I don't want to to subtotal for anything that has only 1 value.
View 2 Replies
View Related
Jul 29, 2009
if you can remove the word total from each cell without deleting the numbers after you copy from a subtotal list?
View 9 Replies
View Related
Jun 28, 2013
I have a sheet with a couple thousand rows of data. I need to calculate the subtotal sum for the first 50 rows of auto-filtered data. I can't seem to figure out the formula that I would need to have to do this automatically. If for example my filtered data the 50th visible row is row 1300, currently i can write a formula "SUBTOTAL(9,Q1:Q1300) but if the filtering changes I would have to go and change the formula to replace Q1300 with QX, where X is the new 50th visible row number. I know there must be at least one way to do it automatically but it seems to be beyond my current skill level.
View 3 Replies
View Related
Feb 10, 2007
i have an excel sheet with columns including city & order value. i want to group rows with the same city name, and then subtotal the order values for each city. how do i do this?
View 10 Replies
View Related
Jun 4, 2007
I would like to insert rows below each of the subtotals in column D and F in the attached sheet. The current code I am using can only add rows according to one column. I don't know how to modify it so that it works on both columns. Also, when rows are added according to column D, the new inserted rows are filled with colors and I don't know how to remove. Lastly, how can I run this marco in multiple sheets?
Sub InsertRows()
Dim i As Integer
Dim rRw As Range
Set rRw = Range("D1")
For i = 1 To WorksheetFunction.CountIf(Columns(4), "* Total")
Set rRw = Columns(4). Find(What:="* Total", After:=rRw.Offset(2, 0), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If rRw.Value <> "Grand Total" Then
rRw.Offset(1, 0).EntireRow.Insert
End If
Next i
Set rRw = Nothing
End Sub
View 5 Replies
View Related
Jun 22, 2012
a huge spreadsheet that is taking up way too much time. It starts out with 6,000-7,000 rows, but then, she does Subtotals & it grows to nearly 10,000 rows! Then she has to deal with each & every one of the Subtotal rows, by hand. Here is a small sample of the spreadsheet, after the Subtotals have been applied:
Manifest Dttm
Manifest No
Carrier Scac
[Code].....
This sheet has columns A-Q. The key columns for our purposes are K (Weight In Lbs), L (Pickup Charge) and M (Consolidation Charge). We need to add information to column R on each SUBTOTAL row only! On each row there will be an amount in EITHER column L or column M, but not both.
1) If the amount is in L and K is LESS than 488 then R needs to show 44.39.
2) If the amount is in M and K is LESS than 124 then R needs to show 3.82.
3) If either
a. The amount is in L and K is 488 or HIGHER or -
b. The amount is in M and K is 124 or HIGHER
THEN R needs to show a formula to do this: (L+M)/(K/100)
Is there a way to either put a formula in just the Subtotal rows to accomplish this or (preferably) have a macro enter either the 44.39, 3.82 or the formula? I was able to come up with a formula that gets the correct amount in col R. Here's my formula:
Code:
=IF(AND(M3=0,K3123))),SUM(L3:M3)/(K3/100))))))
View 6 Replies
View Related
Aug 6, 2009
Any VBA code to delete rows in a subtotalled list?
The subtotal line and the associated rows should be deleted when subtotal equals zero.
View 9 Replies
View Related
Jan 9, 2007
I have a large spreadsheet with 25 columns.
I need a macro to delete prior rows if SUBTOTAL in column K equal zero.
View 12 Replies
View Related
Feb 18, 2009
I posted a thread a while back about how to ignore hidden rows from autofilter when using formulas. http://www.excelforum.com/excel-work...ml#post2039071
The solution worked perfectly. But I have since had to use this on another sheet which is laid out a bit differently, I have tried everything to try and modify the formula to work on the new sheet but so far I have failed miserably!! I need to do the exact same thing on this sheet - filter the sheet by week and search the data for certain criteria ('Line' and 'Fail Reason') and sum up the total quantity, while ignoring autofilters hidden rows. Here is an example of the sheet, along with an attempt of mine to modify the formula, which I think I have got TOTALLY wrong!
View 5 Replies
View Related
Oct 21, 2011
Import Financial System -Recaps Trial BalanceFrom Date:31-Jul-2011,To Date:27-Aug-2011,
Requested By:Jenny Drumm,30-Aug-2011 9:28 AMAge Range: 0 - 30, No. of recaps: 231
Broker File NumberAgeVendor/Co/DeptFirstBankAssistsFRTBRODTYLoadsInvInvClaimsGL AcctTRUETRUEBalanceCostFeeRecptsAdjRecptsROE VarLC Var
[Code] ........
There are more age groups, & some groups have hundreds of lines. Each of the total line in the report has a number plugged in by the system, which, because of rounding, may be off by several cents. I figured out how to move the rows with Broker File Numer, etc & the row after it up above the first Age Range.
What I need to do next is:Delete all rows between "Age Range: ....." and the start of that range's data.Delete all blank rows between "Totals" and the next "Age Range: ...."Delete 5 rows after "Grand Total"Put a formula in the row immediately above each age range's "Totals" for each column, summing all the data for that column, in that age range. (Hopefully with the word Total in column B of that row, too)Put a formula in the row immediately above the "Totals" after "Grand Total" that adds together each of the Age Range totals.(I did figure out how to get the "Balance" cell in each row to sum that row going right to left, so at least I got a start! For that I'm using
Code:
LR = Range("C" & Rows.count).End(xlUp).Row
With Range("R10:R" & LR)
.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.FormulaR1C1 = "=IF(ISNUMBER(RC3), Sum(RC5:RC17), " & Chr(34) & Chr(34) & ")"
End With
View 2 Replies
View Related
Dec 1, 2008
writing a VBA to convert a set of data in sheet 1 to one in sheet 2(I am enclosing that as a Excel document"Test -Original").I have described what needs to be done ( step by step ) below.
Develop a macro
1. I have a report from SAP BW, the original format of which is in sheet 1.I need to develop a macro using VBA and need the report with the format in sheet 2.
2. I need a “Results “row after every Bill to Party in column A as below(screen shot 1.doc):
The number of customers is dynamic i.e.it keeps changing every month
4. Nothing needs to be done to column.SAP BW will not overwrite the format and the data in column G.Hence leave it as it is.
5. Calculate the number of Sales document numbers for each customer and put the value of 1 for every value. If it is blank it should not be counted and put the value of 0 for those rows. (Shown below) Display the sum of the number of sales document numbers in the results row for column H
6. In the column I, put the value of 1 if the difference column (column G) is 0 and put the value of 0 if the value in the difference column (column G) is any value apart from 0.now sum the value in the results row for each customer and display the summation value in the results row under column I
7. Compute the percentage which is the values in (column I/Column J)*100 .This should be done only for the results row
View 9 Replies
View Related
Feb 5, 2007
way to do this but i have a sheet that is into 5 - 6 thous rows, in one of the columns (names) i sort it by names and then order it by subtotal for certain values.
What i need to know, is there anyway i can take just the subtotal values out and put onto another spreadsheet without copying and pasting it all as there are lots of subtotals and this would help alot as the other info is not nec. just the subtotal'd info. either that or is there anyway i can highlight the subtotal'd row info in yellow/bold text anything like that that would make it stand out without having to do it manually?
View 12 Replies
View Related
Oct 23, 2008
I’m trying to get my sheet so that at each change in month it creates a sum of the value but I want to sum to show up in the subtotal value column.....
View 10 Replies
View Related
Apr 4, 2014
I am using the following code to remove an entire row if a specific cell in that row contains "remove":
iLastRow = Cells(Rows.Count, "D").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Left(Cells(i, "D").Value, 4) = "remo" Then
Rows(i).Delete
End If
Next i
However, from what I can see, it starts from the bottom and look upward, removing each line as it finds it. The problem is, there could be upward of 500 lines that need to be deleted. This coding looks and removes lines one at a time and can take a while. Is there any way I can get this done faster?
During the previous steps of my macro, I sort the column (Column D) that has the "remove" in it, so all of the "remove"s are adjacent to each other in Column D of my file.
View 5 Replies
View Related
Oct 31, 2013
I'm puzzled on coming up with a way to delete a set of rows in Excel. I have two columns like below and would like to remove the rows with the later year. Is there a formula or vba I could use to run on 5000 records?
Current table:
Job Code Id
Resource Source
77224L HIMCO
MCL-INV-SUR12
[Code]...
Desired table: [this would exclude rows where record source = MCL-INV-SUR09
Job Code Id
Resource Source
77224L HIMCO
MCL-INV-SUR12
[Code]...
View 4 Replies
View Related
Mar 29, 2007
How to remove unique rows from excel-table and leave only dublicates? any macros?
View 9 Replies
View Related
Jun 10, 2008
I have a customer database that is sorted by phone number. How do I remove duplicate rows.
View 9 Replies
View Related
Jun 20, 2008
I have data in a 3 column by massive amount of rows (10000+) periodically there is about 6-11 rows of 'header' data which needs to be removed. this is a report run monthly so doing this over and over drain time. I need to create a query that will find all of these rows and delete the entire rows.
View 9 Replies
View Related
Apr 27, 2006
I have a worksheet that collects information from another using complex formulas.
In column A I have codes i.e. PRD001A, PRD001B etc. A block of codes starts at A and can go up to Z. i.e. PRD001 could start WITH PRD001A and finish with PRD001N then there is always a PRD001W and PRD001Z to finish with.
PRD002A would follow PRD001Z and might only have A, B, W & Z codes before PRD003A.
In column K is the sum of columns D:J.
What I want is a macro that will look at column A & K and if all OR some of the codes for say PRD001 are >0 don't delete any of the rows for that group of codes.
Then look at column A & K and if all of the codes for say PRD002 =0 delete all of the rows for that group of codes.
View 9 Replies
View Related
May 15, 2006
Sub Remove_E_H_Ts()
Application. ScreenUpdating = False
Dim x&
For x = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
With Cells(x, 2)
If Left(.Value, 1) = "E" Then ' i need to add If Left(.Value, 1) = "E" or "H" or "T" Then
.EntireRow.ClearContents
End If: End With: Next x ' after clear contents how can i remove blank rows ?
Application.ScreenUpdating = True
End Sub
i am trying to make this line of code work below....
If Left(.Value, 1) = "E" Or "H" Or "T" Then
then also after clear contents how can i remove blank rows ?
View 6 Replies
View Related
May 25, 2006
I have a macro that performs Index&Match formulae on another workbook. This leaves me with some #N/A results. I then require the macro to delete all rows that contain #N/A. I have tried to identify these via
If Range("E" & myLoop).value = "#N/A"
and
If Application.WorksheetFunction.Isnumber(Range("E" & myLoop).Value) = False
but each one gives me a Run Time Error 13 (Type Mismatch) because, even though the results are now values and not formulae, it sees '#N/A' as an error (Error 2042).
View 3 Replies
View Related
Aug 12, 2008
ive got a spreadsheet with records such as this
james - smith - leeds- 01535 - 26/02/1983 - bd21 6ls
steven - smith - bradford - 213789 - 28/01/1982 - bd33 5th
james - smith - leeds - 01535 - 26/02/1983 - bd21 6ls
steven - king - manchester - 213789 - 28/01/1982
andrew - wright - bingley - 36473 - 12/01/1981 - bd23 689
what im trying to do is to check all of the rows for duplicates in such a way that (if any row has the matching colums 1,2,5 and 6 then all but 1 of the rows will be deleted.
i have a few thousand entries and am just trying to get rid of all the duplicate rows. The problem is that the duplicate rows are not IDENTICAL for instance soime of them may have spelling mistakes hence i cannot just do a straight forward if row = row then delete.
View 4 Replies
View Related
Jan 12, 2010
I am attempting to design 2 combo boxs by which you can select from a number of equipment types and a criticality:
Equipment Types Criticality
Vac Pump 1A
Filter Dryer 2A
Valve 3A
I want to attempt to write a macro so that when the equipment type and criticality has been selected, a corresponding number of rows will disappear.
For example:
If Vac Pump and 1A are selected then all the rows from 70 downwards will be hidden, and if Filter Dryer and 2A are selected, then the rows from 6 to 38 and from 47 onwards will be hidden.
View 10 Replies
View Related
Jan 30, 2014
I have a workbook with 7 different Sheets to pull data from. On Sheet 3 named "PMP Mileage Linear". I have 4 drop down boxes. In one drop down labeled "Drop Down 4" there are 21 different choices to choose from. Based on the choice I would like to hide or unhide Rows. Example: if a user chooses "3yr/36,000 Miles". I would like to hide rows 16:51. If a user chooses "3yr/60,000 Miles". I would like to hide rows 29:51 (hence un-hiding 16:28). If a user chooses "5yr/100,000 Miles" I would like only rows 41:51 hidden and so on until a user hits the max of 125,000 Miles (5,6 or 7Yr) whereas no rows are hidden. I would have attached the spreadsheet but I don't have permission from the people who own the data.
View 7 Replies
View Related
Feb 7, 2014
I have a spread sheet which has a range as shown below.
Batch NumberItem 1Item 2Item 3
P155635
P254
P378
P2568
P454
P389
P385
I need to get the output as shown below which is for Batch number P2 I need to include Item 1 (568) in one row and delete the other P2 row. similarly I need to do for P3. The end result will be as shown below.
Batch NumberItem 1Item 2Item 3
P155635
P256854
P3898578
P454
View 8 Replies
View Related
Feb 24, 2014
I can't get rid of the alert "Delete entire sheet row" using DisplayAlerts = False. Here is my code:
[Code] .....
View 4 Replies
View Related
Mar 9, 2014
I need to remove the you tube link which is on some rows
Row 9:
[Code] .......
Attached File : Description.csvā€ˇ
View 10 Replies
View Related
Dec 13, 2007
Im not strong in excel, that why Im here. So:
I work every day with big amount of item numbers and lists in excel and I need some macro or code to automatically remove rows, containing unwanted text.
Example:
MEMORY DIMM 512MB PC3200 DDR
MEMORY DIMM 512MB PC6400 DDRII
MEMORY DRIVE FLASH USB2 2GB
MEMORY MINI SD 2GB W/ADAPTER
MEMORY SECURE DIGITAL 2GB
MEMORY DIMM 1GB PC6400 DDRII
MEMORY DRIVE FLASH USB2 1GB
MEMORY DRIVE FLASH USB2 1GB
So I paste the text from my database, and want to automatically delete rows containing DIMM string. How can I do it?
p.s. is there also a way to leave only the rows I want?
View 9 Replies
View Related
