Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Sorting Data Within Cell Low To High?

Forum Register
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

View Answers     

Similar Excel Tutorials

Sorting Data by Date, Text, or Number in Excel
Sorting Data can be done with a few quick clicks of the mouse. I have used the same data as the previous 2 tutorial ...
Sort Data Left to Right in Excel
How to sort columns of data in Excel.  This is the same as sorting left to right. This will change the position of ...
Case Sensitive Sorting in Excel
How to perform case sensitive sorting in Excel. Select a cell within the data that you want to sort and then go to ...
Sort Data Alphabetically or Numerically in Excel 2007 and Later
This Excel tip shows you how to Sort Data Alphabetically and Numerically in Excel 2007. This is a great feature to ...

Helpful Excel Macros

Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Sort Data With Headers in Ascending Order in Excel
- Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.
Sort Data With Headers in Descending Order in Excel
- This Excel macro sorts data that has headers in descending order. This means that data is sorted Z to A and 10 to 1 - o
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.

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


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 4-6 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 65-130, though almost all will actually be between 70-100.
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.




Hi,

How i do, i'm trying to apply some lottery filters on a Tickets that i'm producing on daily basis,
(i know many on them)

this filter i Called "Low/High"

For example :

1-.the Lottery have number is 36 (lowest 1, Highest 36)

2-.the 36 Numbers are Identified as two section (Low= 1~18), and (High=19~36)

3-.In order to Filter out "bad combination", the Array Formula (if any) should
a-.identify / count how many numbers are from LOW and
b-.how many numbers are HIGH

Note :
Combination are in a single CELL,
Combination are with "-" e.i ( 01-02-03-04-05 )
Combination are with 2 digits e.i ( 01-02-03-04-05 )


Example :



Please Login or Register  to view this content.


please I need to get L/H on Column J

Is there a way to do this with function formula?


Thanks YOU ALL.

David.

I have a column comprised of numbers from 000-999. 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.


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.


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.

5-6.......................1 Lowest
7-8.......................2 Lowest
9-10.....................3 Lowest
11-12...................4 Lowest
13-14...................5 Lowest
15-16...................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.


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. 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.


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 High-Min value(1st high-2ndhigh)=36-12=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.


Dear All,

I have an excel sheet with 121 rows of stock data ( Open, High, Low & Close. ) and a Candlestick Chart of it.

Now I want to change the scale of " Y " axis according to the prices of the stock. For eg. If a stock is renging between 100 to 300, that means, Lowest Low of the stock of 121 days ( Data in The Sheet ) is 100 & Highest High is 300 then the scal of " Y " Axis -

Minimum = 80 ( 20% less than Low )
Maximum = 360 ( 20% More than High )

So, the scal of " Y " Axis will change automatically according to the price of the stock after calculating Lowest Low & Highest High of the data given in the sheet.

Is this possible by Macro or by coding ? If yes, please help.

Thanks & Regards

Avinash Raste


I have a spreadsheet with cells that update in real time. Is there a way to monitor the highest and lowest values in these cells? I would need to output the data to another cell so that every time the monitored cells hit a new high or low, the other cell would be updated. Thanks


Hi there,

Anyone got any suggestions how I go about getting excel to do this or if this is even possible?

Column A has an eight digit order number, however only digits 5 & 6 are used for sorting the orders (the others numbers show location etc etc), is there anyway in excel I can sort them in order based on digits 5 & 6.

Also when the order numbers are the same I would like to sort according to column C (with is the order cost) from low to high.





Thanks for any help.


Hi!
Used the search engine but couldn't find an answer to my question.

I'm trying to sort numbers, but instead of getting my numbers sorted from high to low or vice versa, it looks kinda random.
When I try to sort the following numbers from High to Low:
1.000.000
1.037.037
0.703704
0.740741

I get this:
0.740741
0.703704
1.037.037
1.000.000

Are the decimal points a problem perhaps?
Also the Max and Min formulas are giving me troubles as well with this data.


I have a large spreadsheet with many rows of Dollar values. How can I sort the lowest/cheapest price of my "total" line (row 48). The colums effected are colums E through X. I want to keep all the data with its respective column/row also. Thank you.


Basically I want to sort rows 10 - 20 based on the number in cells J10 down through J20 from highest to lowest. Then sort rows 22 - 30 based on number in J22 down through J30 the same way. I have five sections like this I want sorted but dont want the data from rows 22 - 30 mixing with data in rows 10 - 20 (or any other sections below it.)

I think it might be easier to "protect" the ranges or something? Like if I can seperate a group of rows (10 - 20) (22 - 30) etc. and then just put a formula into column J that will adjust the rows in each section always from highest number in the J column to lowest.

Am I making sense, and can anyone help me?

Thanks,

Drew


Hi Guys,

I was hoping someone could help me (it's Probably really easy but I've tried for a while and can't work it out).

I have 2 columns next to each other where the answer in the cell can be either high, low or medium. I'm trying to come up with a formula for the 3rd column where it can bascally say if column 1 is high and colum 2 is low the answer for column 3 must be medium etc.

i.e.

Low + Low = Low
Low + Medium = Low
Low + High = Medium
Medium + Low = Low
Medium + Medium = Medium
Medium + High = High
High + Low = Medium
High + Medium = High
High + high = High

Can someone at least put me on the right track by letting me know if it is an IF formula or AND or both please.

Thanks,


Jon


How do I find the x lowest or highest values in a range?

I have rows of numbers 10 columns wide and I want to find the 5 lowest values in each row.

I ultimately want the average of the 5 lowest values, but I can figure out the averaging part.

I can figure out how to do this with a sort, but given the number of rows, sorting each row would be tedious.

I just posted another question sorting multiple rows for a closely related problem. If that is answered, I can figure this out from that, but I wonder if there is a function that can find the lowest (variable #) values in a range.

Thanks!

JP


Hi guys,

Any help would be greatly appreciated.

I have a list of 20 numbers and only want to average the 10 lowest numbers in that range (without needing to sort them first)

Thanks,


I have data being fed from an application where it's difficult to sort the rows into ascending order. So is there a way that VBA can sort them and produce a value according to the order?

For example, Column A contains 5 rows.
Row 1's value is 4
Row 2's value is 1
Row 3's value is 2
Row 4's value is 3
Row 5's value is 5

If I were to sort these into ascending order, I'd get 1, 2, 3, 4, 5 in Column A. But I can't sort the rows so I need code to tell me which is the lowest number (1), which is the second lowest number (2), etc.

The purpose of the code will be to put values in Column B according to what's found in Column A. For the the lowest number, 1, the value in the cell next to it in Column B would be "a". For number 2, it would be "b", etc.

Is there a way that code can do this?