I'm using a table with columns from A to F and an unlimited number of rows. Note that there is a title at the top of each column.
-I'd like my datas in column F to be automatically sorted in descending order from top to bottom when I hit the enter key or when the number is entered in last column (column F).
-the the entire row of data needs to be sorted according to the sorting criteria of column F ( datas entered need to stay on the same row)
-the macro should validate that the number value entered in column F is the same as the number value entered in column A. (to make sure that there was no typing mistake)
-Result in column D must be the difference between the corresponding value in column B and C (B-C=D)
*The next couple of constraints are not absolutely required, i'd like to have them only if it's possible to add constraints with filling color:
-Whenever there is no value entered in column C on a specific row I'd like the entire row to be colored in yellow (fill color)
-IF a number is entered in column C, and that the result of B-C in column D is not equal to zero, I'd like this specific row to be filled in Red.
-when all the values are entered in a row from column A to F and that the result of B minus C is equal to Zero in column D, i'd like this specific row to be filled in a green color.
***in other words, the datas in each rows from column A to F will be entered on 2 occasions; on the first time, only the datas in column A,B and F will be entered (so basically after entering this set of values the row should be filled in yellow. the second time, the remaining values will be added to the rows, so at this time A,B,C,D,E and F will all be entered. when this is done there will be two options
1-the value in column D is equal to Zero then the entire row should be filled in green or
2-the value in column D is'nt equal to zero, then this specific row should be filled in red.
Keep in mind that the datas of a row should be automatically sorted only when the value in column F is entered or when the enter key is hitted to add a new row.
Is it possible to make excel sort ascending or descending but from mid way through alphabet and then loop through the alphabet again. for example. If Cell A1 had "A" in, it would sort as normal.
Cell A1 = A Cell A2 = B Cell A3 = C Cell A4 = D Cell A5 = E
If Cell A1 had "C" in, it would sort from "C" through the alphabet and then loop to the start of the alphabet as shown below.
Cell A1 = C Cell A2 = D Cell A3 = E Cell A4 = A Cell A5 = B
Don't know if macro is better, but I would like to try it with function
I have 2 sheets.
Sheet 1 Column A: Name Column B: Points
Exemple: Name Points Mary 40 Johnny 20 Erick 60 Ralph 30
I want Sheet 2 to get data from Sheet 1 and sort by descending order automatically.
Then it should be
Sheet 2
Name Points Erick 60 Mary 40 Ralph 30 Johnny 20
I don't want to do it manually every time I change some value; I want it to do automatically; so if I change, for example, Erick's points from 60 to 10 in Sheet 1, Erick should appear on the last place in Sheet 2, with his new value (10)
I have a column (B) of randomly generated numbers 1-14, and am using this formula range to sort in descending order and return the relevant value from column (c).
=VLOOKUP(LARGE(B3:B20,1),B3:C20,2,) to =VLOOKUP(LARGE(B3:B20,14),B3:C20,2,)
Works great, except when a number is duplicated, (E.G. 14,13,13,12,11,10,10,9,8 etc). It then returns the first value from(C) repeated, and not the value from the second and subsequent duplicated reference numbers.
I'm fairly new to using excel and have been trying to create a simple spreadsheet that keeps track of scores and automatically displays the names of the players in descending order. Anyway I have gotten to the point of using hookup to match a value and return that players name. But when two players have the same score it will only return the first found value. I sort the number scores using a LARGE function and it works great. Then I use something like this =HLOOKUP(Y2,B27:K28,2,FALSE) to match that value with the players name. How to return the other players name when the scores are the same?
I'm running a workbook that has a list of all the runs for a day, and several daily driver sheets that automatically copy and order the drops using vlookup.
If you look at run 1 on the run listing you will see that holmfirth is the first drop and sandbach is the last drop.
This is also the case on page 1, as it should be.
However, on run 7 you will see that the first drop is Edinburgh and the last drop is Biggar. The problem is that on the run listing form, we sort the run backwards so that it is loaded onto the trunk wagon backwards, then the load is tran-shipped (swapped) to the delivery wagon (dont ask) and the last drop on the trunk wagon (Biggar on the front end) becomes the first drop(on the back end) on the delivery wagon now in the correct delivery order. I need the daily sheet (page 7) to reflect the fact that Biggar is the first drop and Edinburgh is the last drop.
I'm being asked to report on some survey findings containing 60 questions for my company. I've rated all 11 departments from 1st to 11th place for each question.
I want to be able to display a graph showing which departments came first, second and third and also where my department came - in the example of the first question, 7th.
What I'd like is a bar graph (ideally) without any values on the Y axis, the department name along the X axis and text labels at the top of each bar showing the position (ie. 1st, 2nd, 3rd and 7th).
The problem is that if I produce a graph from the following table:
Dept 1 - 1 Dept 2 - 2 Dept 3 - 3 My dept - 7
..then it produces a bar graph with bars in the opposite direction; ie the best department having the smallest bar.
I could just reverse the figures with 11 being the best instead of 1, but then the text labels on top of each bar are wrong. I know I could just label the correct figures by hand with text boxes, but I have 60 of these things to produce!
The closest I came was using the reverse values and having a custom text format to replace the numbers with the opposite text values ie. 11="1st";10="2nd"; but I couldn't get that to work
i have tried: =VLOOKUP(TODAY()-7,A2:M2000,3) to return data that corresponds to a date 1 week ago. Unfortunatley, it does not work because my dates in the array are in descending order. The order cannot be changed... is there another function I can use to return the value I am looking for?
I have a macro built that sorts my data by column in descending order depending on what column of information is selected from the drop down menu.
However I need the macro to sort column F in ascending order, and continue to sort the other colulmns in descending order when selected.
I can't get my macro to switch the sort from descending to ascending order for this one column. Looking to see if this is possible, and if so what macro formula I would use.
Here is what the macro i am currently using:
Sub ddSort_Click() Dim iDdSortVal As Integer Dim CurrSheet As String Dim lFirstSectionRow As Long Dim lLastSectionRow As Long Dim strCol As String Dim lRptHeaderRow As Long Dim iRptMeasColumn As Integer
I have a list of numbers in cells A1:A10, these numbers change frequently. I would like to set up a formula to calculate the 3 lowest values from those numbers and list the three lowest values in cells J1:J3, with lowest value on J1 and the highest of the 3 values in J3.
I know I can use the small/large functions for this, but im not sure how to work it out to use just the 3 lowest values.
Find the sorting method of dates in excel 2010. I have dates in one column and i want to sort it, but I am not able to sort in ascending or descending order.
About descending and related to each others, I mean for example : #700 in column A,B,C,D,E,F,G,H places in the same row and the same happen to #1533.954 and others. About the numbers that are The One ( like 549.894), I need to put them in its column and a new row with empty cells in its other columns. In the end i want a table like this:
I have some sheets which I would like to loop through and use sort descending on column S, would somebody help me out with the code to do so? I actually have the looping code already, I just need the sort descending bit.
I have numerical headers in each column that I am trying to sort in descending order (Most to Least) . I have about 9500 columns to sort. i am just looking to move the columns in a sort order not actually sort the data in the columns. Here is a sample below.
From this:
HTMLSheet3 Â ABCDEFGHIJKLMN 113648576535355315071349485476152746320913334742372721211628213677181341133173311312012040 114169400000200021040544100412070185414260509119080360310070020100401200118103446282223023310120122413
to this:
HTMLSheet2 Â ABCDEFGHIJKLMN115271364134913338576535355315074854764744632092773724127212116281361318133401016733 113121209114420040000200010518401441004127025463600105091198000371200102010401008101100243446282223233132012
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
Is it possible to allow Autofilter but disallow Sort Ascending/ Descending for an excel list in sheet? I have data set up as an Excel List (Excel 2003, with Autofilter turned on) so all formulas, formatting etc copy down when a new row of data is added, however if I just Protect worksheet and disallow Sorting, the List features don't work. I also tried putting the following in the Worksheet Activate event
ActiveSheet.Protect AllowSorting:=False
But this doesn't seem to disable the Sort option in the Autofilter, only the Data Sort function.
I would like to call a macro which would sort rows 4-23 from the highest to the lowest, or if it is already highest to lowest, then sort from lowest to highest. This is how I am doing the current sort(High-Low):-
I have an Overview worksheet that is pulling info from all other sheets in the workbook.
What I want to do is order that info in ascending order depending on the worksheet name.
So I want all the worksheet names that start with 'AB' say sorted in ascending order and all worksheets that start with 'BC' in ascending order.
Now this I have at present working by a macro I created which once the info is loaded onto the sheet I just selected the info and ordered it in ascending order. The thing is though the next time I enter this Overview sheet I might have an extra worksheet that starts with 'AB' and this will not be included in the order, I will have to adjust it everytime so I need a more automatic ordering process.
1) How do make values on x-axis as words? E.g. apples, oranges, etc.
2) Is it possible to have the numbers on x-axis in descending order? If I can figure out 1), I can do this as well, but it would be nice to know if it is possible to have the numbers on x-axis in descending order.
i want to sort column D acsending, then column C descending. i want to do this using VBA because i cant just format the sheet because another vba macro paste the sheet thier which would rid the formatting. (i would do it myself everytime but it is for someone to make it easier for them and not make mistakes..i searched, just found a sum formula post)
I have a list of data with headings number,name, surname,group. Data needs to be kept in the rows as they are now but I would like to sort the order by group. The group order needs to be:
sm, u17m, u15b, u13b, u11b, sw, u17w, u15g, u13g, u11g. I would like to do this using vba and have a blank row between each group.
I'm sure this has been asked many times before, but I can't seem to find an answer that works for my data. I have some data that is sorted by an ordered list (like a table of contents from an overly detailed book):
1.0 1.1 1.2 1.2.1 1.3 ... 1.11 ...
This list can be arbitrarily deep (although probably not more than ##.##.##.##), and because I'm using periods to separate the nested levels (can be changed), I've formatted it as text. This means when I sort the data using this column I get 1.11 before 1.2 (text sort order).
After lots of failed formula attempts I've resorted to using an additional column with hand-entered values to sort by so it will sort correctly:
1.00.00 1.01.00 1.02.00 1.02.01 etc
but I'm hoping someone knows a more elegant way to do this, or at least to generate this column without typing so many zeroes!
I have a client with a list of books, and they are trying to sort a column in a sheet that has LC classification codes. When just doing a normal A-Z sort, it looks like this:
What is weird about it is that I would think that the numbers after the RJ would be in numeric order but it seems to be treating them as codes. So, instead of RJ 41, then RJ 45, it goes on with a series of things that start with RJ 41. Is it normal for this to happen, or is there a way to change it so that it goes 41, 45, 47, 48, then 416, 420, etc.
I feel I've searched thoroughly and wasn't able to find the right answer. I've attached a daily report example that needs the "Product" field to be sorted in a particular order. The order should be JEX, Q3791J, YOO5, KLX9, GHT (all similar products need to be grouped together).
Since the report is run daily the number of rows changes daily: one day can have 50 entries and the next 10. Is there a way to dictate the sort order and insert a blank row between each product grouping?
Once the Products have been separated into their groups, how would I sort each grouping by the "Term" column?
