Multiple Column Sorting
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Sorting Multiple Columns ...
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 Replies!
View Related
Multiple Cell Sorting
I have a multiple Excel sort option question and I hope that someone can answer it. I have multiple rows and I want to sort rows together that match(text or value) in 3 different columns. If there is not a match, I would like Excel to delete the row. I have over 160,000 items and probably only 20,000 match and this is why it would be such a pain to do this manually.
View Replies!
View Related
Sorting Multiple Columns
How do I sort multiple columns at once? In other words, I have a chart that is a series of 1s and 2s, and I need all of the 1s to drop to the bottom, so that I can do a rudimentary chart in spreadsheet form. My chart has dozens of columns like so: 1 2 1 1 1 2 2 2 2 1 2 1 1 2 1 1 2 2 2 1 2 2 2 2 2 2 2 1 1 1 2 1 1 1 1 2 How do I get the entire range sorted to look like this, without having to do each column individually (hours of work)?: 2 2 2 2 2 2 2 2 2 1 2 1 2 2 2 2 2 2 1 1 1 1 1 1 1 2 2 1 1 1 1 1 1 1 1 2
View Replies!
View Related
Sorting With Multiple Rows Per Entry
I've come upon a problem with sorting that I don't know how to tackle... I have entries in a workbook that I want to sort by a transaction number, but each entry spans multiple rows. One "entry" might look like this, for example: TransID PassengerName Ticket# leg of travel: Departure Arrival leg of travel: Departure Arrival I need to be able to sort by TransID or PassengerName while keeping the "legs of travel" attached to the correct TransID/Ticket#.
View Replies!
View Related
Sorting Info From A WS To Multiple Worksheets
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 Replies!
View Related
Sorting A Column
I have a column which represents by each cell value's number the priority of each row in the table. What I need to do is create an embedded code that updates the numbers in that column when any value in that column is changed. For example: Where the cell values in the column are.. 1 2 3 4 5 6 and we change the fifth cell's value to 2 1 2 3 4 2 6 now there are two cells in the column with the same value, we want to keep the value we just changed in the 5th cell but update every other cell that is following the value of 2.... 1 3 4 5 2 6 then I would like to resort the table by these new priorities. similarly if the change is to increase rather than decrease the priority value...where the 3rd cell was increased from 3 ... 1 2 3 4 5 6 changed to 5.. 1 2 5 4 5 6 the new change would become... 1 2 5 3 4 6 in this case the 4 becomes 3 and the previous 5 becomes 4 which keeps their relative place in the priority ranking. I would like this to then resort the table based on this column. I would like this to execute on the exit of the cell when a cell in the column is changed.
View Replies!
View Related
Display Multiple Rows & Sorting
Please ref attached spreadhseet. 1) Is it possible to hide all cols save D & E and also all blank rows in Col D 2) I would like to have an initial If statement saying stating that if the answer to the question in D2 is Y then don't display anything else other than the info in B2 & B3. £) the sorting seems to have gone funny - not sure what I've done here, I'd like it sorted by phase (col A) 4) where there is a blank in the cell under a question (for example D3) - this means that the same question applies and that multiple docs should be returned (e.g for Q1 D2 docs 1 & 2 display, for qu D37 docs 36 - 39 display)
View Replies!
View Related
Sorting A List Of Numbers By Multiple Columns
I want to sort the list like this: 1) If there is a zero (null) value in all 3 months, these records should be at the bottom sorted by record name (I did not show this field in my file). 2) If there is a non-zero (non-null) value in any of the 3 months, the records will be sorted with each other by total change. Is there a way to do this without me doing sorts multiple times and manually moving rows of data around (which is what I have done to arrive at the list I have attached)? I am not experienced with VBA or Macros, and would prefer a detailed explanation if a solution is using either method.
View Replies!
View Related
Sorting Multiple Customers In A Single Cell
I am trying to figure out how I can sort multiple customers in a single cell, and then assign the customers to the outside salesman. The basic idea is in column A I have the customers for a specific job. Assume I have three customers (Alpha, Omega & Gamma) and they are all in ONE cell. I need to sort this and assign each customer to each of my outside salesmen (Bob, Ted, Fred) in another column. The other component to this question is that Bob, Ted & Fred may have MORE than one customer in that single cell........
View Replies!
View Related
Sorting Dates In A Column
I have a list of dates in a column that I need to sort. Dates in columns are as follows for example: 02/15/2010 05/02/2009 06/11/2033 04/05/2044 When I get do a sort I get the following result (it appears to be sorting by month, day, year) 02/15/2010 04/05/2044 05/02/2009 06/11/2033 I want to sort by year, month, day. Desired result as follows: 05/02/2009 02/15/2010 06/11/2033 04/05/2044
View Replies!
View Related
Sorting A Single Column
This is a simple question but I have been playing around with the syntax(unsuccesssfully) for a while. I want to do is sort a column (not the whole sheet). the column selection being determined by the activecell. I know I can use
View Replies!
View Related
Sorting Multiple Worksheet Tabs In Alphabetical Order
I have a spreadsheet saved with one worksheet with all the results on it and 130 worksheets with calculations on them, each with its' own named tab along the bar at the bottom of the page. What I'd like to know is if it is possible to sort the tabs into alphabetical order so I don't have to roam through up to 130 to find the tab (and it's corresponding worksheet) I'm looking for.
View Replies!
View Related
Sorting (or Maybe Filtering) Worksheet With Multiple Data In Cells
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 Replies!
View Related
Letter Color And Column Sorting
I have Danish Office 2007. 1) Let's say I have 3 columns (horisontal) and many rows (vertical. Each row goes together, as the first column can be text that describes a transaction. The second column will be the amount of money and the third can be something else. I know how to sort these by whichever column I want to. But the problem is every cell needs to be the same size. And I have merged 3 cells in the first column and only merged 2 cells in the last two colums. So Excel tells me I cannot merge unless each cell is the same size. Is there a solution here? I need the 3 cells in the first column, so I have enough room to describe the transaction. And to avoid wasting space, I need to only have the other two columns be the size of two merged cells each. Example, though with the text in another column: Picture of it 2) The other problem is that I would like a cell to display the number in red if below zero, green if above zero. I cannot do this. I know where to put in the format codes, but I don't know what to write. At the same time, I need the cell to show the currency in Danish Kroner. So I need a format code that does this. Someone told me this: [Blue]dk #.##0;[Red]dk #.##0;[Green]*dk #.##0 But that doesn't work, nor if the color is in danish. What do I do?
View Replies!
View Related
Macro For Sorting A Single Column
I used the macros found at http://www.contextures.com/xlSort02.html to sort columns. This solution works fine, the invisible rectangle is really good to my interests. Here's how it works: macro #1 creates invisible rectangles at the top of the columns used, at row 1 where the headers are, and assigns a second macro to the rectangles. This second macro sorts the data table based on the column whose rectangle is clicked. The problem is that this solution is for data tables, it sorts the entire table and I want to sort the one, single and only column where I click the invisible rectangle.
View Replies!
View Related
Sorting Numbers With #div/0! In Column
I have some numbers in a column which due to other cells not yet being filled in are returning a supressed #div/0! error. This is fine, but when i go to sort the column it puts them in the wrong order. I would like to record a macro, and assign it to the column header in order to sort the column.
View Replies!
View Related
Sorting According To Previous Column Sorts
I need a macro or some idea on how to sort the following numbers (I hope this makes sense!)... The problem is with the zeros get sorted to the top (or even if I have blank cells sorted to the bottom) and I need them to ignore the zeros and sort according to the value in their column but not any other column. However, I need them to be sorted in order from D, C, B, A. A B C D 0000 4000 101000 0540 0050 6660 ie I need them in the following order. A B C D 0000 4000 0540 0050 6660 101000 Therefore they need to sort based on the following columns. This is because I am charting the values in a column clustered chart that need to be in ascending order.
View Replies!
View Related
Sorting Column With Dates Does Not Sort
In the attached workbook, sorting the dates in column M results in absolutely nothing happening. The dates are formatted as dates (dd/mm/yyyy). The dates in column M are arrived at by adding a number of days (formatted as Number) to another date, the value of which was determined by an array formula. When I retype the actual date into another column and sort that, I get it sorted. Why does the other sort not work? BTW - I actually need to sort column M with column N.
View Replies!
View Related
Sorting A Column, But Placing Results In A Different Worksheet
I have a simple one-column section (column A) of values in Sheet1: Column-A Hal Sonny Betty Adam James I would like to sort this, but have the sorted results displayed in Column-A of Sheet2. How can I do this? Note: I need to be able build flexibility into this such that I can add names to the bottom of the list in Sheet1, knowing that the results in Sheet2 will be able to accomodate the additions.
View Replies!
View Related
Custom Sorting A Column By Date....and Text
I need to custom sort a column. I have 3 different types of data in the column. First - multiple dates, Second - "TBA", and Third - "ASAP". What I need is when the column is sorted the "ASAP" rows will be first, the dates (sorted) will be next and finally the "TBA"s. I have been trying to use a custom list.
View Replies!
View Related
Lock In A Title On Column Allowing Sorting
I am trying to create titles for columns. I can do that by just creating them in the cells above the data. The problem is that the titles sort into the data using the sorting feature. When I lock any cells and protect them, the sorting is locked out. Can I use some sort of header over the columns that remains in place where I can still sort the data below, by clicking on the column and sorting?
View Replies!
View Related
Table Sort Results In 1 Column Sorting
In my example file are 4 columns. I placed auto filter to columns B and C. If column B sorted to ascending then this changes formulas in column D. I attached workbook also to understand my problem. If you try to sort column B to ascending you will see the problem in column D
View Replies!
View Related
Column List Sorting Locking 2 Columns Together
This should be a simple question for those who have the knowledge. I am making a 2 column excel page, the first column will have an authors name and the second one will have the book name. I need to lock these two columns together so that author name and book name always stay together (side by side) on the sort command. I need to be able to sort by author or book title and I realize that it gives you the choice to expand the selection, but I can't trust that the others (kids) will realize the importance of doing so. This is going to be a very large list with hyperlinks and I can't afford to chance whether someone else will select the correct command. So a long story short. I want to build a list that can be sorted by author name or book name and be sure that the correct author will always be beside the correct book, but that are able to be independantly sorted
View Replies!
View Related
Sorting! I Have 1 Column W/all The Info. Together. To Make It Into Separate Tables
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 Replies!
View Related
Sorting Dates Column And Calculating MTD, YTD
So Column 1 I've got dates, need to sort through that and calculate Year-to-date and Month-to-Date values. These are both Sums of the cells.... YTD = Sum of all cells with most recent yr, in this case 2007 MTD = Sum of all cells in Column B for most recent month, Feb2007 here. I've listed the desired solution for YTD and MTD on the sheet as well. (I'm guessing the solution will have something to do with SUMPRODUCT?)
View Replies!
View Related
Multiple Date Column To Single Column & Sort
I'm looking for a way to sort dates from several columns into a new single column (perhaps multiple columns if the entry columns become too numerous). I've included an example. There are currently only 4 columns, but there may be as many as 20 in the future, each with 20 dates under each heading. Any blank cells would be eliminated. If I filled a blank with a new date, that date would be placed into the chronological column. So basically, this would take the date from several different categories and create a single calendar of events.
View Replies!
View Related
Multiple Column Lookup Across Multiple Sheets
After going through multiple threads in the forums, I got this code to do a multiple VLOOKUP method. =IF(E2+F2=0,"",INDEX(C2:C10,MATCH(1,(A2:A10=E2)*(B2:B10=F2),0))) It works perfect on a sample sheet. But when im trying to implement it in a sheet with too much data, it always fails. I have attached the sheet I am trying the formula on. I have grayed down the columns which needs formula's. The data is picked out from the second sheet. This is how I have modified the formula to suit me.. =INDEX(Data!$G$2:$G$663,MATCH(1,(Data!$A$2:$A$663=$C2)*(LEFT(TEXT(Data!$E$2:$E$663,"mm/dd/yy hh:mm:ss"),8)=$I2),0)) <<Please note that all the dates and numbers in the sheet are in "text" format for ease of use>>
View Replies!
View Related
Convert Multiple Column Into 1 Column
to move data from multiple columns and one single column COMPLIMENTUNBLOCKSINCEWITHSHEWORKINGFROMTIMEONSTAFFNAMEEXPERIENCERECEIVEDWORKINGSHOPSUPPOSEDFORATPLANSONGS into 1 single column like this. COMPLIMENTSHEONRECEIVEDFORUNBLOCKWORKINGSTAFFWORKINGATSINCEFROMNAMESHOPPLANWITHTIMEEXPERIENCESUPPOSEDSONGS Can we do this with a formula?
View Replies!
View Related
Countif With Multiple Column
looking to use =countif for an address file and want to filter out duplicates of last then first name. col F has last name col d with first name I've tried using the Conditional Formatting funtion, but am not understanding how to incorporate multiple columns,
View Replies!
View Related
Merge Same Column From Multiple Files
I have hundreds and hundreds of excel files. but in every file, there is the same column lets say column D which has all the information I want. In stead of opening hundreds of worksheets and copying and pasting over the data into a new sheet. Is there a code I could write that would open all these files and copy the data from the same colum over into my new sheet? so column D in the first work book will copy to colulm A in the new work book. Then colum D in the second workboko will copy to the new worksheet in column B ect ect ect.
View Replies!
View Related
Transposing Column Into Multiple Rows
I've got an issue where I'm trying to transpose data from one column into several rows. I've been looking for a macro to help me out but can't seem to find a way to do this. Does anyone have any idea how I can do this? Ideally, the macro would be written so it would find the data in the column and move it to a new sheet and then it would repeat the process throughout the document. The macro would know that the data is grouped b/c of the blanks found between the data set. So as the macro is running, once it hits a blank, it would then copy and transpose the data and continue. Does this make sense? I've posted a sample of the info I'm working with.
View Replies!
View Related
Multiple Column List Box
If anyone can please guide in creating Multiple Column List Box in form Amend for the data in Sheet2 (without having to change the format on the sheet) and delete or amend selected record. One record is equal to Id,Batch,Task Name which is being looped ......
View Replies!
View Related
Split A Column Into Multiple Columns
The spreadsheet contains over 21,000 rows of data, and one of the columns (D I think) contains data as in the two examples below. What she wants is to split this column at the semi-colons ( and have the column header as the "field" name. Unfortunately not all the cells have the same number of "fields" as you can see. Some don't have an "addressLineTwo" while others also have "stateprovince". Is it possible to split the column so each "field" goes into it's own column? Please note that if a "field" is missing there is not two semi-colons to indicate an empty field. I'm also fairly certain that, between them the two examples below show all possible fields. Data Examples. addressLineOne:Road Belen Staana;addressLineTwo:Costado Oeste;city:SAN ANTONIO DE BELEN;highRate:194;latitude:9.97631;longitude:-84.20038;postal4005 addressLineOne:1766 Homestead Drive;airportCode:ROA;city:HOT SPRINGS;highRate:500;latitude:37.99662;longitude:-79.83079;postal24445;Rating:52;stateprovince:US Didn't there used to be a "Split" function that split text over two cells? I'm sure I used it years ago, but can't find any mention of it in Excel 2003.
View Replies!
View Related
Sumproduct Multiple Values In One Column
I am trying to sum up the rows that have multiple values in one column. Here is my curent formula THAT works =SUMPRODUCT(($H$46:$H$5787="EO-Deal Processing-Closing")*($K$46:$K$5787="Submitted")*($I$46:$I$5787="2-medium")) Now I also want to add the following ($K$46:$K$5787="Assigned") How can I get the value I need so that column "K" I get returned both "submitted" and "assigned"?
View Replies!
View Related
Set Multiple Column Widths
I've used the macro recorder to try to auto-apply column widths after I do a CSV import: (A&B=3.14, C=8, D=13.57, E=24.14, F=9, G=10, H=11.29, I=8.57, J=6.86, K=8, L=13, M=10) ...but for some reason when I execute this macro, "every" column gets the width of 10! how I can fix the below code? Sub SetColumnWidth() Columns("A:B").Select Selection.ColumnWidth = 3.14 Columns("C:C").Select Selection.ColumnWidth = 8 Columns("D:D").Select Selection.ColumnWidth = 13.57 Columns("E:E").Select Selection.ColumnWidth = 24.14
View Replies!
View Related
Multiple Dynamic Name Ranges In Same Column
I have a huge database. The rows are broken up by the groups. The columns are broken up by months, quarters, and totals. All the named ranges are the same exact size because all groups have the same category names and all columns have the same amount of months and quarter columns. So it sort of looks like this Actual 2006-----------------------------Actual 2007 Group Category Jan Feb Q1 Total-------Group Category Jan Feb Q1 Total 100 Labor Cost 220 130 100 Labor Cost-Expense Group Category Jan Feb Q1 Total 101 Labor Cost 101 Labor Cost-Expense Hopefully this gives you a good idea of what the spredsheet format is. Right now I have named ranges for all the groups and years. So the top left name is A06_100, then it goes A06_101, etc. What I'm trying to do is set this up as easy as possible to insert rows in the named ranges and keep the named range, because they are referenced a lot in other worksheets. Also if any columns get added. Basically I want it as user friendly as possible so when people change things it stays together.
View Replies!
View Related
|
TRACKER
