VBA To Rearrange 11000 Records Into 550 Rows (20 Records Combined Into Single Row)
Apr 25, 2014
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.
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.
I have a new request concerning my warehouse managing.
I am getting back to this thread [URL]....
The request is as follows: When having the list of all the shelves, I'd need to create a new sheets with following data in a single column
reporting in order all the shelves with code and quantity.
My idea is as follows: 1 - to create a sheet with all the shelves 2 - to create a function that reads in this sheet the name of the first shelf, then searches in other sheets the shelf and copies to a result sheet all the records found 3 - then passes to the following shelf name 4 - and so on, until the shelves lists ends.
Please note that in each sheet I have these data in two columns.
Through a query I extract data from an oracle database. This database creates a record for every unique Article_Batch_Pallet_Faultnumber combination. Through a filter macro I create a new format on a different worksheet which creates a record for every uniqe Article_Batch_Pallet combination. I need a searchfunction to get the faultnumbers in the same row.
I am working on a data mining project and need to extract records based on a single condition. In the attached workbook you will see 5 columns – 4 Inputs and 1 Output. I need to automatically copy and paste on a separate sheet those records in which the Output value is greater than 970. A Record includes the values of all 4 inputs and the corresponding output value. I did this by hand for this subset of data but the actual number of record is extremely large so I need a macro which will do this for me automatically.
I am trying to rank records from multiple sections of a single array. Since there are over 100 sections I want to use a formula. Using the MATCH function I have the first row for each section of the array (Col D below). The psuedo code of what I'm trying to do is:
RANK(B1,Bfirst row of section:Bfirst row of next section -1)
Example (results in Column C):
A B C D 1 West 100 3 1 2 West 150 1 4 3 West 125 2 4 East 50 3 5 East 75 2 5 East 140 1
I will also need to increment the section as I complete each section of the array. Am I trying to do too much in a single cell??
I have a set of related variables that are split over multiple worksheets, and I need to be able to take specific information, duplicate certain values and produce an output sheet for use in a separate piece of software.
The variables are: Position Number (Sheet 1) Position Title (Sheets 1 and 2) Position Requirement (Sheet 2) Requirement Importance (Sheet 2)
The output sheet requires a list of all the requirements for each position number, which means the position number itself needs duplicating (in new rows) X number of times, where X is the number of requirements assigned. The appropriate requirements are then to be pasted in next to each position number (and the requirements can be found by comparing position number to title, and from title to requirements).
Normally, I’d be able to do this using lookups and so forth, but my problem arises when I have multiple position numbers with the same related title (in the attached example, there are three plumbers with unique position numbers). I can’t figure out how to say to Excel “a plumber has five requirements, and there are three plumbers, so duplicate each position number for each plumber five times, then insert the appropriate qualifications (and their associated importance values) next to the position numbers”.
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.
I got this code to copy stuff from Raw data worksheet which has a Dynamics Atlas table to another worksheet named input. However, I noticed that some records are duplicated so I want the code modified to only copy unique records based on criteria in column B of the "Raw data" worksheet.
Sub Copy_atlas_table() Application.ScreenUpdating = False
With Worksheets("Input") .Range("A2:S2" & .Cells(Rows.Count, "G").End(xlUp).Row).ClearContents
I can view the data how I need to by conditional formatting and pivot tables but HR wants to get rid of all rows that: only have 1 accounting line per document such as Doc MI310712 in attached sample So I only want to display records if there is more than 1 accounting line for each document
My understanding of Excel is that in order to get rid of rows that a VBA macro is needed since there is no function to do it. My VBA is limited but is there a way to specify criteria in a filter to hide the rows using a formula?
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
I 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
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".
I 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.
i 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'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 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.
I 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
ACC dup K I 123456 ? 0 1 123456 ? 1 0
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:
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:
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).
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.