Populating a Matrix using a dynamic list

[dcossyl]

Hi, I am trying to fill a matrix with data about the frequency that a pair of names occurs together in a list that I have. (see the attached image for an example)

The matrix is cataloguing the number of interactions between two people, and the list of name pairs are observations about who initiated each interaction (e.g. Carl initiated an interaction with Jennifer 3 times). I want the matrix to be populated automatically as new observation data gets added to the list. I am able to get the frequency of the name pairs by using a CountIfs function, but I'm not sure how to automate the counts to go to the appropriate cell in the matrix.

The matrix row and column headers correspond to the names in the first and second lists, respectively. Using the lists in Column A and Column B, I want to end up with the final product of a matrix that looks like the one I've created at the bottom of the image I've attached.

Any help is appreciated. I'm happy to clarify my problem if needed.

[jjhayes]

See the attached file. Formula starts in H5, using Index function and some conditional formatting.

[dcossyl]

thank you! this helps me tremendously.

This may be a very novice question, but how do I modify the formula in the matrix if the List of Names and Counts exists in Sheet1 and the matrix exists in Sheet 2?

Thank you again.

[jjhayes]

See the attached file. It shows the formula referencing the counts, which I have moved to Sheet 2. Is that what you were asking?

[dcossyl]

Hi- yes, that does answer my question.

However, I'm having a few more problems: I'm working with a larger matrix than the sample I've shared with you so far. When I try to modify the ranges/references in the sample matrix we've been using and copy it into the real, larger workbook, I'm not able to expand the formula to the rest of the cells in the matrix properly.

I'm attaching one of the actual matrices I'm working with. I've used the formula you've given me in Sheet2,C3. When I try to expand it to other cells, it is changing the reference cells. What am I doing wrong?

I'm working with the highlighted yellow columns in Sheet1 for the Index part of the formula.

Thank you again for your help.

[jjhayes]

I have updated the formula in the attached file to reference the correct ranges. It is first looking at Column B then Row 2 from Sheet 2, and trying to find the matrix of those two items in Column F ("Unique Interactions") on Sheet 1.

However, the unique interactions on Sheet 1 don't seem to match those on Sheet 2. For example, on sheet 1, the first Unique Interaction (4/1/2015) is Gk13 Cf11, 2 four character strings separated by one space.

On Sheet 2, I see a "RGk13" and a "RCf11", which are 2 5 character strings. Is this the intersection that is supposed to match to Sheet 1? If so, they aren't the same and won't match unless we modify the formula.

I modified the formula to ignore the leading "R", and the matrix populated. You may need to look back at your source data to make sure that Column F is following the Column B - Row 2 order if you are not seeing counts you expect to see in the matrix.

It appears there may be about 15 pairs in column F on Sheet 1 that are in reverse order.

I can check on this later if you have more questions.Interaction Matrix_rev1.xlsx

[dcossyl]

Yes, you are right; the leading "R" in the matrix headers should be ignored. Thank you for catching that and for modifying the formula.

One last question, I'm trying to make it easy for me to flag the cells in column F that do not have a match in the corresponding matrix. I've gotten about half way there (ex: Sheet1, cells H2 and I2), but I'm not sure how to account for the mismatch with the leading "R" in the matrix/I think it makes the formula unnecessarily complex.

Is there a more efficient way for me to see flag the cells in the source data whose counts are making it into the matrix?

[jjhayes]

I brute forced it. I gave each entry on Sheet 1 a sequential number (example: GK13 and Cf11 = 1, fu15 ae12 = 2, etc.) and put this in a new column I. Then I did an index-match on sheet 2 to assign the number from the new Column I on sheet 1 to the matrix. This shows all the line items that could appear on the matrix. I then took all those numbers, sorted them and found the gaps. It looks like the following line items don't have a corresponding entry in the matrix on Sheet 2:

hh14 vn11
ej14 tw14
fm6 ed1
qq12 tb15
qq12 zh14
tw14 zh14
tg13 vh14
rj14 rs11
zh14 td14
rv11 qo11
rj13 dq13
fm6 qo10

There is probably a more elegant way, but couldn't think of one.

You should check the list above to make sure my logic was sound.

Thanks!

[dcossyl]

Thank you SO much for all of your help! I really appreciate it.

[jjhayes]

No problem. Glad I could help. If this thread is solved, please mark it so. You can also use the "add reputation" star if you wanted to.