| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SORT data containing formula
How to sort data containing formula For example
Column A Column B Column C 1 10 =B1-B5=4 2 9 =B2-B5=3 3 8 =B3-B5=2 4 7 =B4-B5=1 5 6 =B5-B5=0 Now if I sort in descending order of Column A, the Data I expect is Column A Column B Column C 5 6 =B1-B1=0 4 7 =B2-B1=1 3 8 =B3-B1=2 2 9 =B4-B1=3 1 10 =B5-B1=4 However the results of sorting are Column A Column B Column C 5 6 =B1-B1=0 4 7 =B2-B3=-1 3 8 =B3-B5=-2 2 9 =B4-B7=9 1 10 =B5-B9=10 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SORT data containing formula
You have to convert the formula data to values to do the sorting. Copy the
column with the formula into a new column and PasteSpecial using Values. Then sort on new column. "Abrar" wrote: How to sort data containing formula For example Column A Column B Column C 1 10 =B1-B5=4 2 9 =B2-B5=3 3 8 =B3-B5=2 4 7 =B4-B5=1 5 6 =B5-B5=0 Now if I sort in descending order of Column A, the Data I expect is Column A Column B Column C 5 6 =B1-B1=0 4 7 =B2-B1=1 3 8 =B3-B1=2 2 9 =B4-B1=3 1 10 =B5-B1=4 However the results of sorting are Column A Column B Column C 5 6 =B1-B1=0 4 7 =B2-B3=-1 3 8 =B3-B5=-2 2 9 =B4-B7=9 1 10 =B5-B9=10 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SORT data containing formula
Hi,
If you can modify the formula to read =B1-6 you can copy that down and sort on it. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Abrar" wrote: How to sort data containing formula For example Column A Column B Column C 1 10 =B1-B5=4 2 9 =B2-B5=3 3 8 =B3-B5=2 4 7 =B4-B5=1 5 6 =B5-B5=0 Now if I sort in descending order of Column A, the Data I expect is Column A Column B Column C 5 6 =B1-B1=0 4 7 =B2-B1=1 3 8 =B3-B1=2 2 9 =B4-B1=3 1 10 =B5-B1=4 However the results of sorting are Column A Column B Column C 5 6 =B1-B1=0 4 7 =B2-B3=-1 3 8 =B3-B5=-2 2 9 =B4-B7=9 1 10 =B5-B9=10 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SORT data containing formula
Hi,
You may download and install the following add in (http://www.download.com/Morefunc/300...-10423159.html) and then use the VSORT() array function -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Abrar" wrote in message ... How to sort data containing formula For example Column A Column B Column C 1 10 =B1-B5=4 2 9 =B2-B5=3 3 8 =B3-B5=2 4 7 =B4-B5=1 5 6 =B5-B5=0 Now if I sort in descending order of Column A, the Data I expect is Column A Column B Column C 5 6 =B1-B1=0 4 7 =B2-B1=1 3 8 =B3-B1=2 2 9 =B4-B1=3 1 10 =B5-B1=4 However the results of sorting are Column A Column B Column C 5 6 =B1-B1=0 4 7 =B2-B3=-1 3 8 =B3-B5=-2 2 9 =B4-B7=9 1 10 =B5-B9=10 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SORT data containing formula
I have downloaded the add in----How to use the Vsort function
"Ashish Mathur" wrote: Hi, You may download and install the following add in (http://www.download.com/Morefunc/300...-10423159.html) and then use the VSORT() array function -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Abrar" wrote in message ... How to sort data containing formula For example Column A Column B Column C 1 10 =B1-B5=4 2 9 =B2-B5=3 3 8 =B3-B5=2 4 7 =B4-B5=1 5 6 =B5-B5=0 Now if I sort in descending order of Column A, the Data I expect is Column A Column B Column C 5 6 =B1-B1=0 4 7 =B2-B1=1 3 8 =B3-B1=2 2 9 =B4-B1=3 1 10 =B5-B1=4 However the results of sorting are Column A Column B Column C 5 6 =B1-B1=0 4 7 =B2-B3=-1 3 8 =B3-B5=-2 2 9 =B4-B7=9 1 10 =B5-B9=10 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SORT data containing formula
Once you download and install the addin, the help file would also get
downloaded. IN Excel 2003, navigate to Tools More func Help. There read up on the VORT() function -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Abrar" wrote in message ... I have downloaded the add in----How to use the Vsort function "Ashish Mathur" wrote: Hi, You may download and install the following add in (http://www.download.com/Morefunc/300...-10423159.html) and then use the VSORT() array function -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Abrar" wrote in message ... How to sort data containing formula For example Column A Column B Column C 1 10 =B1-B5=4 2 9 =B2-B5=3 3 8 =B3-B5=2 4 7 =B4-B5=1 5 6 =B5-B5=0 Now if I sort in descending order of Column A, the Data I expect is Column A Column B Column C 5 6 =B1-B1=0 4 7 =B2-B1=1 3 8 =B3-B1=2 2 9 =B4-B1=3 1 10 =B5-B1=4 However the results of sorting are Column A Column B Column C 5 6 =B1-B1=0 4 7 =B2-B3=-1 3 8 =B3-B5=-2 2 9 =B4-B7=9 1 10 =B5-B9=10 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel changes formula after data sort/filter, please help. | Excel Discussion (Misc queries) | |||
| How can I sort data without affecting formula? | Excel Discussion (Misc queries) | |||
| I can't sort data when the key column cells contain formula | Excel Discussion (Misc queries) | |||
| What formula will sort data in a separate worksheet%3f | Excel Worksheet Functions | |||
| How do I make a formula refer to given data even if I do a sort f. | Excel Discussion (Misc queries) | |||
Hybrid Mode
