If you look in the file you will see a records sheets and a form sheet. I want to see if a record has been broken. All the times are in seconds and the distances are in centimeters. How can I find a certain row, compare and act based on an if statment?
View 2 RepliesI have a range of columns i.e. 23 columns (i.e. B through X). Someone can write records in these columns (starting from B21).
Duplicates are considered the rows with similar data in columns 3 and 11. I know about the removeduplicate method and works really well but i want the duplicates not to be removed. Instead another column shall be checked for date of entry (user will entry date in format dd/mm/yyyy). The newest entry will change the value of the cell in column 4 (islatest column)to TRUE while all other records will be FALSE. This will work with the filtering of data on a pivot table on another worksheet.
I have been given a task to compare a set of the current months employee records to last months employee records. The goal is to a) find employees who are still in our group but may have changed managers b) find employees who are no longer in our group and c) find employees that are new to our group. Once this information has been compared between the two workbooks, it needs to be exported to a new spreadsheet that needs additional columns added for formatting in order to import the new spreadsheet in a tracking tool.
For example, last months spreadsheet includes the tracking tool ID column and has a row for each employee. For an employee that is currently with the team they would need the tracking tool ID to be put in the newly generated spreadsheet. Additionally the current employee might have changed managers and that needs to be updated. A new column needs to be added to the new spreadsheet that shows that a current employee is "Active". For employees that are new to the team they won't have a tracking tool ID because they are new but would still need to be considered "Active". And for the employees that have left the group, they would also need the tracking tool ID from the old employee record but would need a column that would mark them "InActive" since they are no longer with the team. My last step is that I want to add a column called "Team" that would iterate through each employees managers and assign them to a specific team based on the manager that they have.
My general idea has been that I need to loop through all of the employee names or numbers from this months records for comparisons to the names of employees from last months records. Once a match has been found I need to copy the entire row from the current months to the new spreadsheet. That way I will get the latest info or "row" for a current employee and that would also handle finding if they have a new manager. Next I need to pull over the tracking ID from the last months records, create a new column called "Status" and make the employee "Active" and create a "Team" column showing the team.
For employees that are no longer on team, I need to copy the whole row from the latest employee records, pull over the tracking ID from the last months records, update the "Status" column and make the employee "InActive" and populate a "Team" cell showing the team. And lastly for the new employees, I need to copy over the row from this current months spreadsheet. They wont have a tracking ID (It will populated when the Excel file is imported in the tracking tool) and I need to update the Status column to "Active" and also provide their "Team". After the new spreadsheet is generated I am done. The file can be imported. And the next month I need to kick off this script again.
I have a very large table that has a one to multiple relationship in a GIS. GIS doesnt support that type of relation ship. I need to find records that are related to the single key but have different values than the first record for that key.
eg
Col 1 col 2 Col 3(key)
ab ab 1
bc bc 1
In this example, only the first row (ab) will be regarded in the GIS. so I need to pull out the second row and put it into another dbf based on the three columns so that I can then put it into the GIS so that the second row is considered and not ignored.
I am using Excel 2007.
I have a large spreadsheet and new data is added periodically.
I have several columns of data associated with a description & date.
The data associated with each description is unique to that day.
I want to archive the older desciption data after I add newer description data.
Column B = date of the data
Column E = description (phrase)
How do I remove duplicate older rows of description data from my main spreadsheet (keep the newest descriptions rows) and save the older rows of data in a archive sheet?
My sheet has many columns from A to BU.
I have about 220,000 records with many duplicate Descriptions in column E. Each Description record has a date in the Date column B.
Records are sorted by: Description A to Z
then by: Date Newest to Oldest.
Here is an example (not actual sheet):
I highlighted the duplicate Description records in these examples that I want to update and archive.
I want to delete the older Description records from my main sheet so that only the duplicate Description records with the newest date remains like the example below.
And the older duplicate records that are removed from the main sheet I would like to save to a new sheet so that I can save a history of the Description records.
I have these following codes. when i click button then it updates the records, like pulling data from sheets and putting it into one. thats working fine. only things is, when it update the records, then it shows whats going on behind, all updating sheets get visiable. is it possible can i just hide whats going on in the back? when i click the button msg box comes up that please wait its updating the record.
Dim Response As Integer
MsgStr = "ARE YOU SURE YOU WANT TO UPLOAD NOW?, PLEASE CONSIDER RE-CHECKING YOU INPUT!!"
TitleStr = "USER MESSAGE"
If MsgBox(MsgStr, vbYesNo, TitleStr) = vbYes Then
Sheets("Employee Data Input").Select
[Code] ...........
I copy a table from a data dump dbf file into worksheet 1. I enter a separate column titled "Comments" and use this for status purposes. My problem is that when I refresh the worksheet, my comments don't stay attached to the rows/ records that they were originally entered for.
View 9 Replies View RelatedI have 1 workbook, with 3 sheets. Sheet1 (EVER) has 3000+ rows and 12 columns of customer information. This sheet is for all customers who have ever placed an order. Sheet2 (06-07) has 1500+ rows and 12 columns of customer information. This sheet has all customers who have placed an order in the last 2 years. Sheet3 has 1 row, which consists of the column titles (12 columns) that are on Sheet1 and Sheet2.
I need to put all customers that are on Sheet1, but not on Sheet2 in Sheet3. I have tried VLookup; advanced Filter and a number of codes in the last 3 days and have not been able to figure this out.
I have a list for example...
Ian
Andy
John
David
Ian
Andy
John
David
Max
I want to search or filter the list and find the only non matched record in the list and put this in another sheet ie max as this is the only unique name in the list, all the others are matched. Everything I have tried eg filter for unique records returns ian andy john david max, which is not what I want.
I have 3 sets of data in three columns A, B and C. Data in Column A is the base data, now i have certain common records in columns A, B and C. Considering the column A as the base, I want to find out in Column
D = Common records in A and B,
E = Records only in A not in B
F = Records only in B not in A
G = Common records in A and C,
H = Records only in A not in C
I = Records only in C not in A
J = Common records in A, B and C
if possible also in Column
K = Common records in B and C
L = Records only in B not in C
M = Records only in C not in B
I have about 5000 records in column A, B and C each.
If I enter emp Id then it should search in my mastersheet and if data found then display.
If records not found then after clicking on add button.
Employee information page should activate and my cursor should be on emp id.
I have developed attached file : My Data Entry Form.xlsm
I have two worksheets each of which includes a left column of unique email addresses (at least, unique within each lsheet)
The data on each sheet is structured identically. What I want is to generate a list of records that appear on both sheets.
In sheet1 I have the following
1. Names (Column A)
2. Date (Column B)
My current list if from A2:B300
I am trying to have a piece of code go through all the records in this list, if any of the dates are within the next 2 weeks (from now()) then copy this record (name, date) and put in sheet2. And loop through all 300 records or possibly additional records.
Im trying to find out if there are any duplicate records in my spreadsheet. See look at the attached. Cell 6745 down is highlighted in green. I want to check this against all the cells above. need to write a formula so that it marks any products in the green section with duplicate (in column I) if they are in fact repeated.
View 2 Replies View RelatedI have one Column (Col A) which has a list of values. In Col B I would like to filter out the values in Col A based on some criteria. For example, below I would like Col B to show all of the records in Col A where the LEFT six characters = "Active".
Col A:
Inactive_Jim
Active_John
Active_Frank
Inactive_Betty
Inactive_Lucy
Active_Tina
Inactive_Jill
Active_Tom
Col B would result in:
Col B:
Active_John
Active_Frank
Active_Tina
Active_Tom
I'm sure this is elementary, but I can't seem to figure it out.
I have one hundred rows of data and within that there are some records which are the same and they will always be in pairs - identifiable by a cell with the same ref common to both records. how to strip out the records that are not part of a pair? I would like remove all of the non pairs and move them to another sheet.
View 2 Replies View RelatedCreating a macro. Need to delete records in column D from the first blank cell. 1st blank cell variable.
View 9 Replies View RelatedOk ive been playing about with excel for the past week to get a spreadsheep up and running so i can see the totals of some things and now its getting a little complecated for me (ive done the easy stuff lol)
Ok here is the situation and i'll try and explain what im doing as best as i can. Im trying to make a spreadsheet that shows how much a resorce can be refined so lets say in a1 to c1 i have 400 in each but i can mix a with b and b with c at the ratio of every 20 of a and c to ever 10 of b
so somewhere in my spread sheet id have the result 20 and 20 as products ato c can make 20 of product d and e i hope that makes sence?
for every 20 + 10 of a(400)+b(400) = 20
and for every 20 + 10 of c(400)+b(400) = 20
so if i had 600 300 600 instead of 400 i can only make 30 of product d of e (id have to choose)
I need to format the background color of a row using an if statement or something similar. If have a certain cell has a specific value I want the entire row that the cell is on to have a specific background color.
View 9 Replies View RelatedHow can I write the equivelant of an if(and( statment in VB? What I would imaging it would look like is this...
If _________ = true
AndIf ________ = true Then
____________
End if
It seems like this should be simple, but after searching, and searching the help files, the internet, and this website, I am no closer to figuring this out.
I have another question with my red sox scores excel sheet. On my overview I want to be able to select any team, and view the indiviual stats against them. For the life of me I dont' know/can't find how to even do a simple yes no if statment to what the value in the drop down menu box is.
View 3 Replies View RelatedI don't even know if using the IF statement is the right function, but anyway. I am using VLOOKUP to complete a supply list, but of course if the supply name isn't on the first spreadsheet then the result is NA. Instead I want it to return 0.
View 3 Replies View RelatedI would like to check whether 2 conditions are satisfy in an if statement. One condition is of data type text and it is stored in a variable calls search1 whereas the other condition is of data type date and i intend to store into a variable calls search2. Could someone tell how to put search 2 into the below code. Basically, i want it to be like
if Cell A is equal to search1 and CellB is equal to search2 then
return value that is associated with the search in cell c
ActiveSheet.Cells(r, 13).FormulaArray = "=MIN(IF(Sheet1!$A$1:$A$20=""" & search1& """,Sheet1!$C$1:$C$20))"
why the code below is very slow with the extra if statment in it (marked with **) i have googled around and found that my for staments are correct i have tried screen updating etc. i have added a timer to see what diffrence it makes, what i have is 3 sheets 1st has about 100 rows with a number and time same as second but that only has 2 rows of the same the 3rd sheet has about 100 rows of just a number what the code does is loop through the 3rd sheet (build list) when it finds a number it looks on the first sheet to find the same number then gets the data in the second coloum and so on until it reaches a cell that says stop.
i have added a button to start this process, with the ** line in it takes 311 seconds with out it it takes 0.17 seconds
I have a list of codes. Some of them may be duplicated. I want to use the conditional formatting and the IF statement to highlight in a particular colour any codes that have appear twice or more. I used to know how to do this but now I cannot get it right.
View 2 Replies View RelatedI have two dynamic named ranges that are:
- identically sized;
- single column;
- multiple rows; and
- both start at same row on worksheet.
They are named 'rngTrackingWorkStarted" and "rngTrackingStatusCode". Using VBA, how do I check all the cells in the range so that; if the value of a cell in rngTrackingWorkStarted is equal to "Yes", the value of the corresponding cell (same row) in rngTrackingStatusCode should change to "G"?
I have a spreadsheet titled "PMIX" In Columns A:F Column F has Week Ending date. Each week, I need to update this, i.e. delete the data from two weeks ago and then add the new weeks data. What I am thinking is I enter a Week Ending Date in H1, then I would like VBA to delete all rows where it has the data in H1. I am hoping for a delete function as opposed to a clear so I can shift all the rows up.
View 3 Replies View RelatedI have over 1000 of rows of data and I want a formula or a Macro to create 5 new records (rows) based on the following:
Below is how data in master sheet1 looks like...
STORE#ZIPCODEID #1ID #2ID #3ID #4ID #5
10134567A100B200C300D400E500
10245678A200B300C400D500E600
...
...
With the formula or a macro I want it to look like this in Sheet2 in the same file...
STORE#ZIPCODEID #
10134567A100
10134567B200
10134567C300
10134567D400
10134567E500
10245678A200
10245678B300
10245678C400
10245678D500
10245678E600
. . .
. . .
. . .
As you can see there are 5 records per STORE# listed in Master Sheet1.
I have an excel file that gets records from access database. I have a field named "Class" in column C which starts at row 4. I want to filter the records in such a way that only those records with Class equal to whatever value I put in cell B2 will be shown.
For example:
A B C
REPORT
Filter Class: _______
ProjID Name Class
001 Project A 4
002 Project B 4
003 Project C 4
004 Project D 8............
Book1 and Book2 are workbooks that I have modified in order to protect private information.
Book1 will have 11,000 records (my example Book1 has only 100). I need to rearrange Book1 such that it looks like Book2. Book2 has 20 complete records from Book1 combined into one single row, and my example Book2 has populated 3 rows only (3 rows x 20 records, making 60 records now appear on 3 rows only).
Macro for getting Book1 to Book2? 11,000 records in Book1 will take a lot of hours to transform into Book2 unless a macro can do the job for me.
Book1.xls
Book2.xls