# Creating a Matrix

1. ## Creating a Matrix

Hi,

I have created a list of trends in excel that now needs to be cross reference with each other to see how many times the trend occurs with another trend. I have assigned each trend a column and a number as seen in my excel file. I need to find out how many times that P1 and P2 occur in the same row as one another, and then I need to do this for all of the other relationships. I was suggested by a family member to use a pivot table, but I am not familiar with them so I am hoping that someone could verify with me if this is possible. If it is, could you please point me in the right direction on how to accomplish this? Thank you in advance for saving me from doing this manually!!!

2. ## Re: Creating a Matrix

Hello BrendanWebb and Welcome to Excel Forum.
I feel that we need some further information.
In cell E4 the value is 3.
I assume that is the occurrence of P3 in relation to some other URBAN PLANNING, ENVIRONMENTAL, SOCIAL, ECONOMIC or TECHNOLOGICAL factor but since I don't see a list of those factors in a column I am not sure to what the 3 is cross referencing.

3. ## Re: Creating a Matrix

Hi,

3 is the number that represents a single occurrence of the trend P3 just as 4 is the number that represents the single occurrence of the trend P4 etc... Previously, I had the cells where numbers are currently as a color,
however, an acquaintance told me to switch them to numbers - but he was unable to give me any advice on to where to go from there.

In essence, the number 3 to which you are referring to, is referring to a single instance of that trend.

I am trying to cross reference each trend with one another one at a time - for example P1 (1) and P2 (2) occur on the same row 5 times. P1 (1) & P3 (3) occur on the same row 8 times. P1 (1) & T7 (64) occurs 0 times.

Let me know if this makes sense.

Cheers

4. ## Re: Creating a Matrix

Not sure how much help this will be.
Put index (data row) numbers and a column header in column B on Sheet1.
Used Power Query to produce the table in columns A:C on the Table1 sheet. Power Query is not an option for the 2007 version of Excel, however if the data shown on Sheet1 represents all of the data that will be collected then the table (Table1_2) is all you need.
Produced a pivot table from Table1_2 putting Row in the Rows area and Attribute (trend) in the Columns and Values areas.
The Attribute filter can then be used to determine the trends to be investigated.
In the attached file the pivot table is showing rows that contain trends P1, P2 and P3.
The Row filter can be set to filter values greater than n-1 of the trends being investigated so that you will only see a listing of rows in which all trends are present.
In the attached file the Row filter is set to show values greater than 2 so that only rows containing all three trends are displayed.
Let us know if you have any questions.

5. ## Re: Creating a Matrix

Wow!! Thank you so much!!! - It took me a bit to figure out how to use it properly as I am an Excel rookie but I got it now. Very powerful!! Thanks for posting the process on how to do this as well so I can figure it out for any list of data!!

Very grateful

Cheers

6. ## Re: Creating a Matrix

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1