Cross table

[qhorse]

Hi everybody !!

i come again here with a problem i can seem to solve :s
i hope someone will be able to give me a hand on this...

here it is :

i have a sheet with 2 rows: location and activity.
several activities can be found in one location, even several of the same activities.

schematic exemple
location activity
1 A
1 B
2 A
3 A
3 B
3 C
4 B
4 C


I would like to create something that give me as a result a sheet where we can see the spatial relations between the activities.
so i would have all the activities in the fields and as much record as i have activities too.
and in the record i would count how many time the two activities are in a same place
the result would be something like :

X A B C
A 0 2 1
B 2 0 2
C 1 2 0

to give you idea, i've got over 3000 location and 250 activity...

does anyone have a nice little idea ? :d

tx a lot !!

[[email protected]]

> i have a sheet with 2 rows: location and activity.

What you describe is 2 columns!

> schematic exemple
> location activity
> 1 A
> 1 B
> 2 A
> 3 A
> 3 B
> 3 C
> 4 B
> 4 C
>
>
> X A B C
> A 0 2 1
> B 2 0 2
> C 1 2 0
>

>From the example of data and output above i'm not sure this makes
sense. Why would you have ABC across the top AND down the left side?
>From the data above would your output not be as follows:

X A B C
1 1 1 0
2 1 0 0
3 1 1 1
4 0 1 1

If this is what you want then you should look into doing a pivot table.
and place "COUNT of Activity" in the 'Data' section, place "Activity"
in the 'Column' Section and place "Location" in the 'Row' section.

HTH

[qhorse]

hi !

sorry i type the wrong word up there, it is of course a column.

what i'm trying to obtain is what we call a "square matrix" (i don't know it this translate really well from french to english...)

what is showed is correct, i need to have the activities vertically and horizontally as well. with this kind of matrix you only need to read half of it of course because the information is repeated twice in it (on both side of the diagonale).

i know it is easy to create a pivot table for what you showed me but in my case it harde since i cannot put the activity in the Rows AND the columns...

[qhorse]

nobody else then ?