Insert Rows Subject To Conditions In Rows Above And Below
Jun 21, 2008
as per the attached, need to insert those grey rows subject to the following condition :
if current row date <> next row date, .and. current row latitude / longitude <> next row latitude / longitude , insert grey row with date = current row date, else insert grey row next row date
note that the coordinates in the repeated grey rows, for the "Home" location, are the same through the sheet, should be entered by the user, at the beginning of the process, since there will be a spreasheet per user.
date is in column K
latitude / longitude are in columns B / C
this will be of tremendous assistance in automating mileage claim review.
The best way to explain my problem is to look at the table below:
How it looks now: ApplePrice 1 Price 2 Price 3FruitDeliciousPearStore 1 Store 2FruitVery DeliciousHow I want it to look:ApplePrice 1FruitDeliciousApplePrice 2FruitDeliciousApplePrice 3FruitDeliciousPearStore 1FruitVery DeliciousPearStore 2FruitVery Delicious
In cell G2 I have a formula that adds the values in column C that are labelled in column B as either 'Apple' or 'Grape'. This is accomplished in the formula using {'Apple', Grape"}. Is it possible to change the formula so that the {'Apple', Grape"} can be replaced with a reference to a range in which the names to be used in the formula will be listed (for example in J1:J3 in the attached file)?
I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:
I have a spread sheet with values in the area of A1:H834
In column H, I have number values from 1-7.
Essentially that number value means that the values in the row are duplicate.
So, for example, if H2 has a value of 4, that means that $A$2:$G$2, really should have an additional 3 rows underneath with the EXACT same data in each cell, however, the way the sheet was created, was to remove the duplicate values and just indicate in column H, the number value of how many duplicates $A$2:$G$2 really is.
I need to unpackage this and create what it was originally. What type of formula can I use, to look at the value in H2, and then insert underneath that number of rowes with the exact same data as A2:G2 and do the same for the remainder of the table all the way down to A834:G834
I'm a macro novice and have been trying to teach myself how to write the correct one for a task I need to do, but I cannot seem to get it right. Basically, I have bunch of data and for one of the variables, different values are separated by commas. What I want is to create a row copying the info below for each piece of data after the comma.
Sheet1
A B C D
[Code].....
I suspect there is a fairly easy way to do this, but I cannot figure it out from searching the forums (or rather, I can't get it to work right).
i have this code which inserts blank rows in alternate rows,
Code: Sub insertrow() ' insertrow Macro Application.ScreenUpdating = True Dim count As Integer Dim X As Integer For count = 1 To 20 If activecell.Value "" Then activecell.Offset(1, 0).Select
[code].....
what changes should i make in this code to insert rows only when ther are now blank rows. So first time i run, blank rows are already there, and when i update some data at the bottom and re-run it inserts blank rows again.
Count all the true statements in column A (Work) of sheet1 (Checklist), once counted insert that many rows on sheet2 in a specific location, I found a count formula just don't know how to do the insert rows part
Code: Sub CountRows() Dim Rng As Range, CountTrue As Long Set Rng = Sheets("Checklist").Range("Work") CountTrue = Application.WorksheetFunction.CountIf(Rng, "True") End Sub
However, I need to alter this to work for inserting more than one row at a time. ie. the user selects 'x' number of rows and 'x' rows are inserted below (in the same way 'Insert Row' works in Excel) and the row above the selection is copied down.
I would like to delete rows that are based on these conditions: First ,Do a loop from row 2 to last available row. - Delete rows with same column(column B) that has the same value. However I want the last available row with the same ID to remain.
- Delete Rows with any values in found in other sheet column B. When the value taken from the first sheet(ABC) is compared to the column B in second sheet(DEF), if they are equal, the row will be deleted in the second sheet and the rows in first sheet will main. I had attached an simple example with the 2 sheets. The Result are shown in the example.
I would like to delete rows that are based on these conditions:
First ,Do a loop from row 2 to last available row - Delete rows with same column(column B) that has the same value. However I want the last available row with the same ID to remain.
- Delete Rows with any values in found in other sheet column B. When the value taken from the first sheet(ABC) is compared to the column B in second sheet(DEF), if they are equal, the row will be deleted in the second sheet and the rows in first sheet will main.
The comparsion of ID are compared between sheet ABC and sheet DEF (the Highlighted are those rows to be deleted based on those conditions) The final outcome are shown on col H I J for sheet ABC and col I J K for sheet DEF.
Is it possible to close rows based on conditions? e.g if A1 contained Yes or No as the switch to close and a column contains open or closed in each row for the trigger. So if A1=Yes all rows with 'closed' will be hidden, if A1=No then all rows are shown
Im making a timeplan for projects in our company. Each Sheet is for one month. So long I only found macros for copying rows if checkbox is true, but without the date condition.
In cell C2 I have the current date shown with today() formula
On the end of the month for example on Sheet called "May" I want that all rows witch are unchecked that means unfinished (H6;H50 are checkboxes) are copied to the next Sheet "Juny". This must happen only once a month (on the first day of next month). Example: On Sheet "May" this macro should be run only on 1st of Juny.
The rows should be copied without spaces between rows (if rows 6 and 8 are unfinished in Sheet "May" the next Sheet "Juny" should contain this rows in line 6 and 7).
I'm trying to hide rows in an excel sheet based on two stipulations. I want to hide the row if it finds a particular value in column B and a different particular value in column K, otherwise i want it to do continue looping until it has hidden all rows that meet both stipulations.
VB: Sub hide_loop() Dim bl As Excel.Range Dim blrange As Excel.Range
[Code]....
Currently I have tried different approaches, like a Do Loop, but I could not make that work, and this seems closer. The problem I'm having right now is that with this loop it hides everything found in column B regardless of what is in column K. I suspect this is because of the code following the if statement.''
combining 2 rows of data if certain conditions are met. I have attached an example of my worksheet for reference.
Example.xlsx
What I want to happen is if a number in column J is followed by an "x" then the values in columns AQ and AR should be combined with the row of the same number (e.g. if 236x is in column J2 then the values in AQ2 and AR2 should be combined with AQ3 and AR3 because 236 appears in column J3, the same process needs to be repeated for 237x with 237 etc.).
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.
I need to delete rows based on following conditions, If,
Ticket number, origin, FB Data, Desc, pax name is same i need to delete all rows except the Row which has Destin filled in. In the above example I need rows only with NBO
I need a macro that loops through all rows (With the exception of the first row) and looks for two conditions. If those two conditions are met, I'd like for it to delete the row.
The first condition is if Column O does not contain a certain phrase "VTX" VTX isn't the complete value of the column, it might just be contained within a few other notes in that column.
The second condition is if Col S equals "COMPLETE/FOLLOW-UP IMAGING".
So if these two conditions are met, Col O does not contain the word "VTX" and Col S equals "COMPLETE/FOLLOW-UP IMAGING", then that row should be deleted.
I have a sheet caled "Data", with thousands of rows.
I want to delte any rows that have duplicate rows only if the following data is in the same row but if in column A, coloumn C and column H have the same data
A B C D E F H I w 1 1 3 4 5 6 7 e 1 1 1 3 4 5 8 w 2 1 5 2 5 6 7
The last row would need to be deleted as dupilcates are A C and H
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 .............................
I have been trying to work on this thing for like 3hours and i could not make even a single line since I dont get what loops I should make and how I should do it. Could someone suggest me how to do this.
1. check the date.Calculate two weeks backwards.
2. Check for id no which is scanned more than once in that two weeks from the current date.
3. Check for type of ppe if its different then leave it if they are same and exceeds 3 of the same type it should BOLD the badge id.
I am trying to create a dashboard of reports. I want to be able to click a button and the user will be taken to the tab with the appropriate report. I am not a programmer so I am trying to use Excel formulas to the fullest.
I am trying to create a 30/60/90 report. I am using a named range for all data. Is there a way to create a dynamic named range and specify for rows where the age column is between 0 and 30, 31 and 90, 90 and 120, and so on?
I have an excel template that is used for communicating in projects to teams their responsibilities and defining which stream/group has Accountability/Responsibility/Consulted/Informed (known as a RACI model) for the activity.
For any given project activity, there are rules:
One person is responsible for performing the activity (R) Only one person can be accountable (A) Many people can be consulted through the activity (C) Many people can be informed of progress (I)
You must have an A and and an R for each activity And one person can be accountable AND responsible A/R
Given that project activities can run to 100 + activities in some of our more complex programmes.... I'm looking to provide a flag in the spreadsheet for when an error has occurred and a gap in Responsibility or Accountability is created. (Less worried about testing for C's and I's)
I want to count all instances if the following conditions are true. In quotations, are the names that I am using for column ranges. Here are my conditions, I want to count the rows that have the following conditions.
When "dates" or J2:J25 is less than or equal to today's date AND "HTeam" or W2:W25 is equal to Civil AND "Percent" or K2:K25 is equal to 100
I have a report in which I need to delete the entire row for each cell in Column A that has the name "Defacto" in a certain location in that cell. I am trying to use VBA code in conjuction with the "MID" function [i.e., Mid(Cell.Value, 8, 7) = "Defacto"]. This is the code I came up with (but, obviously, it doesn't work):
Sub DeleteRowOnCondition() Range("A2").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select For Each Cell In Selection If Mid(Cell.Value, 8 , 7) = "Defacto" Then Rows.Delete Next Cell End Sub
As well, there is another worksheet in the same report in which I need to delete all the rows that do NOT meet that condition (while retaining the ones that do).
I am having a problem finding or creating a macro for what i am needing to do. I have a workbook with 2 worksheets (2 months july and august) to figure out a billing process.
I need to be able to First of all search each value in Column A on sheet 2 (august) to Column A on sheet1 (July).
My sheet is set up like this where ID is a buyer and Products 1,2,3...are if they have purchased that product from us
A B C D 1 ID Product_1 Product_2 Product_3 2 100 0 0 1 3 200 1 1 0 4 300 1 0 0 5 400 0 1 0
Better Picture representation attached
Both worksheets are very similar but August may have some changes from July such as new ID's and changed prduct purchases. What I need to do is have each ID in Column A be compared to Column A in the July sheet, but it may not always be the same cell (because of new ID's added they are moved around)..so it need to search for the same ID first then compare. Once it finds the ID, I then need it to compare the rest of the row (the product info) for that ID to see if any products were added or changed to determine billing. And for any changes that are found in the comparison, I need them to be highlighted.
Now in the case that a new ID was added...it wont be in the July sheet, so the whole row would need to be highlighted to notify of a new ID and new billing needs to be added.
This way the billing person can look at the sheet, see the highlited changes and see what is new or needs to be added to a previous ID's billing. Currnelty they are looking at it manually to see if there are changes.