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.
View 11 Replies
ADVERTISEMENT
Nov 1, 2008
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.
Example (Excel 2003): ..
View 4 Replies
View Related
Jul 30, 2013
I have a table in excel which looks something like this:
Product Ref
Date
Description
1
29/05/2013 19:58:50
Product A
[Code] ....
All I'm trying to do is get the only the first dates for each of the related Productsrefs. So the result should look like this:
Product Ref
Date
Description
1
28/05/2013 19:26:48
Product A
[Code] ....
Is there a built in function in excel that can achieve this?
View 3 Replies
View Related
Nov 29, 2007
in the table i have
A1-apples
A2-bananas
A3-eggs
A4-balls
A5-bananas
A6-apples
A7-apples
I need one list that will show me all items that are on list so i would get:
A50-apples
a51-bananas
a52-eggs
a53-balls
to simpilfy this list would show me what types are there in the first list
View 9 Replies
View Related
Feb 5, 2010
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.
View 8 Replies
View Related
May 30, 2009
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.
View 10 Replies
View Related
Dec 7, 2013
I have an excel database that contains a code to identify specific people.
NAME ADDRESS PHONE CODE
Jones 3 Quay St, PN 063586954 JU79N4
White 24 Dyk St, PN 063547786 9GVJ64
Smith 9 Random St, PN 063512698 4LN867
Butt 89 Yeah Pl, PN 063569986 D920HK
Handle 69 James Ct, PN 06 3549687 ZK26S84
If I wanted to filter the list so I only had codes that had Z, N, H in it. How do I do that??
View 5 Replies
View Related
Apr 23, 2013
Here is the original table:
This is with a filter on:
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.
View 1 Replies
View Related
Jan 30, 2014
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.
View 3 Replies
View Related
May 27, 2014
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) .
View 14 Replies
View Related
May 1, 2014
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?
View 1 Replies
View Related
Dec 12, 2007
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?
View 9 Replies
View Related
Jul 25, 2006
I have a spreadsheet which is a list of dates and amounts.
I want to have a filter on a second worksheet wherein I can type the date and it will filter all amounts for that date.
I've tried the Advanced Filter but I can't get it to auto filter when I change the date. Also it needs setting up every time I do an advanced filter.
View 9 Replies
View Related
Apr 10, 2007
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?
Worksheets("Sheet1").Range("E7:E65536").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Worksheets("Sheet4").Range("B3"), _
Unique:=True
View 3 Replies
View Related
Apr 1, 2013
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?
View 1 Replies
View Related
Apr 21, 2013
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
See the attached sample file.
HOW TO GET THE REQUIRED RECORDS ONLY.xlsx
View 14 Replies
View Related
Sep 30, 2012
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.
View 5 Replies
View Related
Nov 3, 2009
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.
View 4 Replies
View Related
May 11, 2006
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.
View 2 Replies
View Related
Jan 11, 2010
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.
View 2 Replies
View Related
Jul 7, 2006
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)
View 9 Replies
View Related
Feb 28, 2007
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
importing more than 65K records
View 8 Replies
View Related
Feb 14, 2013
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
[Code]....
View 1 Replies
View Related
Mar 6, 2008
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?
View 9 Replies
View Related
Oct 18, 2012
i found this code...
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
using Excel 2010 connecting to SQL 2008
View 4 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
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
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
View Related
Jul 15, 2009
I have a report to compile where i have a master list of data with three columns.
A=Name B=Type C=Location
I have 3 Types to sort. These being D, E, F
I need to sort into three new worksheets from the first sheet the D's into one, the E's into another and the F's
into the last.
I have over 3000 different A's to sort by the Type.
View 9 Replies
View Related
Oct 28, 2009
This might sound like a weird question, but is there any way to prohibit a user to using the sort function in a particular sheet?
Filtering for values is okay but sorting must be prohibited so that no rows "change place" in a sense.
View 4 Replies
View Related
