Sorting Data Within Cell Low To High?


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Not even sure if this can be done using Excel because it would change the value of the cell. I have a column of 10 numbers that contain up to three digits such as 6,42,74,98,105,184,382,583,634,796. I have used the data sort key to put them lowest to high in the column, but here is the catch.
I would like the program to sort these numbers in the next column over or in same column if easier to read lowest to high with 0 being the lowest so that the above numbers would be 6,24,47,89,15,148,238,358,346,679. Hope you follow what I am after... Any help with this would be greatly appreciated.
Thanks
Similar Excel Video Tutorials
Extract Top 5 Records w Formula
 Extract top (max high) 5 records w formula where duplicates are allowed. See how to extract the top (max high) five values and associated names from a ...
Similar Topics
hi
let's say i have these numbers in COL A
2
3  new high
12  new high
6 >>> min in valley 6 less than last peak
22  new high
14
17
12
10
8 >>> min in valley 14 less than last peak
16
23  new high
22
etc
thus as each new high is reached, the numbers might back off. in the above example the greatest difference between peak (new high) and valley (lowest low between new highs) is 14
is there a formula i can place in a cell that says:
"take a look at all the numbers on col A, and show me the greatest difference from any new high made to the lowest low until the next new high is made"
thanks
tx
Let's say I have 4 rows of numbers...I want to rank them high to low,without having to do a data sort..I would like to assign a number in each of these rows ( with a new column,next to the #),the highest # being assigned a 1 and the lowest # being assigned a 4......is there some kind of sum if /max/min formula that would allow me to do this ?
Thanks
I'm creating a spreadsheet to use for a high school golf tournament.
I need a formula that will take the lowest 4, and only 4, numbers from a column of 46 numbers. All numbers must be greater than 0 (in case one team has fewer than 6 players.) In the extreme, the numbers could range from 65130, though almost all will actually be between 70100.
Has my meager skills stumped, as it does the computer literacy teacher in our school.
I would really appreciate any advice, or knowledge if anyone has done one of these before.
I have a column comprised of numbers from 000999. I also have one number in three separate columns as well for your convenience. What I want to accomplish is: sorting these numbers from low to high or high to low.
The numbers look like this...
125
528
863
417
The result I'm looking for:
(low to high)
125
417
528
863
OR (high to low)
863
528
417
125
Appreciate your help.
Hi All
I have a worksheet with numbers in rows 334 and from columns D T, I need a macro to sort the rows 3  34 numbers only from low to high numbers.
So to sum up it will sort row 3 from lowest number to highist number then move on to row 4 do the same then row 5 etc
Thanks
Colin
Dear Friends,
I have a set of some prices. Columns Headers are OPEN, HIGH, LOW & CLOSE.
The set has a 10 days of data. Now at the end of the last record one cell is having a price of Last 5 day's Highest High Value and another is having a cell value which is the Lowest Low of last 5 days.
If the last record is Lower than previous 5 day's Lowest Low than Cell Value will be Highest High of previous 5 days.
AND
If the last record is Higher than previous 5 day's Highest High than Cell Value will be Lowest Low of previous 5 days.
I do not know whether it is possible to do it in a single cell. I tried but failed. Please guide & help.
Thanks & Regards
Avinash Raste
I need to sort rows by column O, lowest to highest (greater than 0), then by column A (date). Let's say the the lowest number after sorting is 5. I would like the 5 to change to 1, then take the next lowest number..hypothetically 13, and change it to 2, the next lowest..24 and change it to 3, and so on until the sort is complete and anything with a zero is under the newly sorted rows. Hope this makes sense. This is WAY above my comprehension but one of you gurus who continue to amaze us novice users can help? Thanks in advance...you ladies and gentlemen are amazing!
I hope this one will be an easy one to solve!
I have 2 cells with a range of numbers  lets say A1 = 5, B1 = 8 (A1 is my lowest range, and B1 is my highest number in the range).
When I enter a number in say, cell A3 I want cell B3 to contain a formula that will give a variety of responses depending on the number inputted to A3. For example, if the number 4 is entered into A3 then B3 will say "too low" (ie: its lower than A1). If the number in A3 is 10 then B3 should say "too high". If the number in A3 is 6 then B3 should state "within range".
The idea is that i can easily change the lowest & highest numbers in my range by changing the numbers in A1 & B1 rather than changing all the formulas everytime.
Hope that makes sense.
Hello,
I was wondering if I could get some help sorting this data
Currently, there are many cells with a name followed by (#) # for any number
I have used the two columns to the right to only leave behind the # in order to sort them.
Is there any way to sort these numbers from highest to lowest or lowest to highest and still keep the row in tact. I would like the name to also follow the sorting of the number so that they will match up in the end.
I have attached a test copy that can be used as a reference.
thanks
Vargs
Can anyone help please as i have been searching for hours to do the following:.........
I have column M on a worksheet listing 9.25,8.66,8.63,8.73 and 9.63 on rows 1014 ... in column S i have to sort the numbers from highest to lowest but number them 15 (1 being lowest and 5 being highest)
So column M will have the list as above but column S will have the numbers 4,2,1,3,5
Can this be done, any advise gratefully received.
Many thanks
Jane
Hi,
My setup:
OS = Win7
Excel = 2007
I am trying to sort 1611 rows of data, which is under 5 column headings, the data looks like this:
The sort needs to sort through the data and arrange them by LCHKIN from lowest date to highest, then from TOT CHCKOUT from lowest to highest, and finally the CALL NO from lowest to highest. In order for you to see which books were used the least, and when last they were checked in, and by sorting the call no. makes it easier for you to find the book on the shelves.
These are the steps I used:
1. CTRL + A
2. Select the Data tab
3. Click Sort
4. Add the following sort functions seen below:
However, these are the results I get, which is incorrect to what I wanted.
Hi there.
I am using the following formula in a cell to get the High Price of EUR/USD for that day (forex, quotes from MT4) :
=MT4HIGH!EURUSD
Now  on my spreadsheet. Users are required to enter the Highest price met for the duration of their trade AND the lowest Price met for the duration of their trade.
I have 2 problems:
The HIGH price quoted (using the formula above) is not necessarily going to be the Highest Price during (users) trade. Example, that high price might have been hit well before user even enters into a trade.
So  i have figured a workaround (i think) but just need a bit of help to implement this.
I need to write this in VB code.
(1)
Once a user enters into a Trade  i will need the following formula pasted
into column active Cell row + column BP. However  this formula needs to change depending on what the user selected in Active Cell row + Column C.
Example, JPY/USD or EUR/GBP or EUR/USD, etc, etc.
(2)
Since i need to grab the Highest & Lowest point reached during that particular trade. What VB code can i use to look at the cell's Highest Value reached (since this will always be changing) and then store that data and (also) for the cell's lowest value reached and then store that?
So now we have the entry point fixed because the formula for getting the live quotes ONLY gets entered into the cell once user enters into a trade.
However, once user ends this trade (which will be on Active Cell Row + column BO) i would then like the formula in Active Cell Row + Column BP to get overwritten with the stored data.
I hope all this is possible.
Hope someone can help.
Thanks in advance.
Hi, if I have 3 fields filled in with numbers (A1 to A3), I want to find a formula in column B that indicates between B1 and B3 LOWEST, MIDDLE and HIGHEST.
Is there any formula that can compare A1 to A3 and give the above output in the B column next to the numbers?
This ones a little complex ( I think anyway )
What I need to do is have a column scoped for say the #2..
lets say column H has mins pop up every minute.. but not shown until that min has arrived.. like it will show intevals down to 0 mins left.. on each webquery refresh.. 20 then 20 and 19 then 20,19,18 then 20,19,18,17 etc etc
like so..
20
19
18
17
16
15
10
8
6
5
4
3
2
1
0
now on each minutes rows are numbers from say column J to P
what I need to do is find say minute 2 ( 2 in the column H if there is one up yet) then select J:P for #2's (the min) row.. and highlight the Ranking of the lowest number, the second lowest number, the third lowest number and the fourth. All in green.
Sometimes there may be 2 3rd lowest numbers or more.. or 4th lowest..
any ideas? I have it in my mind but cant/dont know where to start lol.
Id probably make some super long macro that is time consuming and not effecient.. I read somewhere on the net that I am classified as a "Spaghetti Programmer" or something lol.
Any help would be awsome!
I'm trying to set up my own Golf Handicapper (yes I understand that I can download one or use a free version online, but I like to tinker in Excel)
I have a list of Handicap Differentials I want to take an average of but, certain conditions apply:
If I have a X differentials I want to take the average of Y of them.
56.......................1 Lowest
78.......................2 Lowest
910.....................3 Lowest
1112...................4 Lowest
1314...................5 Lowest
1516...................6 Lowest
17........................7 Lowest
18........................8 Lowest
19........................9 Lowest
20......................10 Lowest
I've set up a named range on a different sheet with these figures. And have used VLookup and Count to get the number of differentials I need to take.
Where do I go from here.
Hi all,
I rarely use Excel so my knowledge of it is rather basic. Anyway I have a few hundred thousand lines of data in an excel sheet, and I would like to create a few graphs from it. The data is structured like so: first column is the date, then I have two other columns with numbers  one of them has high numbers and one of them has low numbers. There are typically over 1000 entries for each date.
What I want to do:
For every unique date in the main data source (i.e. Sheet1), I want to get the highest number from the "High" column and the lowest number from the "Low" column and plot the difference between those two (high minus low  i.e. the "range") on a graph. In my mind I see it like a bell curve, most days will have a similarish range and so should bunch up in the center. Fewer days will have substantially larger or smaller ranges and so will be the small outer part of the bell curve (my book on statistics should be arriving shortly, then I'll be able to say this stuff intelligibly lol). I'm guessing the x axis will be the difference number (i.e. if 20100101's highest high is 200 and lowest low is 50, then is should make a mark at 150 on the x axis  and if I get another with the same value then it would go above that one, so as to increase the height to show multiple entries at that number...)
In short, it should be able to find any unique date in the Date column and then get the high/low for that date from the 1000+ entries for that date anywhere in the data, and plot the difference between the two on a graph. And do this for the other 260ish dates, which each also have +1000 entries each. I don't know the dates beforehand, it should be able to handle any unique dates thrown at it since there will be +10 million entries from lots of years (not all at once lol).
I'm using Excel 2007. Example of data attached.
Any help appreciated. Cheers
I have a long list of temperature difference data. In the list is sets of 24 numbers, that correspond to an hour every day. So pretty much I have 24 values (1 for each hour of the day) and then a blank row, then 24 more numbers, then a blank row, 24 more numbers, and then a blank row... etc etc... and this goes on for approximatly 7 months.
I want to pick out the high value and the low value out of each of the 24 values (ie. the highest and lowest number each day). Is there any way to do this? I don't wanna have to go to each day, look at the 24 values and see which is the highest and the lowest because over a 7 month period, this would take a long time, and I have to do it multiple times in different worksheets
Thanks for the help
Anyone know how to easily calculate the average of the lowest say 5 numbers in a series of numbers?  I can't simply sort the data since their are hundreds of lines of data that change each monthly update. Just want a formula that will look at the series in columns a to m and tell me the average of the lowest 5 numbers
Hi all...
I have a sheet with random numbers from 100 to 100 in a1:a50, in b1:b50 are names.
In c1:c4 I would like to return the 4 highest numbers in A in decending order.
in C5:c8 I would like to return the 4 lowest numers in A in ascending order.
in D1:D8 I would like to return the names from B that correspond to those high and low numbers in C, the numbers in column A are not unique.
I know how to pull the 4 highest an lowest useing large and small but I cant figure out how to pull over the name from B?
Thanks,
Soood
Hi! I have a long list with names of banks. I also have three different numbers (between 1 and 10) for every bank. Eg Bank A 5 7 4, Bank B 5 9 3. Sometimes there are not three numbers for a bank but only one or two. There are never more than three numbers though. I want to have a forth column. This column shall give me a new number based on the other numbers. The sorting shall work like this. If a bank only has one number then that number shall be presented in the forth coulmn. If there are two numbers then the lowest one shall be used. If there are three numbers then the two highest numbers shall be chosen and if they are different the lowest one of the two highest shall be presented in the forth coumn. I do not know if you can do this using normal worksheet function of if a user defined function is necessary. PLease any help very much appreciated! Thanks alot!
(1) A Formula to take the value in Column A and say (if B2>A2, "High","") Then calculate, HighD1, result multliped by E1and indicate this result in COLOUR. Or say EXCEEDS.
(2). Second Formula to to pick up all High and multlipy by G1 and which are not High multlipy by G1 as well. (This is to find out the Heighest and Lowest percentages.)
Hi. I have got Daily High Low and Close data for stock prices for many years.
ie. 8/1/2009 100 90 95
8/2/2009 98 92 96 etc.
I would like to find the Highest High, Lowest Low for each week, as well as month. I would like the Highest High, Lowest Low and ending( last day of week and month) Close for the week and month to be put in separate columns as and when they occur so that I can make use of them in calculations.
I hope I have made my self clear. I hope someone can help. Would be much appreciated. Thanks.
I have an application that produces a column of 6 numbers, and most of the time these numbers are close in value or even have two or three of the same values. But others will be way off. Here is an example
28.5
28.7
29.2
16.4
28.7
21.2
I am having to find a way of pulling out the three lowest numbers, but the numbers within a few percent of the pattern that the column is establishing. So above, the three lowest would be considered 28.7, 28.7 and 28.5. The actual lowest numbers are considered anomolies. What I would like to do is use something like MODE but have it find numbers with approx 2% of each other so when I get a column with numbers within a tenth of each other, I will still get a MODE return. Thus in these numbers,
28.5
28.6
29.2
16.4
28.7
21.2
28.6 would be the MODE number returned. Any suggestions?
Thanks,
Bill Biggs
Hi I need help with a spreadsheet!
I have a range of numbers 
76.20
76.20
76.20
75.95
50.80
50.80
50.80
50.55
25.15
25.15
25.15
25.15
25.15
18.80
12.44
12.44
6.09
2.91
2
1
I need to be able to input a number eg: 1225 and pick from this list to make up this exact number starting from the highest number to the lowest number..........some of the numbers may appear more than once, as I go down and use the higher numbers I need it to move down to the next lowest number once these high numbers have run out.
Is this possible to do? It is used to work out spaces inbetween blades on a steel slitting machine. So they may be asked to cut 180.00mm out of a 1500mm wide sheet so I need to set up the spaces inbetween two blades to 180.00mm.
Any help would be greatly appreciated.
Melanie
Trying to use the High water Mark method in order to establish a draw down, from new high to low.In the sheet attached looking at "Simple Example" Column V gives the Time series. So the highs occur at 36,48,56,61,70 and 75.What I am trying to calculate is the lowest values between 2 highs. For example between 1st High(36) and 2nd High(48) the minimum value was 12.Between the 2nd High (48) and 3rd High(56) the minimum value was 20 and so on. Thus the draw down between 1st and 2nd High was 1st HighMin value(1st high2ndhigh)=3612=24.
Thus in a whole time series I need to produce an automated calculation rather than manually looking at the values and by hand selecting the ranges.
I have made an attempt with the complex example side.