Delete Multiple Rows But Not With Max Volume Of Business
May 8, 2007
I am currently doing some work which is taking ages and i wonder if there might be a quicker way of completing the work.
Each Row contains a User ID alone with the volume of business submitted under that user ID via a specific payment route, the problem i have is that i may have 10 of the same user ID's but each with a different business volume and payment route attached, I have already sub totaled by User ID so tat can see total business volume for each user however, I want to delete all the duplicate user IDs leaving only the row with the max volume of business attached.
I have approx 40,000 user ID's so i need the solution look for sets of User IDs and leave only the one with the biggest volume of business attached to it.
View 9 Replies
ADVERTISEMENT
Aug 18, 2013
I have the following formula in a cell
=NETWORKDAYS(A2,B2)-(MOD(A2,1)>=MOD(B2,1))
its works off of cells A2 for the start date and cell B2 for the end date and works perfect
I want to use this in code to automatically count business days for all rows. I have tried the following
Dim ressfp,a4,a5
a4 = Worksheets("Master").Range("O" & b).Value 'target comp date
a5 = Worksheets("Master").Range("P" & b).Value 'actual comp date
ressfp =NETWORKDAYS(a4,a5)-(MOD(a4,1)>=MOD(a5,1))
I get an error on the word MOD saying compile error syntax error but cant work out why
i have tried replacing the networkdays formula with the following
=SUM(INT((WEEKDAY(A2-{2,3,4,5,6})+INT(B2)-INT(A2))/7))-(MOD(A2,1)>MOD(B2,1))
but this gives the error that the curly brackets are invalid characters.
View 2 Replies
View Related
May 10, 2012
I need a macro that can look at multiple (say three) rows and delete those rows if they match another three rows in the worksheet.
For example:
1 0
3 1
5 7
4 4
6 5
8 3
1 8
5 2
3 9
6 5
8 3
1 8
7 5
If three rows are the same, then one set is deleted (it can be either bottom or top set). I would like to do this for rows 1-500 in the worksheet.
View 1 Replies
View Related
Sep 29, 2011
Need to create a macro?
Delete rows where multiple column meets multiple criteria.
detail:
delete rows where
Column H is less than 10000
AND
Column C is empty(blank)
Those 2 criteria have to occur at the same time..
View 1 Replies
View Related
Oct 29, 2008
I need to find all rows that have columns that match in all 3 of the columns.
I then need to delete all but the last row in each "group" of rows.
like:
1 q w e
2 q w r
3 q w r
4 q w r
Delete rows 2+3
View 9 Replies
View Related
Sep 20, 2009
I have a lot of information for various contacts, in the following format:
Name: John Smith
Address: 123 Spring Street
Phone: 555-5555
Email: example@example.com
Name: Jane Doe
Address: 456 Elm Street
Phone: 555-5555
Email: example2@example.org
Name: Richard Roe
Address: 10 Bridge Street
Phone: 444-4444
Email: example3@example.org
What I am trying to do is convert that information so that it looks in a spreadsheet as follows:
example.jpg
I feel fairly sure this is the kind of thing Excel is capable of doing, but despite much searching and experimenting can't figure out how to do.
View 4 Replies
View Related
Sep 8, 2009
i have several student ID results that have conditional formatting in place. I'd like to KEEP the following data but delete/hide everything else:
1) Filter out purple rows
2) WITH filtered rows: KEEP any scoreA that's 100% or less than 85%
3) Look at Score A and Score B - KEEP if the difference is greater/less than 5%
4) If a student took testB but didnt take testA, KEEP.
View 2 Replies
View Related
Jul 20, 2009
I need help with the following conditions. I have attached a sample file with desired results.
Conditions required to delete row:
1Delete Row when cell E is blank (example: Row 6 will be deleted as E6 is blank)
2First character in column 'C' is an alphabet (for example: Row 5 will be deleted as the first character in the cell is an alphabet
3First character in column 'B' is an alphabet (for example: Row 29 will be deleted as the first character in the cell is an alphabet
Special Notes : All characters in column 'B' are not in number format (ex: 555-9999 is general)
There are more than 1500 rows of data that need to be sorted with the above conditions.
View 9 Replies
View Related
Nov 16, 2009
I need a macro to delete rows with spaces greater than 20. I don't know how excel define a space so I'm not sure where to start.
View 4 Replies
View Related
Dec 29, 2012
I'm trying to put together functionality which allows the user to remove cell content within a selected row. I've chosen to remove the cell content rather than to delete the whole row because I need to maintain the 'Input Range'.
The code below is the script which I've put together to clear the cell contents.
Code:
Sub DelRow()
Dim msg
Sheets("Input").Protect "password", UserInterFaceOnly:=True
Application.EnableCancelKey = xlDisabled
Application.EnableEvents = False
msg = MsgBox("Are you sure you want to delete this row?", vbYesNo)
[Code] ........
The code works fine if the user selects one row. However, if the user selects mutliple rows, although the text values are removed from all rows, only the 'Interior Color' is removed from the first rather than all and I'm not sure why.
View 3 Replies
View Related
Apr 22, 2007
One more short macro code I would like to have.
I would like to delete rows on all worksheets in a workbook below where the value "Total" exists in column B (same on all sheets).
View 9 Replies
View Related
Aug 1, 2008
I need to eliminate the passing jobs, leaving the failing ones so I can do coaching sessions with the guys that are missing the mark.
Conventions of the report:
Each job has a 20 digit job # and is allocated 12 rows worth of information fields unless customer has more than 1 cable outlet with a modem, then the rows increase by 4 for each additional modem
Tech name is repeated after every 12 rows even if within the same job #
On passing jobs, column I will only contain “Yes” (there are blank cells in column I, as not every parameter is checked on every job), failing jobs will contain “No” in column I
Items I need to accomplish:
· Increase column C width to 22 and column E to 25
· Format column C to number, 0 decimal places (to avoid scientific notation)
· Move tech name to beginning of each job # and eliminate repeated names
· Delete all rows plus tech name on passing jobs
· Change cell color to red in rows (columns A-I) that contain “No” in column I
I have highlighted each different job # in different color
John Smith10002934422610200000Outlet 2D - Up Stream SNR274029.4Yes10002934422610200000Outlet 2D - Up Stream Tx365546Yes10002934422610200000Outlet 2D - Down Stream .............................
View 10 Replies
View Related
May 30, 2009
Does anyone have codes to insert and delete multiple rows. I need to run a macro where a dialog box pops up requesting number of rows to insert and delete.
View 9 Replies
View Related
Feb 23, 2011
how they can be used to delete rows that meet multiple criteria, however all the examples I have found seem to be rather complex and personal to the individuals as opposed to being rather generic. Many people, including myself would find it useful to have a simplified generic answer that can be applied to all kinds of criteria. Such as if the data in column B = x and the data in column C = y delete the row. Here is my example.
SCENARIO EXAMPLE:
In column A - Date e.g. 05/02/2011, 06/02/2011
In column B - Day
In column C - Time (in format 09:06:21)
In column D – Service numbers dialled (70, 110, 224) two to three digits long
MACRO EXAMPLE:
Delete the row if the criteria in:
Column D = 10, 40, 192 or 244
And
Column C is between 09:00:00 and 16:00:00
macro code or at least a link to a thread that they think gives a scenario as simple as this.
View 5 Replies
View Related
May 22, 2014
ertret.jpg
I am trying to delete rows that contain the same addresses BASED ON COLUMNS.
Do you see how in columns C and D they all match EXCEPT for row 4? How do I delete allllll the rows that when c and d match.
I want to keep the ones that DO NOT MATCH.
P.S I HAVE 4000 ROWS I WOULD HAVE TO GO THROUGH
View 1 Replies
View Related
Apr 2, 2014
I have the following code to delete rows based on the value "No" in cloumn "L". This code should loop through all sheets and delete the corresponding rows.
[Code]....
The code works perfectly but with one little issue. It fails in the first run always with the following error message.
VBA error.png
I need to comment out the line
[Code] ....
continue it with a breakpoint to "End With", remove the comment. Then it loops all worksheets and deletes the rows.
There is no protection or similiar on any sheet.
View 1 Replies
View Related
Dec 23, 2009
I am trying to use the code below to eliminate data in a spreadsheet based on certain criteria. I have been able to get this to work with text fields just fine, but now I am running into an issue with numbers. I am trying to get this code to delete rows if they have the word "unknown" in column C and if the value in column B is greater than 900 hours (999:00:00), the numbers in this column are formatted as [hhh]:mm:ss. What should I tweak to make this work?
View 7 Replies
View Related
Oct 31, 2012
Here is the problem: i have two conditions to satisfy first is data from column A and second is data from column C, what i wanted to do is if both data from column A and B has the same other data in column A and C then sum the total in column D, F, I retain data for column B, E, G and H. Then delete the duplicate rows. By the way we also need to replace the remove the text (W1,W2,W3,W4 and W5) in column C.
View 6 Replies
View Related
Apr 4, 2007
Is it possible to delete multiple rows when the worksheet is protected using vba? For Example Peps - Select a undetermined amount of rows and then use a macro to unprotect - delete the selected rows - protect.
View 5 Replies
View Related
Feb 29, 2008
I have an extremely large CSV file that I am opening via Excel. Can anyone tell me how to delete a row using multiple criteria. For Example, i want to delete the whole row if a certain cell doesnt equal GA SC or NC. I have seen how it can be done using one criteria but not multiple ones.
View 8 Replies
View Related
Jun 5, 2014
Maybe this has been taken care of in another thread, but still:
ROW ID NUM OCC K OCC I
1 222222 0 0
2 222222 0 0
3
4 333333 0 0
5 333333 0 0
6 333333 0 0
7
8 444455 1 1
9 444455 1 1
I have a situation like above, but what I want to do is:
=FOR(ROW1:ROW9, IF COUNT ROWS(ID_NUM)>= 2,
(create name for set?)
AND
IF OCC_K AND OR OCC_I VALUES >= 0,
THEN DELETE ROWS(within set) >=2
Or more simply:
If there are rows with multiple Accident ID numbers, name that set,
and if OCC_K and/or OCC_K values are BOTH >= 0,
then delete all duplicate rows?
Yes I figure there needs to be some way to distinguish Row 1 from Row 2, (flagging one of the two?) so that ROW 2 can be identified, and similarly with Row 4 from Row 5 from Row 6, but the general flow of commands is what I'm trying to get at.
Is it also possible to use AND and OR in the same line? Or BOTH?
(I have other variations on this that I also like to ask about, such as multiple rows within a set that meet certain conditions that are identified in a different field, but for now this is a simpler version.)
I would like to think I'm not going to need a macro, and that I can do this in a Formula.
View 10 Replies
View Related
Jul 10, 2014
A macro is required to identify rows within a selection e.g. entire column A, that share the same value, then delete appropriate rows depending on the values in another column. The attached example details the requirements.
Extract Rec1.xlsx
View 6 Replies
View Related
Jul 24, 2014
I've been piecing a macro together from different sites and have run into a couple issues I can't find resolutions to. I have a excel doc for tracking paint emissions per week. I would like to have a button on my master tab that will add a row and fill the formatting from the line above across all 60 some sheets. Ideally I would like the same button to first prompt if you want to add or delete rows. When adding rows I would like it to add it above the button (there's 3 buttons and for 3 different sections) and if deleting ask which row to delete. If it can't be done in one button that's fine, I can live with that. The big thing I need is for it to replicate across all of the excel sheets. I've attached a copy of the document
View 1 Replies
View Related
Nov 5, 2008
How to create a macro to search a value and delete the row but I'm having trouble trying to get this to search multiple values. Keep in mind I'm really new at attempting to create a macro so this may look terrible ;-)
I have a list of 20 numbers (changes from time to time) and I need to filter any row containing any one of these numbers out of my results each day. I am currently able to filter a single group of numbers but get an end error every time I attempt to string a group of numbers.
This is what I have so far and is an example. We'll use three numbers as an example.
"12345","12346","12347" are the numbers that we'll use for the example that I need to filter. The code that I have is:
View 13 Replies
View Related
Nov 13, 2013
I need a VBA macro that deletes whole rows if the cell in one of the columns does not match one of three texts. For example, in column B, if the text in there isn't "Mary", "Joseph", or " " (empty space), the row must be deleted.
View 4 Replies
View Related
Nov 24, 2013
I have a workbook with multiple sheets. I want to use VBA macro to delete the row where the first column is strikethrough. I try to the follow VBA code but it doesn't work.
Sub Macro1()
Dim cell As Range
Dim delRange As Range
[Code]....
View 3 Replies
View Related
Apr 9, 2009
I need a macro to delete old data from a large selection of data, in order to keep the size down.
What I want this macro to do is
Check all rows from 5 downwards.
If A5 (date) is less than cell $B$1 AND B5 is not equal to C5 then delete the whole row.
Continue until reaching the bottom.
View 9 Replies
View Related
Apr 24, 2007
what is the vba code to delete the rows that the data you select from a list box. also how do you multiple select the data. the code i used is this
Sub RemoveDuplicate() Dim AllCells As Range, Cell As Range Dim NoDupes As New Collection Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item
' The items are in A1:A105 Set AllCells = Range("p21105")
' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
On Error Resume Next For Each Cell In AllCells NoDupes.Add Cell.Value, CStr(Cell.Value) Note: the 2nd argument (key) for the Add method must be a string Next Cell................
View 4 Replies
View Related
Aug 30, 2007
I have a file with many intercompany transactions. There is a range of business unit numbers that if anyone one number is in both Column B "Bus Unit" and Column E "Affiliate" I want the row deleted.
Please see the attached, it is more clear I hope. The two yellow rows would be deleted.
The list of business units that I would want to never be on the same row is "4, 7, 41, 44, 46, 51"
View 8 Replies
View Related
Feb 29, 2008
I have a Macro that I use daily on a spreadsheet I get with raw line-item transaction data on it. Basically, it deletes columns I don't need to look at, applies column headers, deletes certain rows that I don't need to look at, applies an auto-filter and filters to the first group of data I need to look at, and puts a subtotal on a couple of columns at the top so I can see totals on filtered results.
I need an additional function in this Macro. At some point in the middle of the actions above, I need to evaluate all rows and delete those that are 'opposites' based on a couple of criteria. Basically, I need to delete any transactions that have been refunded in their entirety, because for my purposes those are zeroed out and I don't want to see them....
View 4 Replies
View Related
