Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data in Excel 2000
Hi,
I'm using excel 2000 and was wondering how I can sort the data in a pivot table? On the ROW side I have many different (and some identical) strings of words. In the DATA field I have put both the word strings to get the count of them as well as the value of each string of words. I would like to sort the strings of words after; firstly the total count of word strings and secondly after the total value of the word string. Bare in mind that it is Excel 2000 that I'm using so it doesn't work to use Data~sort to get it to work.... What am I doing wrong? Thank you so much for the help with this! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data in Excel 2000
Your message is a little less than clear
Are you actually using a Pivot Table? Why do you think Data | Sort does not work in XL2000? What do your mean by "total value of the word string" ? Maybe an example of 5-10 rows of data would help best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Maria" wrote in message ... Hi, I'm using excel 2000 and was wondering how I can sort the data in a pivot table? On the ROW side I have many different (and some identical) strings of words. In the DATA field I have put both the word strings to get the count of them as well as the value of each string of words. I would like to sort the strings of words after; firstly the total count of word strings and secondly after the total value of the word string. Bare in mind that it is Excel 2000 that I'm using so it doesn't work to use Data~sort to get it to work.... What am I doing wrong? Thank you so much for the help with this! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data in Excel 2000
Yes, sorry I am not explaining this very well...
I have something like this: Search String Value Hello $114.95 help $17.90 thank you $27.90 thank you $87.90 thank you $87.90 I have made this into a pivot tabel by putting the search strings in ROW and search string as well as value in DATA. This lookes somehing like: Search String Data Total Hello Count of Search String 1 Sum of Order Value 114.95 Help Count of Search String 1 Sum of Order Value 17.90 Thank you Count of Search String 3 Sum of Order Value 203.70 I want to sort this pivot table after the search string that is the most common and therefore has the highest count of search string. And if there are search strings that are equally as common then they should sort after value. If I use Data~sort it will not sort after two different things. Thanks! "Bernard Liengme" wrote: Your message is a little less than clear Are you actually using a Pivot Table? Why do you think Data | Sort does not work in XL2000? What do your mean by "total value of the word string" ? Maybe an example of 5-10 rows of data would help best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Maria" wrote in message ... Hi, I'm using excel 2000 and was wondering how I can sort the data in a pivot table? On the ROW side I have many different (and some identical) strings of words. In the DATA field I have put both the word strings to get the count of them as well as the value of each string of words. I would like to sort the strings of words after; firstly the total count of word strings and secondly after the total value of the word string. Bare in mind that it is Excel 2000 that I'm using so it doesn't work to use Data~sort to get it to work.... What am I doing wrong? Thank you so much for the help with this! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data in Excel 2000
I see the problem.
We need to get he count and the sum values side by side for each phase and I cannot see a pivot table way to do this This is what I would do for a quick (non pivot table) solution Let's assume there are many sting phases not just 3 Use Data | Filter | Unique to display a list of unique phases Copy and paste these in a free part of the worksheet (or on another sheet) Go back and Data | Filter | Show All In the new area use COUNTIF and SUMIF to get this data Let's say you now have Search String Count Total Hello 1 114.95 help 1 17.90 thank you 3 203.70 Now sort this table using Count as the first key and Total as the second best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Maria" wrote in message ... Yes, sorry I am not explaining this very well... I have something like this: Search String Value Hello $114.95 help $17.90 thank you $27.90 thank you $87.90 thank you $87.90 I have made this into a pivot tabel by putting the search strings in ROW and search string as well as value in DATA. This lookes somehing like: Search String Data Total Hello Count of Search String 1 Sum of Order Value 114.95 Help Count of Search String 1 Sum of Order Value 17.90 Thank you Count of Search String 3 Sum of Order Value 203.70 I want to sort this pivot table after the search string that is the most common and therefore has the highest count of search string. And if there are search strings that are equally as common then they should sort after value. If I use Data~sort it will not sort after two different things. Thanks! "Bernard Liengme" wrote: Your message is a little less than clear Are you actually using a Pivot Table? Why do you think Data | Sort does not work in XL2000? What do your mean by "total value of the word string" ? Maybe an example of 5-10 rows of data would help best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Maria" wrote in message ... Hi, I'm using excel 2000 and was wondering how I can sort the data in a pivot table? On the ROW side I have many different (and some identical) strings of words. In the DATA field I have put both the word strings to get the count of them as well as the value of each string of words. I would like to sort the strings of words after; firstly the total count of word strings and secondly after the total value of the word string. Bare in mind that it is Excel 2000 that I'm using so it doesn't work to use Data~sort to get it to work.... What am I doing wrong? Thank you so much for the help with this! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data in Excel 2000
Forgot a step: with Paste Special, convert formulas to values before sorting
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bernard Liengme" wrote in message ... I see the problem. We need to get he count and the sum values side by side for each phase and I cannot see a pivot table way to do this This is what I would do for a quick (non pivot table) solution Let's assume there are many sting phases not just 3 Use Data | Filter | Unique to display a list of unique phases Copy and paste these in a free part of the worksheet (or on another sheet) Go back and Data | Filter | Show All In the new area use COUNTIF and SUMIF to get this data Let's say you now have Search String Count Total Hello 1 114.95 help 1 17.90 thank you 3 203.70 Now sort this table using Count as the first key and Total as the second best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Maria" wrote in message ... Yes, sorry I am not explaining this very well... I have something like this: Search String Value Hello $114.95 help $17.90 thank you $27.90 thank you $87.90 thank you $87.90 I have made this into a pivot tabel by putting the search strings in ROW and search string as well as value in DATA. This lookes somehing like: Search String Data Total Hello Count of Search String 1 Sum of Order Value 114.95 Help Count of Search String 1 Sum of Order Value 17.90 Thank you Count of Search String 3 Sum of Order Value 203.70 I want to sort this pivot table after the search string that is the most common and therefore has the highest count of search string. And if there are search strings that are equally as common then they should sort after value. If I use Data~sort it will not sort after two different things. Thanks! "Bernard Liengme" wrote: Your message is a little less than clear Are you actually using a Pivot Table? Why do you think Data | Sort does not work in XL2000? What do your mean by "total value of the word string" ? Maybe an example of 5-10 rows of data would help best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Maria" wrote in message ... Hi, I'm using excel 2000 and was wondering how I can sort the data in a pivot table? On the ROW side I have many different (and some identical) strings of words. In the DATA field I have put both the word strings to get the count of them as well as the value of each string of words. I would like to sort the strings of words after; firstly the total count of word strings and secondly after the total value of the word string. Bare in mind that it is Excel 2000 that I'm using so it doesn't work to use Data~sort to get it to work.... What am I doing wrong? Thank you so much for the help with this! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data in Excel 2000
We need to get the count and the sum values side by side for each phase
and I cannot see a pivot table way to do this There is. In the pivot, you just need to drag "Data" and drop it over "Total", and it'll transform exactly to that side-by-side columns format. However, I think the pivot's sort feature (in xl2003 at least) doesn't allow the sort-by, then-by, then by stuff which the OP wants to do, within the pivot itself. That kind of sorting has to be done on a frozen copy of the pivot. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Sort A-Z not working but did in 2000 | Excel Discussion (Misc queries) | |||
Help with sort in Excel 2000 | Excel Discussion (Misc queries) | |||
Using Excel 2000 as Data source for Word 2000 document | Excel Discussion (Misc queries) | |||
Formula Integrity Not Preserved During Sort in Excel 2000 | Excel Discussion (Misc queries) | |||
How do I sort by date (not days, weeks, months) in Excel 2000? | Excel Discussion (Misc queries) |