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

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

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

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

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

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

You're right...the column heading over the counts ALWAYS reverts back to left
justified and there doesn't seem to be any way around it.

I never noticed that....um...feature? before.

(sorry I couldn't be more help on that one)
***********
Regards,
Ron

XL2002, WinXP


"Sean" wrote:

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

Thanks Ron, maybe we can get a rebate from Microsoft!

Ron Coderre wrote:

You're right...the column heading over the counts ALWAYS reverts back to left
justified and there doesn't seem to be any way around it.

I never noticed that....um...feature? before.

(sorry I couldn't be more help on that one)
***********
Regards,
Ron

XL2002, WinXP


"Sean" wrote:

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