Sorting And Filtering The Records To New Worksheet
Jan 14, 2009
I need to do sorting and filtering the similar records to new worksheet. I need you help to do this job, give some idea or command to use for the following steps.
I have attached the worksheet newtran.xls.
1. I need to find the last column, the column will be increased or decreases. so that we need to find the last column at time of running the macro. Now the value is there till column DD
2. I need to concatenate the column B, D, F to last column find out at 1 step. Insert a new column and place the concatenated value.
3. Entire row will be Sorted based on concatenated value column.
4. Find the similar row on concatenated column and entire row need to be moved to new worksheet.
Background: I am HR manager for a construction company & keeper of the call-in list of personnel who are looking for work. I have a simple sheet that has columns:
Date Name Craft Experience ...more info...
If each call-in had only one craft, wouldn't have a problem. Those who are multicrafted ar listed e.g. "EL, MW, BM" In the column C. A caller two days later may be listed as "MW, BM, EL" We input the data as they say it since that is usually their order of expertise. (Yes, I know that it should have been set up with each craft having its own column, but I inherited the sheet & it has 4000+ entries)
I wrote a couple of small macros & assigned buttons on the sheet to allow the users to sort the sheet by date, or name, or craft. My customers (project managers) have requested to be able to sort by craft but have all the folks with any specific craft listed together.
We have an ISP based antispam system which can sometimes incorrectly pickup messages as Spam. I would like to scan through all quarantined messages every week to check that this does not happen. I can export a report from the program to excel but what I would like to do is remove subject lines with common spam words (Viagra, Russian etc) so I don’t need to check these, this would in effect reduce the report by 85%.
Ideally the ‘spam word’ list would be on the second sheet as in example and could be added to as I find obvious words.
The ideal end result would either be all lines which have a word from the ‘Spamwords’ sheet are removed or all lines which do not match the words in the spam list are copied to a new sheet.
I have a list with rows containing NAME, CLUB and TIME (A5:C124).
I'd like to be able to create a new list which would contain the fastest 3 TEAMS along with the combined time (SUM) of the fastest 3 times for each CLUB. Not all CLUBS would have 3 entries and these would need to be excluded.
You can see there that some inserted object (in this case, PDF files shown as icons), are moved. I need to find a way to immobilize every inserted object within each cell boundaries. I want to freely sort or filter and avoid this kind of problem that gets worse with more rows, columns and inserted objects.
I am using Excel 2007 and I have a worksheet with multiple columns, some of these columns are protected and some are not.
I then lock the worksheet with a password so the user can't edit certain cells that are protected but can modify the unprotected cells, the problem is the user cannot use the filter or sort the workbook.
Is there a way to allow filtering or sorting but still lock down the worksheet.
I have a very big Excel file (62 MB). I need to be able to filter by one column (FACILITY) and sort the whole file using the (REGISTRATION NO) column as well as finding the missing sequence number in the (REGISTRATION NO) column. The problem is that the (FACILITY) column has more than 200 different facility name. I am thinking of macro as I have different Spreadsheet I need to do the same steps in each one separately.
I have attached a copy (example from the data that I have) .
How to highlight, filter, or otherwise in someway mark all records on a spreadsheet that do not appear on another spreadsheet?
On Worksheet1, I have columns of data for First, Middle, Last, Clock Number, Address 1, Address 2, City, State, Zip Code and Employment Status. On Worksheets 1 and 2, the data and columns are 100% identical, except Worksheet1 has names I want excluded from a database import process I'll be running on the 2nd worksheet.
Is there an easy way or a quick VBA script that would allow me to highlight or filter out of Worksheet1 any rows that are not in 2?
I have a protected worksheet where I allow all users of the worksheet to filter yet when I filter, Excel gives a run time error 1004 - you cannot use this command in a protected worksheet. Could someone let me know what am I doing wrong?
my VBA code to uniquely filter a column of values ( Cells E7:E65536) in Sheet1, and to paste the filtered results into column B (starting at cell B3) in worksheet Sheet4 in the same Excel workbook. Will the code below work?
More importantly, does the AdvancedFilter method allow for the filtered results to be deposited into another worksheet within the same workbook?
I am creating a workbook with multiple worksheets. When row H on the first worksheet matches certain criteria, I want the whole row that this specific cell is in to copy to a separate worksheet of the same workbook in excel so that a list of these rows automatically compiles. How do I do this?
I 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
I am trying to set up a simple sports picking list using Excel 2010. I would like to be able to carry out several filers and then extract results to a new sheet to allow printing.
For example, there are four available terms and I would like to be able to filter by term/sport and student. Ideally I would like to be able to add a command button once this is working.
I'm looking to use the value from a series of dropdowns (made via data validation lists).
Cell AM5 is a dropdown of named ranges made form a seperate sheet Cell AO5 is a dropdown that uses '=INDIRECT(AM5) to lookup the values in the named range.
I need cell AO5's value to be used to filter rows in the current worksheet. The current problem is when i use the first dropdown in AM5 it still displays the last value, untill i use the dropdown to select a new one. This value typically will not be found and i do not want my code to execute in these cases.
how to find a certain criteria and delete the entire row that the criteria is in. I am more interested in finding certain criteria with vba, in my case any text/non numerical values and clear contents from that cell for the entire worksheet while retaining the rest of the cells that have numerical values in them. My data has --- in cells that represents missing data which would be easier if it was completely blank.
I've found a great userform on this site that allows you to populate a datasheet as well as delete or amend datasheets. It was by dodger7 within Database. Very useful. I've adapted this to my needs and it works great apart from i cant amend the userform that shows data when you select delete or amend. I've had a go but don't understand how i can create my own feilds and set it up in order. When i go in to the code i can veiw the delete and create/amend userforms but this is a search function relating to a reference number. Once you select Find it brings up another form and that is the one which i need to adapt to show my new feilds. I have attached the sheet so you can see my problem. I was wondering if anyone can advise as i love this userform/database method but can't complete the changes to my needs.
I have a product Database with 11 columns. I am trying to build a Search/look-up userform.
I found a brialliant example of the same posted by Roy, UK. I have copied the same and its working well, but with a few little problems.
Here's what I've got:
I have a userform with a Combobox (called Combobox2) where it shows all the products from column B in the worksheet "ProductData". In the worksheet "ProductData" , I have 11 Columns with headings from B1:L1.
When user selects a product from the dropdown list (from Combobox2), all the details related to that product from other columns are displayed on the userform via labels.
This part is working fine....except that the values in Combobox2 are taken from activesheet and I want them to come from worksheet "ProductData". I have hidden the sheet "ProductData".
Second Part is, I have about 20 products in the Database and all of them have a Product Specification Sheet in PDF format. I am just wondering, if there is any way I can have a button on the userform that can used to open this PDF datasheet for the product thats being lookeed up by user.
What I mean is, if user selects productA from the combobox2, then it displays all the info about the the ProductA on the labels on the usewrform. and if user clicks on the "View Data Sheet" button then it opens the Datasheet PDF file for ProductA.
Below is my code for the first part:
Option Explicit
Private Sub ComboBox2_Change() Dim ws As Worksheet Set ws = Worksheets("ProductData") Dim Ncell As Range With Range("B2", Range("b65536").End(xlUp)) Set Ncell = .Find(ComboBox2.Value, LookIn:=xlValues)
It is so nice to check-in once in a while to see all these new ideas and solutions of people's problems. So, I would like to ask a question that really bothered me for some time, and it looks like I cannot find a full answer to it. So here it goes:
How can I import data which is either more than 65000 records long or it will sum up to be more than 65000 when imported? I need all my data to be on one worksheet, and I don't care if these data will be imported let's say in columns A B C, then once it reaches the 65K, it will be imported to the neighbouring columns D E F, etc.?
I searched the Ozgrid for an answer and I found a thread
I am using the combo box that lists the loan officers number from the selection the loan officers name and branch is loaded. I want to be able also base on the officer selected add get the total new loans opened by that officer. I added an if statement that checks if the loan officers number from the "Oct_2012" is equal to the loan officers' selection from the combo box then add all the loans than match that criteria. I am not sure if what I am doing will work but when it reach to the For block it only reads the For statement and then go to the endif and don't execute the statement within the block.
Code: Sub cmbLnOffNum_Change() Dim idx As Long Dim LnOffRow As Long
I have a worksheet with various data autofiltered. I know when I filter on one of the fields, the drop down arrow becomes blue. If I filter on one or more fields, finding which fields I have autofiltered can become hard to find.
This is my question -- Can I put a button or some type of one touch command were I can take those autofilters off and return the worksheet backs to its original state before I autofiltered?
Code: Sub Button1_Click() Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Dim stSQL As String Dim SNfound As String 'Your sqlserver 2008 connection string Const stADO As String = "Provider=SQLOLEDB.1;" & _ "" & _
[code].....
but i dont see where to put in the Database object...
The Database it needs to connect to in SQl is called
The Server name is SQLSRV when you expand databases the database is called SWHSystem the Table is called dbo.Credential and from that i need to get SELECT All from the Name and CardNumber from dbo.Credential and put that in a New Sheet titled Personal
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”.
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.