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 !!

 

[stuart_bisset]

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'
know it this translate really well from french to english...)

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

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

 

[qhorse]

nobody else then ?