Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Cross table
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 !! -- qhorse ------------------------------------------------------------------------ qhorse's Profile: http://www.excelforum.com/member.php...o&userid=25807 View this thread: http://www.excelforum.com/showthread...hreadid=478171 |
#2
|
|||
|
|||
Cross table
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 |
#3
|
|||
|
|||
Cross table
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 ------------------------------------------------------------------------ qhorse's Profile: http://www.excelforum.com/member.php...o&userid=25807 View this thread: http://www.excelforum.com/showthread...hreadid=478171 |
#4
|
|||
|
|||
Cross table
nobody else then ? :( -- qhorse ------------------------------------------------------------------------ qhorse's Profile: http://www.excelforum.com/member.php...o&userid=25807 View this thread: http://www.excelforum.com/showthread...hreadid=478171 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating pivot table to include additional rows | Excel Discussion (Misc queries) | |||
Change Data In Pivot Table | New Users to Excel | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
table | Excel Discussion (Misc queries) | |||
table | Excel Worksheet Functions |