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 have a sheet of data (Sheet1 below) running into almost 1000 rows. Each month, I get an updated sheet (Sheet2) which has overlapping data with sheet1 (rows starting with N, O, P, Q), new data (rows starting with R, S) and data present in Sheet1 but not in Sheet2 (row starting with M in Sheet1). The columns "Item" and "Quantity" in sheet1 are populated by me and are blank in sheet2. The table below explains the structure.
Sheet1 Name ID Date Item Quantity
M 231 14/03/2001 egg 5
[Code] .......
It gets tedious to update Sheet2 every month. Is it possible to merge the 2 sheets into a Sheet3 (like below) if Name, ID, Date in each row is an exact match?
Sheet3 Name ID Date Item Quantity
M 231 14/03/2001 egg 5
[Code] ........
Further, it will make my job much easier, if it was possible to identify rows only present in Sheet1 and add a tag "absent in new" in a new columnidentify rows only present in Sheet2 and add a tag "new" in the new columnidentify rows with matching data in Name, ID, Date in Sheet1 and Sheet2 and add a tag "current" in the new column.
Something like the table below:
Sheet3 (Desirable) Name ID Date Item Quantity Comments
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.
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
I have an extract of sample data in columns A to E in the attached file.
I would like to create some VBA code to copy across to columns H to K the data in A to D that has that is flagged as "Y" in column E, sorted in descending order of the values in column D. When there is an "n/a" in column D the products should be sorted in ascending order of the product name. I have shown the desired outcome in H2:K6.
I had attached the excel sheet below. I need to have a macro which will be able to help me to deliver the result ( as attached) as per the raw data (as attached ).I have been cracking my head as there are more than 1000 item for me to be sorted by me daily and several reports with almost the same issue.
description-i will need worksheets to be created for each of the available status on the report and have all the details to be pasted accordingly to the status.this is meant for excel 2003.
I am attempting to come up with code to copy a row only if columns K:Z are all blank in a row.
I tried using thirteen if statements (it was a messy code) and then using a search code with a For/Next statement and none of them are working. They copy the rows but something keeps bugging no matter how I change them.
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 this macro to copy a range to another worksheet:
Sub transpose_UPCID() ' Hace el copy y paste transposed en BAUCS de los numeros de UPC"s y ' las cajas ATR Dim rng1 As Range Dim rng2 As Range Set rng1 = Sheets("Sales-Inventory").Range("UPC", Range("MK_ID").End(xlDown)) Set rng2 = Sheets("Sales-Inventory").Range("Figure", Range("Figure").End(xlDown)) rng1.Copy Sheets("BAUCS").Range("C11").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True rng2.Copy Sheets("BAUCS").Range("C37").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True Application.CutCopyMode = True End Sub
The only problem is that this code copies all the items and I need this code to look into Column A in the Sales-Inventory worksheet. There I have a data validation with a total of six options. I also have six differents worksheets where I need to copy the values based on that data validation. To give you and example, all items containing XX in column A copy to XX worksheet. Something like that.
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.
Each task in my spreadsheet has multiple jobs assigned to it. Frequently users will work two jobs for the same task simultaneously and log the time together.
Unfortunately when I go to try to sum up how much time was spent on each task, this causes it to appear as if the task took twice (or even twenty+) times as long as it truly did.
I want to be able to figure out the time spent on each task for each user. In order to do this, I need to get rid of rows where the task id and the user id are both duplicated in another row.
I have 2 worksheets (X and Y) and am trying to write a macro/vba to copy certain cell values from a row if that row has a marked cell. For example, on sheet X, marked cell is column P and when P = "Y", then values from cells B, D, I, J, L need to be copied to sheet Y.
Sheet Y has multiple rows and the values from X need to be copied into appropriate rows. B from X goes into A from Y, D into B, I and J into C, etc.
Im not sure how to go about this other than have every cell copied and then filtering sheet Y. But Id like to have a macro only copy marked rows and appropriate cell values (some contain formulas) as the data is constantly changing and sheetY has to be printed up.
I have 2 reference cells with the date range to look for in C1 and D2. G2 through IV2 have dates that run across. I would like for the macro to look for the start and end dates in C1 & D2 and paste the new data as values from A1:B20 into those particular columns of dates within the range.
ABCD1head count23start7/1/092vac%5%end7/15/093iap%3%4misc%3%5off%21%6% ot0%7off%21%8% ot0%9rpr wk rt1.510inst wk rt5.0211go back %5%12nw Mvr %3%13fall out %10%14jep/incomplete %16%15lines in service116% esc3%17% change610%18churn rate60.994%19report rate0.297%20% of market2218%
