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?action=getinfo&userid=25807
View this thread: http://www.excelforum.com/showthread.php?threadid=478171

0
10/21/2005 9:54:56 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
797 Views

Similar Articles

[PageSpeed] 28

> 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

0
10/21/2005 10:59:47 AM
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..

--
qhors
-----------------------------------------------------------------------
qhorse's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2580
View this thread: http://www.excelforum.com/showthread.php?threadid=47817

0
10/21/2005 12:48:12 PM
nobody else then ? :(


-- 
qhorse
------------------------------------------------------------------------
qhorse's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25807
View this thread: http://www.excelforum.com/showthread.php?threadid=478171

0
10/24/2005 10:47:44 AM
Reply:

Similar Artilces:

Break between tables
I am creating short, wide tables (landscape shape.) Two could fit on one sheet, except that the column widths are to be different. Does anyone know how to do some sort of "format break" between tables in a sheet, so that I can start fresh with standard column widths? Thank you. Keri Your ouot of luck on this. Every cell in a column has the same width and every cell in a row has the same height. You could use cell merging but this usually presents many more headaches then it solves Good Luck Mark Graesse mark_graesser@yahoo.co ----- Keri wrote: ---- I am cre...

Can a form linked to an autoLookup query update another table
I created a form that is updated with an AutoLookup query from another table once a certain linked field is filled in. Can this form be used to add a record to the source table with the updated information? When that record is saved, the record is added to the form's source table - assuming the form is bound to the source table. How does your form save the record? Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "oterosuz" <oterosuz@discussions.microsoft.com> wrote in message news:3608D905-8769-435E-B5E5-7B0F566AFD63@microsoft.com......

Creating Cross-Project Links
Hi 1). What is the usage of Creating Cross-Project Links ? As I understand, "Cross-project links are useful when two or more projects have only a few tasks that affect one another. For example, a milestone within a development project might trigger a task in an advertising campaign project." 2). How it is implemented in Project 2007 ? When I copied the task from the source project into my project ( target project ) using "Paste Special", the new task I created become another task in my target project. My observation is both task ( ie. task in the source...

ASP.NET Dynamic Table
Hi All, Am using VS2010 with Framework 4.0 in VB.NET I have a table which I wish to create on page load which isn't database driven I can create the table single column using new LinkLabel controls but I wish to have the links in 3 or 4 columns, not just one. Example At the moment: LinkLabel1 LinkLabel2 LinkLabel3 LinkLabel4 LinkLabel4 LinkLabel6 .... Would like them: LinkLabel1 LinkLabel2 LinkLabel3 LinkLabel4 LinkLabel5 LinkLabel6 .... This is the code I have so far, taken from an example &a...

Customer Cross Reference
2nd Request - Posted a few Months Ago, No Reply --------------------------------------------------------------------------- Has anyone out there used the Customer Part Number Cross Ref v8.0? I loaded a customer list the other day. When I entered the Customer Number on the Sales Order, it was instantly converted over to our number. Worked Great... I'm wondering if there is a way to automatically reference the original Customer Number on the Packing List and/Or Invoice or is that something that needs to be manually typed in. I tried adding notes, but I don't know if they carry down...

Web table to Excel...
Could someone help me exporting the tables from the following web-page into excel ? http://www.ekdd.gr/ESDD/IH/REBBBD-esdd-epityxontwn.htm (use unicode encoding) is this impossible ? ...

Unit of Measure Detail table
Hi! I am trying to modify the GP sales invoice to add line item weight but for some reason I cannot pull the IV UOM Detail table on the report. This report is linked to the IV UOM HDR table which I was able to pull on the SOP Sales report but if I try to open this, I cannot see the IV UOM Detail table. Any ideas? Thanks -- Marisol Mortera ...

No "Move Mailbox" cross site migration
Hi everybody: We have a forest with subdomains, the main domain is AD 2000 and has 1 Cluster Exchange 2003 SP1, 1 Server Exchange 2003 SP1 with ADC SP1 and SRS, and 3 Exchange 5.5 in the same Exchange 5.5 site. We have 8 subdomains in AD2000 with Exchange 5.5 SP4 server with hotfix applied for cross-site in each subdomain, and they had an own site in Exchange 5.5. We are trying to consolidate and move all mailbox and public folders to the Exchange 2003 cluster, we did the domainprep in each subdomain and create a RUS in the Exchange 2003 cluster to each subdomain. When we tried to move cros...

matching data from different tables with similar columns
HI, I am having some trouble with matching some data and I was wonderin if anybody could help me? I have two tables with the columns "Name" an "Amount", the first table has the column "Names" field but the colum "Amount" empty. I wanted to fill the "Amount" column in the firs table based on the second table, the problem is that the second tabl has many more records than the first. Is there a way to create formula to look up the name on second table and "amount" data on th first table. Thanks for the help. Regards, Marco -- Mes...

Pivot Table Problem
I am working in an excel spreadsheet which another person created a pivot table in. I have to do a lot of cutting and pasting into another report. When I scroll my mouse over any cell in the sheet with data in it a bluebox appears with the information in that in cell. How do I turn that off? It is very annoying as it causes a delay in my trying to copy that cell. Hi, While in the pivot table choose PivotTable Tools, Options, Options, Display and turn off Show contextual tooltips. Cheers, Shane -- If this helps, please click the Yes button. Cheers, Shane Devenshire ...

Excel 2002 only pivot table feature?
Hi All, I have a pivot table that was created in Excel 2002. My co-workers who view the same pivot table in Excel 2000 don't get a "Show All" selection to "select all"/"deselect all" row field data when they click on the row field item down-arrow. I hope someone can help me with this question...Is the "Show All" selection an Excel 2002 only feature or is there a setting/feature in Excel 2000 that can produce the same result as the Excel 2002 "Show All" selection? Keith Excel 2000 doesn't have this feature, but you can show ...

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

comparing tables/vectors
Hi there :-) I have two columns with numeric data [but the kind of data shouldn't matter, I suppose]. They're of _different_ lenght, but there are empty cells below both of them. I want to compare the columns and get the _number_ [amount] of mutual elements as a result. Ie.: if A={2, 3, 5, 7, 9, 23, 25, 46}, and B={1, 3, 4, 6, 7, 8, 9, 46} than Result=4 If I defined the columns to have the same lenght [including some of the empty cells below] would the empty cells be counted as well? I wouldn't like that :-) Thanks in advance, regards. -- Uka P. Assuming ...

cross-reference
I created a cross reference in word to a section of my document that has a heading of 'Heading 3' I used Heading for the reference type and for Insert reference to I used Heading Number (Full context). I was expecting to get something like this 4.8.2 but instead I just got 0. is there some setting somewhere that needs to be tweaked? Make sure that numbering is correctly applied to your headings. If you are using Word 2007, you can choose the "1 Heading 1 1.1 Heading 2 1.1.1 Heading 3" format at Home tab | Multilevel List. If you are using Word 2003, see http://www...

excel 2007 pivot tables
I find the new value filter option (with the ability to input date ranges) very useful. However, it only works for Row or Column Labels and not for the Report Filter. Any suggestions/ideas? Thanks, Gigel Hi Yes the new date range filter is good, but unfortunately it does not extend to the Report Filter area. The only thing I can suggest, is move your Date field to the Row area, right click>Group>Days>select a multiple of days to suit. Then drag the field back to the Report Filter area. -- Regards Roger Govier "gigel" <gigel@chidu.net> wrote in message ...

Pivot Table Subtotal Question
I have a pivot table of prescription drug costs consisting of 4 columns: Month, Customer, Insurance and Base. [ Medicare D program - you're going to see a lot of this :-) ] Actually 2 questions: 1) Auto format gets me "January Total" for example, but no monthly totals or sub-totals. 2) Auto format seems to require something in Data Items. Other than the subtotal issue, column only format is really what I want. Presume these 2 issues are related. Appreciate any suggestions. Thx Dave A pivot table works by summarizing a field in the data area. If Insurance and...

Help find a Table/Field
Can anyone tell me if Great Plains stores account balance information? If so, what table is it in. I've been able find period balance and debits and credits. I'm trying to build a balance sheet and can't find in any tables the account balance. It need to be calculated from RM20101(open)/RM30101(historical) for all the AGNGBUKT where AGNGBUKT =1 is the current Amount Due and the rest of the BUKTS are past due amount then subtract the Payments & Credits from the total. I am not aware of any other ways to get that amount. "Bman" wrote: > Can anyone tell me...

Fixed Assets ACRS tables
Is there a way to update/change ACRS tables? Can new ones be added? Our client here in sydney is reporting to their US headquarters, and they are using depreciation rates table from Gap 20. The rates are not on the ACRS tables of GP. Thanks. ...

Table Import
Hi, can anyone guide me to the user manual for using Table Import. Thanks ...

SmartArt Table Hierarchy
Win XP PowerPoint 2007 Table hierarchies build downwards. eg 'A' on top; subordinates of 'A' below. I want to build upwards. 'A' below its subordinates. If that's possible, how? Thanks. ...

Pivot table subtotal select
After recently upgrading my MS Office I cannot select and highlight a subtotal row within a pivot table as I was able to before. By placing my cursor at the left side edge of a subtotal row (cursor changes to a right-hand pointing arrow) I was able to format all subtotal rows within the pivot table with a different color, bold etc. Can you tell me what option I need to select w/in Excel to regain this ability? Thanks Kevin Allen From the PivotTable toolbar, choose PivotTable>Select, and check that Enable Selection is on. Kevin Allen wrote: > After recently upgrading my MS Off...

Table Cell
I have created a form using a table. I want to lock or block cells that should not be changed. How do you do this? ...

WHY: duplicated entries in suppliers table after using QSimport
hello after using QSimport to populate the items table, i've noticed that there are a lot of duplicate entries being created in the supplier table. There are 254 only distinct supplier names, but there are 654 suppliers total. When you look at the suppliers which are present more than once, you notice (i am 99% sure) that all of the ones that get duplicated are had to be truncated to 30 characters, the max data length for that field. i checked and they are not being duplicated because they are misspelled or even because of trailing spaces in the supplier name 1) would it be harmfu...

Maximum Table Size?
Hi everyone! Happy post-Thanksgiving withdrawals as left-overs diminish! I'm using A02 on XP. I have a DB for internal contract audits and it's just one big table with 2 forms and a few reports. I seem to have hit a wall on adding fields to my table. I have approximately 162 fields and it won't let me add any more. I get the error: Property value is too large. There are no fields that can be logically moved to another table. It's just a questionaire with comment fields and a few dates. I guess I can have Table1 and Table2 and link them but I only need a few mor...

Connecting tables
Is there any way to connect tables in Publisher similiar to the way you can connect text frames for overfill. kpappano wrote: > Is there any way to connect tables in Publisher similiar to the way you can > connect text frames for overfill. Nope. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org Is there any other way I can do it? Any suggestions? "Ed Bennett" wrote: > kpappano wrote: > > Is there any way to connect tables in Publisher similiar to the way you can > > connect text frames for overfill. > > Nope. > > -- > E...