|
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 07:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com