Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Other" category in Excel
I work for a translation company and work a lot in pivot tables to display
our top spend by target language (German, French, etc.). I would like to use a pivot table that displays my top 10 languages with an "OTHER LANGUAGES" category with the spend for all the remaining languages. If I filter the top 10 languages in the pivot table, the others disappear completely from the report. So I have one unfiltered pivot table, and another table that references the 1st with an "OTHER LANGUAGES" category that I created manually. But is there any way to create an "OTHER" category automatically within the pivot table? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Other" category in Excel
not within the pivot table, but in your data set use a helper column
=if(top-ten-crteria, language-cell,"Other") use this in your pivot table "whynot0611" wrote: I work for a translation company and work a lot in pivot tables to display our top spend by target language (German, French, etc.). I would like to use a pivot table that displays my top 10 languages with an "OTHER LANGUAGES" category with the spend for all the remaining languages. If I filter the top 10 languages in the pivot table, the others disappear completely from the report. So I have one unfiltered pivot table, and another table that references the 1st with an "OTHER LANGUAGES" category that I created manually. But is there any way to create an "OTHER" category automatically within the pivot table? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Other" category in Excel
Thanks a lot!
What formula would you use to define the top 10 criteria within the base data set? Let's say I only have 2 columns. Column A contains target languages, and column be an amount in USD. Target Language Spend French $1,000 Italian $,1500 Italian $10,000 Italian $5,000 French $1,000 Thanks again! "bj" wrote: not within the pivot table, but in your data set use a helper column =if(top-ten-crteria, language-cell,"Other") use this in your pivot table "whynot0611" wrote: I work for a translation company and work a lot in pivot tables to display our top spend by target language (German, French, etc.). I would like to use a pivot table that displays my top 10 languages with an "OTHER LANGUAGES" category with the spend for all the remaining languages. If I filter the top 10 languages in the pivot table, the others disappear completely from the report. So I have one unfiltered pivot table, and another table that references the 1st with an "OTHER LANGUAGES" category that I created manually. But is there any way to create an "OTHER" category automatically within the pivot table? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Other" category in Excel
if you want the number of times the language was usewd, you could add a
helper column with =countif(A:A,A1) and look for the languages with the largest numbers if you wanted the most dollars in the helper column =sumif(A:A,A1,B:B) I would then use data-filter-advanced filter-unique to get a new table with just line for each language of you could use the filter to get the unique list before the countif or sumif equations. the criteria for top ten must be established by you. "whynot0611" wrote: Thanks a lot! What formula would you use to define the top 10 criteria within the base data set? Let's say I only have 2 columns. Column A contains target languages, and column be an amount in USD. Target Language Spend French $1,000 Italian $,1500 Italian $10,000 Italian $5,000 French $1,000 Thanks again! "bj" wrote: not within the pivot table, but in your data set use a helper column =if(top-ten-crteria, language-cell,"Other") use this in your pivot table "whynot0611" wrote: I work for a translation company and work a lot in pivot tables to display our top spend by target language (German, French, etc.). I would like to use a pivot table that displays my top 10 languages with an "OTHER LANGUAGES" category with the spend for all the remaining languages. If I filter the top 10 languages in the pivot table, the others disappear completely from the report. So I have one unfiltered pivot table, and another table that references the 1st with an "OTHER LANGUAGES" category that I created manually. But is there any way to create an "OTHER" category automatically within the pivot table? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Other" category in Excel
Thanks again! I used =sumif(A:A,A1,B:B). It calculates the total spend for
each language. So I have something like this (I have one line per purchase order): French $7,442.73 French $7,442.73 French $7,442.73 French $7,442.73 French $7,442.73 French $7,442.73 French $7,442.73 Afrikaans $1,785.40 Afrikaans $1,785.40 I can't use the LARGE (array,{1,2,3,4,5,6,7,8,9,10]) function as it shows the same largest amount 10 times. How would you display the top 10 amounts but with each total above counted only once? Thanks a mil... "bj" wrote: if you want the number of times the language was usewd, you could add a helper column with =countif(A:A,A1) and look for the languages with the largest numbers if you wanted the most dollars in the helper column =sumif(A:A,A1,B:B) I would then use data-filter-advanced filter-unique to get a new table with just line for each language of you could use the filter to get the unique list before the countif or sumif equations. the criteria for top ten must be established by you. "whynot0611" wrote: Thanks a lot! What formula would you use to define the top 10 criteria within the base data set? Let's say I only have 2 columns. Column A contains target languages, and column be an amount in USD. Target Language Spend French $1,000 Italian $,1500 Italian $10,000 Italian $5,000 French $1,000 Thanks again! "bj" wrote: not within the pivot table, but in your data set use a helper column =if(top-ten-crteria, language-cell,"Other") use this in your pivot table "whynot0611" wrote: I work for a translation company and work a lot in pivot tables to display our top spend by target language (German, French, etc.). I would like to use a pivot table that displays my top 10 languages with an "OTHER LANGUAGES" category with the spend for all the remaining languages. If I filter the top 10 languages in the pivot table, the others disappear completely from the report. So I have one unfiltered pivot table, and another table that references the 1st with an "OTHER LANGUAGES" category that I created manually. But is there any way to create an "OTHER" category automatically within the pivot table? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Getting a category x-axis to sort of "right-justify" | Charts and Charting in Excel | |||
In a bar chart, how do I change the x-axis "category" labels? | Excel Discussion (Misc queries) | |||
UDF Add-in New category in "Paste Function Dialog Box" | Excel Worksheet Functions |