I've a worksheet with a list of destinations and country code as well as Y/N fields.
I want to create a solution such that when I change the yes/no field for one destination, all other destinations with the same country code will follow.
I have attached my work file below.
I 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.
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
example of a database user form that will allow me to list records in a sheet as well as search for records in a sheet. I know excel has a built in feature for this but it is menu driven and I need something that is button driven and will allow me to resize the form layout. I was not able to figure out how to do that with the built in form.
View 7 Replies View RelatedI have a dataset with 5 columns. i want to identify the the max users in each zip code and the corresponding ID for it. For e.g. for zip 01105, max users are 1380 for ID 010840. I want my final data to have just these 3 values.
I tried the pivot table but it hides the ID corresponding to the MAX value.
ID Zip CityState Users
01084001105SpringfieldMA1380
01012001105SpringfieldMA1102
01003001105SpringfieldMA518
01145001105SpringfieldMA27
01384001105SpringfieldMA3
01084001107SpringfieldMA629
[code].....
I am looking for a formula to give me the top10 records from a range.
Please take a look : Top_10.xlsx
creating a formula to count the number of rows in a different worksheet if a cell has a value of "1" or "2" (these are the only two allowed values) AND a status of "'on track". also need to add values in other cells within the different worksheet if the value is "1" or "2" AND has a status of "on Track".
View 9 Replies View RelatedI have a problem in excel that is very urgent to resolve. I have a table that contains duplicate records e.g two people living at same address. I want to merge those records whose address field value is same. Find attached an excel sheet that contains the exact data and the exact output that I want. I know it requires VBA coding.
View 4 Replies View Relatedi have cell N1 witch is a number example "8832"
cell O1 is text example "state street"
now i have A1 thur A10 as an address "number"
now E1 thur E10 is street address "text"
i need to take n1 match it to A1-A10
then take that row number and see if E1 of that row numbers matches
O1 if it does that that row numer and get g of that number if not keep checking to i find a match
I have 2 workbooks Mater and Update. I have a VBA code that compare between the two files column by column and update the Master file according to the Update file ( the code below).
Now my user want to see in a separate worksheet ("changes") only the changes - Date and time of changes at column A , old value at column B and new value at column C ........
I need to count how many rows of records there are on the page labelled Data.
this is what i wrote...
=COUNT(Data!A3:A1995)
doesn't seem to be working correctly
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 2 worksheets. Sheet 1 has in column B a single ID number in every cell, Sheet 2 in column A can have multiple numbers in a cell (e.g. "55517 / 55518 /55519" written in one cell.)
For every row in Sheet 1, I need to find the ID in Sheet 2 and add in column C in Sheet 2 the corresponding cell value found in column E on Sheet 1.
By doing this formula, when I am in Sheet 1, I can already find the row number in Sheet 2, if it exists.
=IF(ISERROR(MATCH("*"&B2&"*",Sheet2!A:A,0)),IF(ISERROR(MATCH(VALUE(B2),Sheet2!A:A,0)),"NONE",MATCH(VALUE(B2),Sheet2!A:A,0)),MATCH("*"&B2&"*",Sheet2!A:A,0))
I suppose I can then move to the destination cell by using OFFSET.
This is all I have been able to figure out conceptually.
I think it makes more sense for this to be in VBA. Especially the 'writing part' I don't know how to do. After the OFFSET part, how do you tell Excel to write something to that cell?
Because several IDs from Sheet 1 can occur in Sheet 2, it would have to add like REPLACE at the end (ie. len(cell)) "+result", so they get summed if there are multiple finds.
I am also not sure the MATCH sentence above can be translated as a Macro, or whether it is even an efficient way.
And finally, it has to loop (repeat for every row in Sheet 1), which must require VBA.
I've been trying to resolve an issue with the userform that i created. It adds new records to the sheet "Data" but i can't seem to add a search/edit function to it.
It could be either a combobox with the existing Project Id's or a text box + a control button, so a user could enter Project Id and hit a button.
The spreadsheet example is attached : Project Entry Form.xls
I am new to to VB Scripting, filter the records. In the attached file there are multiple records which needs to be filtered. Once all the records are filtered, i want to delete the entire row of that record(s). I want to filter column 'F' with the values mentioned in Sheet2. I tried recording a macro, but it is not allowing me to do so as the macro has some limitations. The search and delete row loop The main purpose is to delete the row of the value, if not found, move on to next value in Sheet2.
View 4 Replies View RelatedI have a large (300K+ records) database with a sizeable amount of duplicate records. I want to delete the duplicates but this is not a matter of simply Remove Duplicates; I need to evaluate them before I do.
I am wondering what functions would:
1) select the specific record in a set of duplicates that makes a determination of a status
2) once the status has been determined for the set, delete all other records
Fields in my database:
ACCIDENT NUM (ID field, in text or General format)
DUP (for Duplicate, indicated by a character, for now its a "?")
OCC_KILLED (in Number format)
OCC_INJURED (in Number format)
SEVERITY (in text format)
Here are some scenarios:
ACC dup K I
12345 ? 0 0
12345 ? 1 2
Or:
ACC dup K I
123456 ? 0 1
123456 ? 1 0
Or:
ACC dup K I
1234567 ? 0 0
1234567 ? 0 2
1234567 ? 0 0
This is the formula for indicating if there are Duplicate records in the larger dataset:
=IF(OR(A2=A3,A2=A1),"?","")
I need to determine the Severity of the accident based on this:
If OCC_KILLED > 0 then SEVERITY = F (for Fatal)
IF OCC_INJURED > 0 and > OCC_KILLED then SEVERITY = I (for Injury)
IF OCC_KILLED and OCC_INJURED >= 0 then SEVERITY = F
IF OCC_KILLED and OCC_INJURED = 0 then SEVERITY = PDO (for Property Damage Only)
I have a code already in place for how to create the value for Severity but it DOES NOT account for duplicate records:
(in SEVERITY field):
=IF(A1<>0, "F",IF(B1<>0,"I","PDO")
sampleset.xls is a sample ot the database.
there just want to ask about on how to count records using array attach here is my sample file.!
View 6 Replies View RelatedI have an problem with pivot.
Problem data:
Month SP Qty
JanSmith101
JanKiran125
JanSmith111
JanSmith110
JanJohn149
JanLaura145
FebSmith102
FebLaura120
FebKiran117
FebLaura134
FebLaura107
FebSmith111
MarLaura125
MarSmith149
MarLaura139
MarJohn108
MarKiran121
MarJohn101
MarLaura115
MarRavi125
Output table: I need to be able to use pivot on the above data and generate Total qty per person per month in columns. Able to convert it into following output
SPJanFebMarTotal
John1490209358
Kiran125117121363
Laura 145361379885
Ravi00125125
Smith 322213149684
I also attached a sample file for clarity. Sample Pivot.xlsx
I have an Excel table. Each row is a separate record. The user types in new records at the end of the table. One of the columns in the table contains a formula. Ideally the formula should apply for each cell in the entire column. Since I don't know how long the table will end up being, I simply ask that the user drag the formula down from the cell above. I do not want the user to accidentally change the formula though. At first I thought a Custom validation with a secret word that the user is unlikely to type in, would keep the user from changing the formula already in the cell. However, I can't stop the user from deleting the formula, which doesn't help. I then thought maybe locking (protecting with password) the column would do the trick. However, now the user can't drag down the formula from the cell above. I would therefor like help with one of 2 options:
1- have the formula apply to the cell automatically as a new record is created (ideal)
2- find a way to protect the cell so that the user can copy the formula down, but not change or delete it
I did find the following code online, which appears to go some ways to answer my first option, though I can't figure out why it only works if the formula is in column A (i.e. I have formulas in columns A, B, K, L, and AF to DL).
I have 2 columns A and B.
There are 1000 records in column A and 500 records in column B.
I would like to compare both columns for duplicate entries and deleting all instances of those record in both columns, leaving behind records that were not duplicated to begin with in their respective columns.
I have a spreadsheet with a column of records (text ) from B1:B4000. I would like to delete the entire row of the duplicate record. Overall data set range is (A1:I4000). This spreadsheet may contain more than 1 duplicate record. Is there in easy fix?
View 12 Replies View RelatedI have a worksheet with a header row.the data is from B2:B25.I have to accomplish two things.
i)I want to get only 8 records (viz record nos-5,6,11,12,17,18,23,24)from the entire worksheet.serial numbers are shown only for illustration purpose.i want to eliminate all other records from the worksheet.then i will get the records from seriel numbers 1-8 as shown in E2:E9.
(2)I want the content of row3 to come up in C2 and then row3 should be deleted. now the content of row4 should come up to C3 and then row4 should be deleted.it should continue.the result will be 4 records as shown in G2:H5
See the attached sample file.
HOW TO GET THE REQUIRED RECORDS ONLY.xlsx
Columns F, T and S of the attached worksheet contain order IDs.
Each order ID could be present in 1, 2 or even all 3 columns.
How could I find out and show which columns each order ID is present in?
I have 2 excel sheets with records of email addresses. Excel Sheet A , Excel Sheet B.
I want to exclude the email addresses of Excel Sheet B from Excel Sheet A (if there are similar records I want to remove them from excel sheet A)how to do this?
I am trying to lookup data from a list and return multiple records. I cannot use row or column funtion as the tool will not allow
I have tried something similar to this but its not working =INDEX(C$2:C$102,SMALL(IF($A$2:$A$102=$G$3,$K7-$K$7+1),$K7))
the attached excel has the data set. The attached picture has also a sample data that I need formula for.
I have data in sheet1 and sheet2.I need macro i will check sheet1 data wth sheet2 data,if there is any differences in both sheets then highlight those unmatched records in both the sheets.
View 2 Replies View Relatedi am having data in ColA & ColB in both Sheet1 & Sheet2
1) some records are both in Sheet1 & Sheet2
2) in Sheet1 itself some records are duplicated
3) in Sheet2 itself some records are duplicated
what i want is to extract the all the records in Sheet1 & Sheet2 in Sheet3
without duplication of records..!
I have attached 2 spreadsheet :-
1. BounceBackMaster.xls
2. BouncebackMails.xls
I need to excute the macro from master file asking the input file. Input file will be the Mails spreadsheet. In the master spreadsheet there will be "TAG" column, depending on value it need to check the value in the Mails spreadsheet "MessageContent" column after subject value. It need to group all tAG value, each sheet need to be created with TAG value and related need to be copied from "BounceBack" sheet to created sheet.
Example:
Step 1 :- Macro is executed from "BounceBackMaster.xls" and asked for input file, input file will be "BouncebackMails.xls"
Step 2 :- From "BounceBackMaster.xls", First TAG value will be readed " Monthly Account Statement" is check in "BouncebackMails.xls"........
I have a column of data that is an 850,000+ list of phone numbers. I need to peridoically insert another 10,000+ at a time and see if there are duplicates. Using conditional formatting to highlight the duplicates with a colored cell, and then sorting those records to the top is what I want to do, but it's too much for Excel to handle, and it hangs. It looks like I need to break up the 850k records into smaller chunks. Anyone know what an average pc with Excel 07 can handle? It's a 2.8ghz w/2gb ram.
View 6 Replies View RelatedIn Excel 2003, I need a macro to do the following:
For each record in column F (starting at row 2) (workbook: CATS LITTERS LISTS AS AT 01 FEB 10.xls) (worksheet: RAKE)
Find a matching record in another open workbook (workbook: DOG TRAINING v 3.xls) that has multiple worksheets as
follows: ...
