Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique occurrences of a value Q
I have a number of locations listed in Column A. These are duplicated a
number of times. These are also variable (from a list of approx 60 possible locations) How can I add the instances of the unique locations listed For example, Column in could contain the following: London; Bristol; London (again); Bath; Manchester; Bristol; Liverpool etc I want to return (based on the above) London =2 Bristol = 1 Bath = 1 etc etc I don't want to use the formula if=London etc etc as I have a list of over 60 locations Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique occurrences of a value Q
I know you posted in the Worksheet Functions group, but a Pivot Table is the
easiest solution: If your list is in A1:A100, with A1: Location...then <Data<Pivot Table Use: Excel Select your data Click the [Layout] button ROW: Drag the Location field here DATA: Drag the Location field here too If it doesn't list as Count of Location...dbl-click it and set it to Count Click [OK] Select where you want the Pivot Table...and you're done! That will list each Location and the count. To refresh the Pivot Table, just right click it and select Refresh Data Post back with any questions. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Sean" wrote: I have a number of locations listed in Column A. These are duplicated a number of times. These are also variable (from a list of approx 60 possible locations) How can I add the instances of the unique locations listed For example, Column in could contain the following: London; Bristol; London (again); Bath; Manchester; Bristol; Liverpool etc I want to return (based on the above) London =2 Bristol = 1 Bath = 1 etc etc I don't want to use the formula if=London etc etc as I have a list of over 60 locations Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique occurrences of a value Q
Thanks Ron
I probably can by using some simple refresh codes on a macro etc then linking where I want the results to appear to the PT Thanks I'll have a crack at it Ron Coderre wrote: I know you posted in the Worksheet Functions group, but a Pivot Table is the easiest solution: If your list is in A1:A100, with A1: Location...then <Data<Pivot Table Use: Excel Select your data Click the [Layout] button ROW: Drag the Location field here DATA: Drag the Location field here too If it doesn't list as Count of Location...dbl-click it and set it to Count Click [OK] Select where you want the Pivot Table...and you're done! That will list each Location and the count. To refresh the Pivot Table, just right click it and select Refresh Data Post back with any questions. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Sean" wrote: I have a number of locations listed in Column A. These are duplicated a number of times. These are also variable (from a list of approx 60 possible locations) How can I add the instances of the unique locations listed For example, Column in could contain the following: London; Bristol; London (again); Bath; Manchester; Bristol; Liverpool etc I want to return (based on the above) London =2 Bristol = 1 Bath = 1 etc etc I don't want to use the formula if=London etc etc as I have a list of over 60 locations Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique occurrences of a value Q
Ron your right its simple!
One question, when I refresh the data, it reformats the column width's to the original size (which I want wider). How can I refresh the data without changing the column widths? Thanks Sean wrote: Thanks Ron I probably can by using some simple refresh codes on a macro etc then linking where I want the results to appear to the PT Thanks I'll have a crack at it Ron Coderre wrote: I know you posted in the Worksheet Functions group, but a Pivot Table is the easiest solution: If your list is in A1:A100, with A1: Location...then <Data<Pivot Table Use: Excel Select your data Click the [Layout] button ROW: Drag the Location field here DATA: Drag the Location field here too If it doesn't list as Count of Location...dbl-click it and set it to Count Click [OK] Select where you want the Pivot Table...and you're done! That will list each Location and the count. To refresh the Pivot Table, just right click it and select Refresh Data Post back with any questions. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Sean" wrote: I have a number of locations listed in Column A. These are duplicated a number of times. These are also variable (from a list of approx 60 possible locations) How can I add the instances of the unique locations listed For example, Column in could contain the following: London; Bristol; London (again); Bath; Manchester; Bristol; Liverpool etc I want to return (based on the above) London =2 Bristol = 1 Bath = 1 etc etc I don't want to use the formula if=London etc etc as I have a list of over 60 locations Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique occurrences of a value Q
Sean
To prevent the unwanted column width adjustments when you refresh the data: Right-click on the Pivot Table Select: Table options UNcheck: AutoFormat Table That should do it. *********** Regards, Ron XL2002, WinXP "Sean" wrote: Ron your right its simple! One question, when I refresh the data, it reformats the column width's to the original size (which I want wider). How can I refresh the data without changing the column widths? Thanks Sean wrote: Thanks Ron I probably can by using some simple refresh codes on a macro etc then linking where I want the results to appear to the PT Thanks I'll have a crack at it Ron Coderre wrote: I know you posted in the Worksheet Functions group, but a Pivot Table is the easiest solution: If your list is in A1:A100, with A1: Location...then <Data<Pivot Table Use: Excel Select your data Click the [Layout] button ROW: Drag the Location field here DATA: Drag the Location field here too If it doesn't list as Count of Location...dbl-click it and set it to Count Click [OK] Select where you want the Pivot Table...and you're done! That will list each Location and the count. To refresh the Pivot Table, just right click it and select Refresh Data Post back with any questions. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Sean" wrote: I have a number of locations listed in Column A. These are duplicated a number of times. These are also variable (from a list of approx 60 possible locations) How can I add the instances of the unique locations listed For example, Column in could contain the following: London; Bristol; London (again); Bath; Manchester; Bristol; Liverpool etc I want to return (based on the above) London =2 Bristol = 1 Bath = 1 etc etc I don't want to use the formula if=London etc etc as I have a list of over 60 locations Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique occurrences of a value Q
Thanks Ron, it works exactly the way it should except for one some
thing, the column header field I had centred, but when I refresh it goes left aligned, any ideas? Thanks again Ron Coderre wrote: Sean To prevent the unwanted column width adjustments when you refresh the data: Right-click on the Pivot Table Select: Table options UNcheck: AutoFormat Table That should do it. *********** Regards, Ron XL2002, WinXP "Sean" wrote: Ron your right its simple! One question, when I refresh the data, it reformats the column width's to the original size (which I want wider). How can I refresh the data without changing the column widths? Thanks Sean wrote: Thanks Ron I probably can by using some simple refresh codes on a macro etc then linking where I want the results to appear to the PT Thanks I'll have a crack at it Ron Coderre wrote: I know you posted in the Worksheet Functions group, but a Pivot Table is the easiest solution: If your list is in A1:A100, with A1: Location...then <Data<Pivot Table Use: Excel Select your data Click the [Layout] button ROW: Drag the Location field here DATA: Drag the Location field here too If it doesn't list as Count of Location...dbl-click it and set it to Count Click [OK] Select where you want the Pivot Table...and you're done! That will list each Location and the count. To refresh the Pivot Table, just right click it and select Refresh Data Post back with any questions. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Sean" wrote: I have a number of locations listed in Column A. These are duplicated a number of times. These are also variable (from a list of approx 60 possible locations) How can I add the instances of the unique locations listed For example, Column in could contain the following: London; Bristol; London (again); Bath; Manchester; Bristol; Liverpool etc I want to return (based on the above) London =2 Bristol = 1 Bath = 1 etc etc I don't want to use the formula if=London etc etc as I have a list of over 60 locations Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique records based on the Criteria in another colum | Excel Worksheet Functions | |||
Setting up a validation of data listbox to provide the unique items within a range | Excel Worksheet Functions | |||
how do i find unique avg buy prices in multiple group of buys/sell | Excel Worksheet Functions | |||
code not unique find latest date | Excel Discussion (Misc queries) | |||
Count unique occurrences of name | Excel Discussion (Misc queries) |