+ Reply to Thread
Results 1 to 6 of 6

Creating a Matrix

  1. #1
    Registered User
    Join Date
    04-07-2021
    Location
    Alberta
    MS-Off Ver
    2007
    Posts
    9

    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!!!
    Attached Files Attached Files
    Last edited by BrendanWebb; 04-24-2021 at 01:14 PM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,429

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    04-07-2021
    Location
    Alberta
    MS-Off Ver
    2007
    Posts
    9

    Re: Creating a Matrix

    Hi,

    Thanks for your reply!

    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. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,429

    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. #5
    Registered User
    Join Date
    04-07-2021
    Location
    Alberta
    MS-Off Ver
    2007
    Posts
    9

    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. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,429

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Creating a Matrix (I think?)
    By mshamsiev in forum Excel General
    Replies: 7
    Last Post: 02-22-2019, 12:00 PM
  2. I need Help with creating Pricing Matrix!!! Please Anyone!
    By e.limm in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-14-2017, 08:05 AM
  3. Creating a Matrix
    By blens1 in forum Excel General
    Replies: 4
    Last Post: 12-05-2014, 01:09 AM
  4. I need help creating a 3x3 Matrix in vba?
    By mattm99 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-18-2011, 03:36 PM
  5. Creating a matrix from a database
    By Sandy24 in forum Access Tables & Databases
    Replies: 1
    Last Post: 06-20-2011, 08:27 AM
  6. [SOLVED] Creating a matrix from columns
    By Ernie Sersen in forum Excel General
    Replies: 2
    Last Post: 02-18-2005, 10:06 AM

Bookmarks

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