Sorting Info From A WS To Multiple Worksheets
Sep 30, 2009
Here's the issue: I have a spreadsheet with 12,000 contacts in it (name, email, phone number, country, industry, etc etc). The sheet is kind of messy, and I want to clean it up. One way thing I want to do is organize it. I want to sort the Master sheet into other worksheets, and I would like to do this Industry.
Is there a way to make excel register when a contact is in a certain industry, and then subsequently move that contact into a sheet? I tried playing around with If/Then functions, but I think this is a job for a macro/VB expert.
View 9 Replies
ADVERTISEMENT
Oct 24, 2008
I have 3 different sheets of information.
Each sheet has a column of text. With the column of text I have a number associated with it.
I want to ceate a column for each number and have it look at all three sheets for info corresponding with that number.
Ex: 1st Sheet
Excellent condition 20
Like New 20
Poor condition 21
2nd Sheet
No work has been done. 20
Needs Work. 20
3rd Sheet
March 20
December 21
The formula needs to lookup the first number and place the text in the first row, then the next row either go to the next number or if there is not one then the next sheet.
I would like a column that reads something like this:
Sheet 21
in first row - poor condition
second row - december
View 9 Replies
View Related
Oct 9, 2007
I have a workbook that we use for our internal customers to use to place orders for equipment. There are five worksheets within that file that outline the different products. The requestor will indicate what items they want by putting a number in the quantity cell that is appropriate for the product. This could be using a combination of all five worksheets for the one request.
What I want to do is to create a summary page that only extracts the information from the other worksheets that has a value marked in the quantity spot only.
View 9 Replies
View Related
Apr 17, 2009
I need to sort information in a column containing both numbers and words. In the "asending" & "desending" it only gives two options to choose from. (none) & PartNum.
View 14 Replies
View Related
Oct 24, 2008
I have groups of information I need to stay together, but I'd like to be able to sort them by last name. Today, Tomorrow and Long Term are conditionally formatted (=ISBLANK is green and =ISTEXT is red). As long as the information stays together in a group, that won't be a problem. I have a hidden column A where every cell next to the form refers back to the cell with the last name in it. But when I tried to sort by that, it still seemed to mangle my form. There are no merged cells here. There will eventually be dates, locations (booked) and absent, etc.. filled in. I just want to be able to sort this entire block, without changing anything in it, by last name. There are about 100 of these blocks of info.
LAST NAMEFIRST NAMETodayTomorrowLong TermDATEBOOKEDReason for SubAbsent EmployeeHours is WorkingDayHappyPhone Number:555-5555Cell Number:555-1234Special Skills:Notes:
View 9 Replies
View Related
Sep 22, 2009
I have 3 status sheets (about 300+ ea.) that I was given to sort out.
Information:
1) Column A: Number of items (i.e. 1 )
2)Columbe B: Rec'd Date + initials + no. of copies received, followed by notes (i.e. 021709,akb,01)
Since there is only one column with all the information together, is there a way to sort the attached sheet by initials? I don't know how to create a formula to pull all the date,mjg's; date,jac's; date,akb's; etc... into a separate table.
A: No. of items
B: Date,mjg... = Total no. of items
C: Date, abk... = Total no. of items
D: Date, akb... = Total no. of items
View 9 Replies
View Related
Aug 9, 2006
I am installing over 1000 phones for a business. I was provided a list created in excel (not by me) of all of the phones with their locations and phone numbers. Upon successful installation I simply search the spreadsheet for the phone number and place an "X" in the completed column. I do know how to filter the spreadsheet to print only those with an "X" that I submit weekly to show progress.
Now I am given a separate list of specific phones that someone wants completed by a certain date. I have copied this list to " Sheet 2" of the spread sheet. Is there anyway I can have exel mark and "X" in the completed column on Sheet 2 when I update Sheet 1? Can I update the list on sheet 2 with those that are already "X"ed as complete on Sheet 1?
View 5 Replies
View Related
Aug 27, 2009
I have been looking at Macros that sort worksheets alphabetically, but there are sheets within my workbook that I want to leave static. I have two sheets, names 'Start' and 'Spacer', what I would ideally like to have is a marco that will alphabetically arrange the sheets between these sheets, leaving the others where they are and obviously leaving 'Start' and 'Spacer' at the front and end of the range respectivly!
View 5 Replies
View Related
Jun 20, 2006
Is there a formula I can use that would sort information from a master
sheet into up to 4 different worksheets in the same workbook? Right
now I'm engineering IF statements, but I've come across the problem of
blank rows. To get rid of this, I fixed the false value as "zzzz" and
then sorted (because when sorting with " " as the false value, the
values end up on the bottom of the worksheet) but when using the Find
and Replace option, it replaced the zzzz values in the formulas, which
defeats the purpose.
View 12 Replies
View Related
Apr 21, 2009
I have a workbook with two worksheets.
Worksheet one is "Company ID" info, in it are the columns:"Company Name", "street add", "city", "state".
Worksheet two is "Company Contact Person" in it are the columns: a reference to worksheet one column one "Company Name", and column two is 'Contact Person name'.
I want to be able to sort worksheet one at will, By city, or state, or any of the various columns. When sorting worksheet one I want the relationships in worksheet to to remain intact.
"Company Name" - "Company Contact Person".
View 6 Replies
View Related
Nov 25, 2011
I have a workbook with worksheets that are named in various ways that I need a VBA macro to sort.
Some sheets have names that begin with numbers.
I am able to get the macro to sort them all alphabetically but those with numbers are always put at the start of the work book instead of within the alphabetical format I want to use
I may have sheet names like:
CAT 3CAT 1CAT 2CAT MONKEY DOG 4DOG 3DOG
This is how I want these sheets sorted:
CAT 1CAT 2CAT 3CAT DOG 3DOG 4DOG MONKEY
The sheet names are examples and not actual sheet names.
Below is my code so far
Code:
For lCount = 1 To lShtLast
For lCount2 = lCount To lShtLast And Not IsNumeric(Mid(UCase(Sheets(lCount).Name), 1, 1))
If UCase(Sheets(lCount2).Name) < UCase(Sheets(lCount).Name) Then
[Code].....
View 2 Replies
View Related
Aug 29, 2006
Is it possible to keep the sort icon available on a worksheet which is protected? I have issued a spreadsheet to colleagues which contains formulas so I have protected it, but I have now been informed that they need to be able to sort the data according to a ref number.
I thought of using code (which I'm not very good at) and used some from another excel document, but couldn't get it to work...the code was ....
View 9 Replies
View Related
May 14, 2012
I have 2 nearly identical workbooks and I need to update historical data from the old workbook into the newer one.
My current Coding Snippets that I want to use look like the following:
Code:
Sub UpdateWorkbook()
Dim ws As Worksheet
Dim r1 As String
Dim r2 As String
Dim r3 As String
Dim r4 As String
Dim r5 As String
Dim r6 As String
[code]....
Now, this code isn't working I suspect because the Copy and PasteSpecial Functions don't work the way I wish to.
View 4 Replies
View Related
Jan 22, 2013
I have standardized each existing worksheets and any new worksheet to have certain parameters. The purpose of the Macro requested is ensure these worksheets are sorted alphabetically (and only these worksheets). The code I have been working with is contained below. It is working as expected but now I need to incorporate a statement which places the following condition:
Sort worksheets only if A3 has cell value of eCRF NameIgnore worksheet name: TOC & ENTER ECRF NAME
VB:
Sub Sort_Tabs()
'declare our variables
Dim i, j As Integer
[Code].....
View 5 Replies
View Related
Mar 8, 2008
I’m having problems linking data from various worksheets to one master worksheet, all in the same workbook. The workbook contains swimmers PB’s (personal best times) for each stroke and after each gala the swimmers new time is entered onto the worksheet. The worksheet data is then sorted so that the fastest swimmer is at the top.
The master worksheet contains all swimmers by name (alphabetically) and their best times for each stroke, what I am trying to do is have the master worksheet update automatically when I enter the swimmers new PB’s in the individual stroke worksheets, this works when I use ’Paste Special’, but when I go to sort the data with the fastest swimmer at the top, the data in the master worksheet does not reflect the correct times.
View 9 Replies
View Related
Apr 4, 2012
I have a workbook which I'm using to collect monthly data. Each sheet (with the exception of a sheet called 'welcome') is named by month and year. Is there a way to sort these in chronological order?
View 3 Replies
View Related
Oct 17, 2013
I'm trying to solve i have multiple books but each book contains same information e.g
Book1
Sheet 1
Date
Name
Sheet 2
Location
Postcode
Book 2
Sheet 1
Date
Name
Sheet 2
Location
Postcode
etc ... there is also multiple sheets in each book all are same layout just each book is different customer.
I'm trying to do is have a Listbox that shows all customer name and link back to His/her file location. Then retrieve certain information and display on blank workbook?
Question:
Can this be done without opening workbook
Considering i have 20+ workbooks and growing is this even possible
Cannot merge workbooks into one as this info is supplied by third party
View 1 Replies
View Related
Feb 26, 2014
I have multiple worksheets with different data which are as follows:
Sheet 1 - Pricing
Sheet 2 - Selections
Sheet 3 - Summaries (created later with a pivot table)
For the most part I have the sheet working as I want it to, the issue I have is on the selections sheet. The Selections sheet has data validation linked to the All Data sheet via a drop down list, what I would like it to be able to do is to display in the dropdown list 2 separate columns from the All Data list rather than just the primary list.
Allow me to elaborate:
Sheet 1:
Column A; Socket, Socket, Socket, Cable, Cable
Column B, Supplier A, Supplier B, Supplier C, Supplier A, Supplier B
So just to confirm I would like to be able to select the item from column A but it also display (while selecting), column B side by side so that a comparison can be made as part of the selection process.
View 5 Replies
View Related
Feb 22, 2009
I am trying to get a list which counts info based on multiple conditions but I can find no way round this one point
I have a sheet which has all the games played in a particular division and the time that each goal was scored and I am trying to break down the following
What I want is a list that has a column that has the result of how many times when team A were playing at home was a goal scored between the 45th and 65th minute
column A = Home Team
B=Away Team
C= Time of First goal
D=Time of Second goal
E=Time of Third etc
I have a countifs in place for counting the home team and counting if the first goal was scored in under 10 minutes (this is simple as obviously all the first goals are in column C) but what I want to do is be able to have a count of how many times a goal was scored between x number of minutes eg between 40-50.
My problem comes when trying to count the goal times as some matches will have no goals and the cells will be blank and others will have multiple cells filled and the figure I want could be in any column and I also only want it to count only the amount of games a goal was scored between those times so if a game had 3 goals scored in between that time I only want it to count 1.
The sheet has a lot more columns but this is the main sticking point.
View 10 Replies
View Related
May 12, 2014
I would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies
View Related
Aug 22, 2014
I have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
View 1 Replies
View Related
Feb 10, 2009
I need for coloring "Expected Delivery Date" column corresponding with "Control Date" and "Control Result. But I want to correspond with the cells' last content of "Control Date" and "Control Result" But first of all I want "Control Date" should be flashed 10 days ago of it's date... (it's already done on my excel file)
Then
I want "Expected Delivery Date" as green when
- "Expected Delivery Date" =< "Control Date" and "Control Result" =Y (Means Control is Ok on due time)
I want "Expected Delivery Date" as red when
- "Expected Delivery Date" > "Control Date" and "Control Result" =Y (Means control is ok not on due time)
- "Expected Delivery Date" > "Control Date" and "Control Result" =N (Means control ok is not given, delayed...)
View 3 Replies
View Related
Aug 19, 2009
I have a workbook with a single worksheet that has about 2000 rows, columns A, B, C, D, E & F
Cells in colums A, B, C, E & F all have very different information in them, nothing is similar in any of those columns that I can base a criteria on. Cells in column D however will have 1 of about 18 possibilities in them
What I want to do is have another workbook with 18 worksheets, each worksheet named 1 of the 18 possibilities, and somehow magically pull the data from the first workbook and insert it into the correct worksheet in the second workbook, leaving the data in the first workbook intact.
I update the first workbook several times a day, adding and deleting from it so would need to update as it goes, or be able to run the update as and when needed.
My skills are limited to simple formulas inserted into cells and dragging them down!
View 11 Replies
View Related
Dec 3, 2012
I'm not sure what I'm trying to do is even possible, but figured this is the place to ask the question. I'm trying to compare a list of companies (column a) to a list of employee email addresses (column b) and post results (column c) that display all associated email addresses that are unique to company name. Since multiple employee email addresses can be associated with numerous company names.
View 2 Replies
View Related
Dec 18, 2007
Whoever created my customer contacts Excel sheet prior to my arrival entered all of the contacts address information into one cell and in order for me to put this info into Access, I need them seperated. For example:
10000 X Street Louisville, KY 40291 is in cell E2
I would like it to read:
10000 X Street in cell E2
Louisville in cell F2
KY in cell G2
40291 in cell H2
They also did this with phone numbers (ie. desk# / cell# / fax#).
There are over 1000 contacts in this sheet, so it would take forever to split these apart row by row.
View 4 Replies
View Related
Jan 6, 2009
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
View 9 Replies
View Related
Sep 3, 2009
I have a potential of 5 columns of numerical data (simple number entries) which are entered manually in no particular order.
Is there any way of sorting the data so that it is presented in numerical order (smallest to largest) starting with the smallest figure at the start of column 1 up to the largest figure at the end of column 5.
View 11 Replies
View Related
Jun 11, 2009
I'm looking for a way to sort things with multiple decimals (version number). Is there a way to have something like this:
1.1
1.57
1.1.5
1.2
1.68.23
1.23.1
become:
1.1
1.1.5
1.2
1.23.1
1.57
1.68.23
I could do it all manually, but I have to work with like 400 entries at a time.
View 4 Replies
View Related
Dec 23, 2008
I am trying to select multiple rows so that i can sort. The code i have
View 2 Replies
View Related
Mar 10, 2009
I'm trying to merge two or more tables.
The first column of each table is the same field, for example 'Country'. Lets say the first table has information on male population, the second table has information on female population. So i want to merge the tables into one, but here's the problem: table1 has 100 rows (countries), table2 has 96 rows (countries). I need excel to recognise the 4 missing rows of data in table2 and insert blank rows so all the data in table2 corresponds to the correct country in table1 (column1).
Here's a (very) simple example: ...
View 14 Replies
View Related