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.
Book1.xls
Book2.xls
View 5 Replies
ADVERTISEMENT
Aug 10, 2014
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.
View 7 Replies
View Related
May 28, 2014
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
shelfcodequantity
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.
I am here attaching you an example
My problem is this:
How to tell in VB to realize step 2 ?
View 3 Replies
View Related
Oct 16, 2008
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.
View 2 Replies
View Related
Jun 21, 2008
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.
View 3 Replies
View Related
Jan 3, 2012
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??
View 1 Replies
View Related
Apr 16, 2008
I have the following data :-
SHEET 1
COL A_______COL B________COL C
V1990_______J100_________U1212
H2323_______Y999
U2222
SHEET 2
COL A_______COL B________COL C
U2222
I0000_______U8900________T67888
H2323
I need to search data from range defined A1.C3 and if any data in that range found in the sheet 2 that having the same records.
View 7 Replies
View Related
Sep 13, 2009
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”.
View 4 Replies
View Related
May 7, 2006
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 Related
Mar 4, 2013
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
[Code] .......
View 1 Replies
View Related
Nov 20, 2009
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?
View 8 Replies
View Related
Feb 7, 2014
Certification and Training tracking.xlsx
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.
View 6 Replies
View Related
Sep 22, 2008
I would like to combine values from multiple records into a single record using a unique identifier. In the example below 'ID' is the unique identifier.
For example:
View 3 Replies
View Related
Jun 6, 2013
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
[code].....
View 2 Replies
View Related
Jun 17, 2014
I am looking for a formula to give me the top10 records from a range.
Please take a look : Top_10.xlsx‎
View 6 Replies
View Related
Jan 9, 2007
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 Related
Oct 25, 2008
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.
View 4 Replies
View Related
Jul 30, 2009
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
View 6 Replies
View Related
Aug 26, 2009
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 ........
View 9 Replies
View Related
Aug 26, 2009
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
View 2 Replies
View Related
Jun 10, 2009
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.
View 11 Replies
View Related
Jul 11, 2006
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.
View 9 Replies
View Related
May 20, 2008
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.
View 6 Replies
View Related
May 1, 2013
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
View 9 Replies
View Related
Jan 20, 2014
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 Related
May 23, 2014
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
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.
View 10 Replies
View Related
Feb 6, 2014
there just want to ask about on how to count records using array attach here is my sample file.!
View 6 Replies
View Related
Apr 28, 2014
I 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
View 1 Replies
View Related
Feb 21, 2008
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).
View 10 Replies
View Related
Oct 7, 2008
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.
View 5 Replies
View Related
