Trying to use an IF statement to move a large amount of data into a matrix

Hey guys,

I previously received some help from a few guys here on the forum, I'd just like to start out by thanking those individuals once again, it really helped me move forward from an otherwise pretty bleak predicament :S

The same function I used there is what I am trying to manipulate for a new purpose, so hopefully this will be quick fix for someone. The attached workbook "bugs" highlights the function in perfect use. It compares row and column titles and matches up the relationships. If 2 bugs are in the same location at the same time, their relationship in the matrix increases by one, if you take a peak at the 2 worksheets it will become quite clear.

My new issue that I have been struggling with is really quite similar -- almost annoyingly so. What I am trying to do is take beliefs of individuals (attached beliefs excel file) and essentially do the same thing as I did above (take some data and move it into matrix form). So if an individual answered Afterlife_Y AND Hell_N, well the Afterlife_Y:Hell_N matrix value will increase by 1 (Note: An individual only has 1 response per belief question, for example they are asked if they believe in hell, they have Yes, No or Undecided as possible answers, thus _Y, _N and _U -- an individual can only have 1 answer for each belief question). The primary issue I am having with the current formula is that previously it had to match locations, a bug in 4 and another bug in 4 increased that relationship, whereas in this case I need it to increase only if the values are "1" (which indicates their response) for each question -- currently the formula would be also matching 0:0 responses, whereas we need only 1:1 responses. Take a peak at the document, I think it will become quite clear

There are some blanks, these will not be considered for the time being, the current formula just skips them. It should be noted as well that there is 70,000 responses in my copy -- I trimmed it down to ~2500 so it could upload here.

Thanks again guys! I hope I've been clear, let me know if there is anything still foggy.

Although this seems the same as your last post, it is quite different, the last post you already had the basic matrix data set up; here, you want to compare columns of data to each other ( at least from what I can understand of the sample), but now it looks like you need a separate table for each row (ie- each respondent), so the formulas (while they will look close ) are definitely going to be different !

Actually...it looks even more complicated...any question related to same has got to return no answer, but need column matches for others...hrrrm....going to need a day or two to figure the logic out myself (still got RL to deal with ), but if someone gets it beforehand, thats cool !

To clarify, the data as it stands right now ONLY has 1 response for each question, it isn't necessary to have a "check" for no responses (meaning 0) as the data is set up correctly (meaning only 1 response per respondent per question). Meaning, for the question do you believe in hell? Out of the 70,000 responses not one person has a response in more than one group (by group I mean the 3 possible answers, Hell_Y, Hell_N and Hell_U).

Because of this I don't believe we need a function as complicated as you suggest, because we can simply have the function search each row for a relationship as we did with the previous function, it just needs to ignore the 0:0 responses and return 1:1 responses. It wont return anything incorrectly if this is accomplished because I know there is no response from an individual with a "1" in more than one question (meaning each question has a response with 1, 0 and 0, in any order, but always one 1 and two 0's).

What I was thinking the function would do, is simply perform the same operation as we did in the previous workbook, but this time search for relationships with "1" at the same time, instead of searching for a "matching location" at the same time. You can see in the affiliation tab what I am trying to do with the matrix. For example, its not possible to have any God_X:God_Y relationships because an individual can only provide 1 answer to the do you believe in God question, but if we use the same formula on this data, it results in a large number in those relationships because it is returning a value for the instance where for example they picked God_Y as their response, therefore God_N and God_U were both 0 -- the previous function "matches" these 2 values and ends up increasing the God_N:God_U relationship in the matrix because they were both the "same" but we do not want an increase if they are both 0, only if they are both 1.

I was thinking it would be simple because in my head I thought that we could just eliminate the "match" (where it increased the relationship by 1 as long as the 2 responses were the same, whether those responses be 0, 1, 2, 3, 4, 5 or 6, it didn't matter as long as they were the same) and change it to needing to be "1" (where both responses need to be 1, as 1 indicates a response, 0 indicates no response). I know I am having a lot of trouble with it, so perhaps I am not fully grasping what is required to do this, I'm just wanting to make sure you aren't making it more difficult due to a lack of my explanation. Please let me know if anything remains unclear, and of course thanks again for all of your help

As a side note, the data as far as I can tell is in the same form as it was in the previous bug workbook, in this case its just questions instead of bugs and we want to match when they are both "1" not when they both "match."

Try this in E2:

Formula:

Please Login or Register  to view this content.

Drag down and across

or this modification to Pete's:

Formula:

Please Login or Register  to view this content.

the Blue highlighted part checks to see if the intersection is part of the same group, and outputs "" if it is (changed the red part in Pete's to get the right column start)

Attached has both versions

Hope this helps

SUMPRODUCT works by multiplying individual cells of two arrays and then adding them up. As you have only 1's and 0's in your arrays, then two corresponding cells in the two arrays will only contribute to the total if they are both 1, as 0*0, 0*1 and 1*0 will all produce zero. The first term in the SP formula will take the array from column H, but this will change as the formula is copied across as there is no $ sign before the H, so in subsequent columns it will look at column I, then column J, and so on.

The second term will look at the column with a heading the same as in column A, so on row 2 it will use God_Y, then on the next row God_N and so on. The leading diagonal will always produce a total as the cells in God_Y|God_Y will all be the same - you should just blank these out as you had in your example. Note also that the matrix is symmetrical, so the value in row 4 col 2 is the same as row2 col 4, for example, so you only need either the top or the bottom triangle.

Hope this helps.

Pete

@ Pete, you are right It's B2 for both of them