Below sample data I am trying to chart but I'm not sure if Excel can do this.
It is basically a record of animals (tags) and where they have been spotted.

I am trying to create a chart (maybe a scatter) with Location and Month on
the X & Y axis and the tag data to appear to show a sighting. I have created
a work around using a pivot table but the person looking at this chart is not
an Excel user and I want to keep it simple.

Any suggestions?

Location Month Tag
NW Jan J33
NW June J34
SW May J33
SW June J35
E Oct J36
E Nov J35
W May J33
W Apr J34

Hi,

Insert new blank columns B and D. In B2, enter the following formula and
copy down. This will give each location a unique number.

=IF( COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,VLOOKUP(A2,A$2: B2,2,FALSE))

In D2, enter the folloing and copy down. This will give each month a number.


=MONTH(1&C2)

Plot an x-y chart of columns B and D.
Hide both sets of axis labels.
Create two new dummy series as below, and plot the numbers as two new x-y
series on the chart.

NW 1 0
SW 2 0
E 3 0
W 4 0

Jan 0 1
Feb 0 2
Mar 0 3
Apr 0 4
May 0 5
Jun 0 6
Jul 0 7
Aug 0 8
Sep 0 9
Oct 0 10
Nov 0 11
Dec 0 12

This will give you points along both axes. You can then use something like
Rob Boveys Chart Labeller add-in to label all three sets of points with data
labels, your tags, the locations and the month name.

No doubt Jon or Andy will come up with a much simpler methodb but I can
email you the file if you like.

Dave

url:http://www.ureader.com/msg/10297321.aspx

Thank you Dave,

I will try your solution. I would be grateful if you could email the file
to me. What is the best way to give you my address ?

"Dave Curtis" wrote:

Hi,

Insert new blank columns B and D. In B2, enter the following formula and
copy down. This will give each location a unique number.

=IF( COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,VLOOKUP(A2,A$2: B2,2,FALSE))

In D2, enter the folloing and copy down. This will give each month a number.


=MONTH(1&C2)

Plot an x-y chart of columns B and D.
Hide both sets of axis labels.
Create two new dummy series as below, and plot the numbers as two new x-y
series on the chart.

NW 1 0
SW 2 0
E 3 0
W 4 0

Jan 0 1
Feb 0 2
Mar 0 3
Apr 0 4
May 0 5
Jun 0 6
Jul 0 7
Aug 0 8
Sep 0 9
Oct 0 10
Nov 0 11
Dec 0 12

This will give you points along both axes. You can then use something like
Rob Boveys Chart Labeller add-in to label all three sets of points with data
labels, your tags, the locations and the month name.

No doubt Jon or Andy will come up with a much simpler methodb but I can
email you the file if you like.

Dave

url:http://www.ureader.com/msg/10297321.aspx

Hi,

Please keep your threads together, starting a new thread for every response
makes it very difficult for us to follow.

Cheers,
Shane Devenshire

"MFC" wrote in message
...
Thank you Dave,

I will try your solution. I would be grateful if you could email the file
to me. What is the best way to give you my address ?

"Dave Curtis" wrote:

Hi,

Insert new blank columns B and D. In B2, enter the following formula and
copy down. This will give each location a unique number.

=IF( COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,VLOOKUP(A2,A$2: B2,2,FALSE))

In D2, enter the folloing and copy down. This will give each month a
number.


=MONTH(1&C2)

Plot an x-y chart of columns B and D.
Hide both sets of axis labels.
Create two new dummy series as below, and plot the numbers as two new x-y
series on the chart.

NW 1 0
SW 2 0
E 3 0
W 4 0

Jan 0 1
Feb 0 2
Mar 0 3
Apr 0 4
May 0 5
Jun 0 6
Jul 0 7
Aug 0 8
Sep 0 9
Oct 0 10
Nov 0 11
Dec 0 12

This will give you points along both axes. You can then use something
like
Rob Boveys Chart Labeller add-in to label all three sets of points with
data
labels, your tags, the locations and the month name.

No doubt Jon or Andy will come up with a much simpler methodb but I can
email you the file if you like.

Dave

url:http://www.ureader.com/msg/10297321.aspx