VLookup Random Numbers - Sort Range In Descending Order And Return Relevant Value From Column
Aug 27, 2012
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.
View 3 Replies
ADVERTISEMENT
Oct 8, 2006
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
View 4 Replies
View Related
Mar 15, 2014
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)
View 1 Replies
View Related
Jun 17, 2009
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.
View 9 Replies
View Related
Feb 16, 2010
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.
View 8 Replies
View Related
Oct 5, 2007
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
ex) 1.751
2.52
3.753
418
417
416
View 5 Replies
View Related
Oct 15, 2012
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.
View 2 Replies
View Related
Jul 1, 2013
I wanna sort these numbers in descending order and related to each others in excel 2010.
H
G
F
E
D
C
B
A
1383.99
1533.954
831.197
1533.954
1533.954
1383.99
700
1533.954
1
[Code] .........
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:
H
G
F
E
D
C
B
A
1533.954
1533.954
1533.954
[Code] .........
View 5 Replies
View Related
Mar 26, 2012
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
CurrSheet = ActiveSheet.Name
[Code] .......
View 1 Replies
View Related
Jun 9, 2009
formula to identify consecutive numbers in order, but having trouble figuring out how to identify consecutive numbers in random order.
Cell M1,N1,O1,P1, and Q1 each have a number, 1,4,9,3 and 7.
We have 3 and 4 being consecutive number but they are not in order, would like help in a formula to put a 1 on an empty cell S indicating that there is a consecutive number with a 1 if there are no consecutive numbers then it would give a 0.
The current formula only works if the consecutive numbers are in order, 1-2, 3-4, 5-6, etc...
=IF(SUMPRODUCT(--(N1:Q1-M1:P1=1)),1,0)
View 9 Replies
View Related
Mar 3, 2007
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)
View 3 Replies
View Related
Mar 25, 2009
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer
For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function
View 9 Replies
View Related
Jan 15, 2014
The logic of the formula like this....
TAB X, Column A contains a data set, each row is a unique value. In column B of TAB X, I want to input a formula that performs the following logic: Search TAB Z, Column C, for an exact match of data contained in TAB X, Column A....and if found, look in TAB Z, Column D...if a blank cell is contained there, then in TAB X, Column A, say "Unsettled" ; however, if there does exist any character (not-blank) in TAB Z Column D, then in TAB X, COlumn A, say "Settled".....but, if the data ID listed in TAB X, column A is not found whatsoever in TAB Z, Column C, then say "NOT FOUND". So for example, within TAB X, Column A there is a unique ID in one of the rows "ABC123", then searches "ABC123" in TAB Z, Column C. If found, it looks in TAB Z, Column D and that cell is blank, so TAB X, Column B, same row that contains ABC123, the result is "Unsettled".
View 1 Replies
View Related
Nov 5, 2008
Im totally new to excel and have some data i need to sort. how to sort numbers into ascending order across a row. ie, I have 34 2 45 79 102 63 etc, and I need them to start low and order to high. i have over 1200 rows, so it would be good to do the whole sheet at the same time.
View 4 Replies
View Related
Apr 3, 2008
I am using the autofilter function on a spreadsheet and the first column displays numerical data ranging from 1 to over 100. When I apply the 'Sort Ascending' from the autofilter on this column, it is attempting to display the data in alphabetical/numerical order however the order it is displaying appears such as this 1, 10 , 100, 101, 102, before moving on with data that starts with 2 (2, 20, 21, 22, 23 etc). Is there a way I can filter this column to display literal numerical data such as 1, 2, 3, 4 etc?
View 2 Replies
View Related
Dec 12, 2012
This is a re-submission of a question previously submitted because the title for the first submission was so poorly worded.
I have a column that has numerical values in random order. I want to locate the first occurance of a value in that column.
I have unsuccessfully tried an Index-Match function - apparently unsuccessful because the values must be in ascending or descending order?
View 6 Replies
View Related
Dec 8, 2009
How do i achieve the graph that is on the pdf with the data in the workbook i've attached. i cant work it out?
View 2 Replies
View Related
Dec 15, 2009
I am looking to create a seperate league table based on percentages in two tables in descending order.
What i want is the not ready times in one ranked table and the call logging in another along with the persons name and percentage.
Im stumped about what is the simplest way to do this.
View 6 Replies
View Related
May 11, 2007
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
Any ideas? ....
View 9 Replies
View Related
Feb 10, 2010
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?
View 4 Replies
View Related
Jun 19, 2014
Check the attachment, i could not make out this using vlookup, how to overcome this problem.
test.xlsx
View 2 Replies
View Related
Oct 25, 2011
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.
View 1 Replies
View Related
Aug 27, 2013
How to rearrange slices of pie chart in descending order without sorting the data?
View 1 Replies
View Related
Jan 5, 2010
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.
View 5 Replies
View Related
Sep 1, 2009
I am trying to sort a column of data in ascending order but I am not getting the results I expect. An example of the data held in the cells is:
4972-1A
4972-10A
4972-2A
4972-1B
4972-3C
When I sort it in ascending order it sorts like:
4972-10A
4972-1A
4972-1B
4972-2A
4972-3C......................
View 4 Replies
View Related
Apr 8, 2012
Im am looking for a way of automatically sorting the data I have in column "C" (C2:C99 to be exact).
The data represents stock codes which are a mix of text and numbers, starting with 1 letter followed by numbers.
I would prefer something I can put in the sheet tab view code section, so that when any new data is added or altered it automatically sorts the stock codes in to alphabetical then numerical order
EG:
A10
A11
B13
B15
etc....
View 3 Replies
View Related
Oct 18, 2012
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
View 2 Replies
View Related
Feb 7, 2012
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?
View 7 Replies
View Related
Apr 13, 2006
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.
View 6 Replies
View Related
Apr 7, 2008
I want to create a button so when you press it, it will pick a random cell and display the info. Similar to the same sort of thing as a 'tip of the day' button you see on so many websites. I have a formula that kind of works from this site, but only when you make changes on a sheet or each time you load it up. I have tried to assign it to a button/picture but with no luck!
View 4 Replies
View Related
