Sorting With Refrence To Other Column
[data]...
now this is the data i have(theres more but this is just an example), i want to sort it with refrence to 'inv' column such that i hav ....
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Refrence A Cell From One Sheet To Another Issue
I have used this formula ='`Booking Form`'!K28 To refrence a cell from one sheet to another in the same workbook However when I use this formula ='`Booking Form`'!E8 to do the same (different cells) I get this result #NAME? can someone help me with what I doing wrong. As far as I can see the formula is correct as the only difference is the cell references.
View Replies!
View Related
Check If Refrence Library Is Loaded
I call this code from within Workbook_Open......... If these Refrences libraries are loaded then an error appear tells Name conflict with object library........it's clear why...... Is there a way 2 check if the Refrences are loaded then exit the sub i.e. no need to excute this code , and if not loaded then excute the code ? Sub GetRefLibr() Dim ID As Object On Error Resume Next '*Reference ADO Object Library using Major / Minor GUID Set ID = ThisWorkbook.VBProject.References ID.AddFromGuid "{00000205-0000-0010-8000-00AA006D2EA4}", 2, 5 ID.AddFromGuid "{000204EF-0000-0000-C000-000000000046}", 2, 5 ID.AddFromGuid "{00020813-0000-0000-C000-000000000046}", 1, 5 ID.AddFromGuid "{420B2830-E718-11CF-893D-00A0C9054228}", 1, 0 On Error Goto 0 Dim MyVar MyVar = "Come see me in the Immediate pane." Debug.Print MyVar End Sub
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
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
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 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
Sorting
I have a sheet with fixed number of rows but variable number of columns. I have to sort this sheet on the basis of column A starting A2. i am using the following code Range("A1", Range("A1").End(xlToRight).End(xlDown)).Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes But it does not sort properly. To illustrate, i have 10 rows, some 4 columns wide and some 5 columns wide. Since the first row is 5 columns wide , all the rows with 5 columns populated get sorted but the rest get left out.
View Replies!
View Related
Bulk Sorting
I have columns with sort filters, columns with tables, unformatted cells and frozen panes. When sorting one column how do I make all other columns sort simultaneously with it?
View Replies!
View Related
UK Postcode Sorting
I've been assigned an assessment involving excel (2007), which involves me creating a small business system. Part of this system involves a spreadsheet with a list of engineers and the postcode range that they are allocated to to fix callout problems. What I would like to know, is if a callout request is made using a postcode, is it possible to automatically assign the engineer to the request? Example, J Scott's work range is NE10 - NE19 If I entered the postcode NE12 7TD into the callout request form, is it possible to use formula or VB to assign J Scott's ID to the callout request? All suggestions and help are greatly appreciated, I would request that solutions are kept as simple as possible as long as it's not much to ask for D:. A possible solution i've thought of is making 2 fields in each of the engineer's rows, one being the lowest area range, and the other being the highest (e.g. J Scott's would be 10 and 19 respectivly) from there, I could possibly use formulae to assign the engineer's ID, and get his details using VLOOKUP.
View Replies!
View Related
Address Sorting
I've been searching for HOURS on how to solve this, here it goes. My boss receives a report like the one shown. This report has over 1000 rows. She wants to be able to sort it by city but unfortunally, the address and the city are in different rows as well. It seems simple but I'm getting gray hairs over this! Help!!! 1044315KURT WEBB6436 GONDOLA WAY951201/14/2010Bitmap Bitmap OpenView AssignmentUpdate ResultSAN JOSE, CABitmap Bitmap 1045289KYU KANG34 CLIFFSIDE DR94605-30491/14/2010OpenView AssignmentUpdate ResultDALY CITY, CABitmap Bitmap
View Replies!
View Related
Sorting The Cells
in the the attached image of excel i want to see what is the bigger number in the row 49 then sorting these numbers descending and write it in C52 then make a quick equation on in each number ,and this equation is to deduct this figure from each other nine numbers and see what is the zero or above zero result and put it the cell next to this number (column E) starting from E52 example 527.7 is the maximum number so i put it in the c52 and then deduct 527.7from each number ,like the following 41.0-527.7 = negative value (doesnot count) 21.8-527.7= negative value (doesnot count) .... .... so on and we will see that the only greater than zero or equal to zero result is only 1 result so we put the figure 1 in the cell E52 and so on on each number
View Replies!
View Related
Sorting With The Use Of CurrentRegion
'macro sortSales 'You will want to use the CurrentRegion property and look at 'all references to ranges. Any method that does not use an 'If statement, or anything similar, and doesn't change the 'worksheets, except by sorting, is acceptable. Be sure it would 'work on worksheets similar to these but not identical 'As you make changes to the macro, test that it still works for 'Test-Sort 1before you test it for Test-Sort 2 Sub Macro5() Range("A1:G39").Sort Key1:=Range("D2"), Order1:=xlAscending, _ Key2:=Range("B2"), Order2:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal End Sub This wasn't in the lecture and I have no idea where to begin correcting it... The worst part is that I have finals all next week and I need to study for them. I usually was able to find my answers on this forum but I don't seem to be able to do that in this case... any help GREATLY appreciated!!!! I've included the above macro in the attached file.
View Replies!
View Related
Selective Sorting
IS it poss to sort an entire sheet by one column then another, but to exclude certain rows that include certain data. This is what i mean, I have a sheet where data is entered on a daily basis. this data has tobe kept with in its dates. Each day starts with a start time and ends with an end time, and on occasion a break is included within the data. then between dates there is a blank line. IS it poss to sort the data and exclude the blank lines and the start, end and breaks within the data, but keep the data within date, so all data for the 12/4/09 stay within this block...?
View Replies!
View Related
Automatically Sorting
I have information in columns A (First name),B (Last name), C (Misc), D (misc). Is there a way that someone can enter in new information at the bottom of the list and it will automatically sort by the last name once saved or closed ect.. ? I have people not very familiar with Excel and the keep messing up inserting rows and I don't want them to sort. If they could just enter information at the bottom and save it, then when someone opens it to just view it, it will be in order.
View Replies!
View Related
Sorting Of Rows
how do I cut rows and paste into another sheet/file based on the values in a row. For example in row A A 1 123 2 123 3 123 4 333 5 333 6 444 7 333 8 444 I need to copy all the 123 into a sheet/file and the 333 into another sheet/file.
View Replies!
View Related
Sorting Data Out
I have five columns of data A to E. In each row there can be data in either 2 3,4 or 5 of the columns. What I want to do is essentially condense the data. So in columns, say, G,H,I J,K if there is data in column A then It goes into column g, if not it asks if there is any data in column b and so on. Then for H it does the same but one on. I have attached an example to show what I would like to happen.
View Replies!
View Related
Sorting To New Worksheet
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 Replies!
View Related
SORTING BY COLOUR
Some silly twits in my organisation think they are being helpful by highlighting lines of excel and then expecting me to sort on it - how do I do this without going through every line - is there a way of returning the format value of the cell including the colour / background colour ? Some random emoticons for you to look at while you consider... :hungry: :warning: :wink: :roll: :lol: 8-)
View Replies!
View Related
Sorting Hyperlinks
I created a data base that had a hyperlink to a plan of the store so we can find parts easy. With a lot of help for people here on this web site, it works very well. My only problem now is that I sorted my Database by the hyperlink column. It made it easy to do one hyperlink and pull the fill handle down for the rest of the same hyperlink reference. When I went to resort the database so that the parts were in alphabetical order. The sort worked well except that the hyperlink for lets say cell F87 was not sorted. Example : - F87 hyper link presort had the reference to Rack 10 self B. Post sort it had the reference to Rack 16 shelf C. But when clicked on it still took you to Rack 10 self B.
View Replies!
View Related
Pasting And Sorting
I have 2 excel sheets with Donor ID numbers on both sheets. What I need to do is match them up I need 3 rows Original # Assigned # Invoiced # the original number and the invoiced number will be the same number, but I need to copy the invoiced number to the other sheet that has the original and assigned #'s on it and match it up.
View Replies!
View Related
Auto Sorting
Is there a way to have an excel worksheet automatically sort the data everytime new data is entered. I have daily purchase logs that 9 users fill in throughout the day as they make purchases. I would like to have it sorted by pur. amt automatically.
View Replies!
View Related
Formula Instead Of Sorting
I have a spreadsheet that has original data. I originally used the array formula after sorting. But my application is different now. I can not use the sort function or any macros so it has to be a function or filter. Been playing with this one for a while but cant figure it out. Please see file. Also the array wont work if there is a gap in the data. The data needs to be in the final list (green) in order and only one of each can show. Basically I want to show only one of each value or item and then find the sum of that value.
View Replies!
View Related
Chronological Sorting
This is an example of what is being worked on: (There are 500+ more lines like the ones below, in groups of approximately 17 lines per page of information.) * C.B.A. Bank - 2nd reference to branch near Mike's store. (21/03/1956) * Berwick exams results. (06/06/1877) * Berwick exams (09/05/1877) * Steps taken for erection of Berwick. (11/07/1874) * Berwick tenders for work at home. (11/07/1877) * W. was a storekeeper at Berwick. (25/07/1881) * Berwick Brass. (27/06/1877) * Commercial erected for son. (27/06/1878) I need to do the folowing to it: (not necessarily in the following order) - Insert into Excel - Remove brackets - Sort chronologically - Copy back into program I am using again, in same chronological order. The desired outcome is to be able to throw into Excel all the lines and press a button, (possibly the Sort Ascending one) and have a chronological list of all entries.
View Replies!
View Related
Sorting Ranges
I have a table setup in sheet1 and I want the user to be able to choose how this data is sorted. Ie based on a number of column headings. I have a validation list that lets the user pick on of the following options: Club Seasons Pld W D L GF GA GD Pts Titles Won Runners-up The answer is given in sheet2 cell C3 What I want is some code that will sort the range B10:N51 by the column heading result given above.
View Replies!
View Related
15 Different Currencies- Sorting
I am mapping this file on the basis of a lot of fields. But the end product has 15 currencies. My requirement is to separate the accounts on the basis of the currencies and then consolidate it on the basis of GL account. For example, first I have to find all the accounts that are in USD and then if there are 2-3 similar GL accounts, then I will consolidate on the basis of the GL accounts. Please find attached file.
View Replies!
View Related
Inventory Sorting
I am doing inventory right now and need help sorting numbers that I have. Ex: I have numbers like 10 1001 101 AB1394-10 AB2948-MPG XD89-MPG KIT F64 KIT GSW294-HD10 I need something that will help me sort it by the numbers only (numerically by the first part only). I want the above numbers to look like the following after sorting: 10 F64 KIT XD89-MPG KIT 101 GSW294-HD10 1001 AB1394-10 AB2948-MPG Basically, I'm trying to get the computer to sort the numbers numerically by disregarding any letter before the number.
View Replies!
View Related
Sorting Of Apostrophe
I have to match up two columns of data from different sources. In one column the apostrophe is being sorted as a space, in the other it is being ignored. e.g Col A Misty M'Lady (ignored) Mohayer Col B M'Lady (read as space) Misty Mohayer Most advice I see concerns removing hidden leading apostrophes which is not the case here, others recommend Control and H to find and replace. This should work but does not as Excel does not seem to read the apostrophe at all. I have tried Text to Columns > Delimited > Other ' to remove and then would have done =A1&B1 to put the text back together, but again this just does not read the apostrophe.
View Replies!
View Related
Sorting In Workbook
I'm using the following code to sort my worksheets and all was well until it changed from worksheet 9999 to 10000. Now it sorts 10000 and above and puts them at the opposite end of the workbook!! Is this the revenge of the millenium bug? how to tell it to stop doing that immediately and treat 10000 as a number as well!! lShtLast = Sheets.Count For lCount = 1 To lShtLast For lCount2 = lCount To lShtLast If UCase(Sheets(lCount2).Name) > UCase(Sheets(lCount).Name) Then Sheets(lCount2).Move Before:=Sheets(lCount) End If Next lCount2 Next lCount
View Replies!
View Related
Dynamic Sorting
i have a table with 4 columns: (customer name, revenue, expenses, profit) in cell A1, i can select from either of the above headings and what i wanted to do is sort the mentioned table based on the heading that's selected in A1. for example, if i select Customer name in A1, then the table in (B2:E12) is going to be sorted by customer names (alphabetical)....or if i select Revenue in A1, then the table will be sorted by revenue values...etc.
View Replies!
View Related
Sorting Numbers Like 1.2.2.3, 12.4.2.3 Etc
I should sort numbers in ascending order from left to right, but the numbers sort only from right to left. This is what I want: 1.3.2.2 1.4.1 2.1.1.1.1 3.2.3.12 3.3.1 And this is how it goes: 2.1.1.1.1 3.3.1 1.4.1 1.3.2.2 3.2.3.12 There are also numbers that have letter at the end (1.2.3a), and the cell format is text.
View Replies!
View Related
Sorting 4 Columns Together
Is there a code or some way for me to sort 4 columns together? I want to sort a list of employees and for each employee theres a column with their Lost Business, customer satisfaction, and two more columns. I want to sort all the columns at the same time so that the best employees overall will go to the top of the list and the worst ones will be at the bottom. Whenever I use the sorting feature it does each column independant of the others so everytime I sort a new column it just moves around the last one I sorted.
View Replies!
View Related
|
TRACKER
