0

I have a spreadsheet of many-to-many matches between people. My data looks something like this:

+-----+-------+-----+-------+
| id1 | name1 | id2 | name2 |
+-----+-------+-----+-------+
| 123 | John  | 456 | Jane  |
| 123 | John  | 789 | Bob   |
| 456 | Jane  | 999 | Susan |
| 111 | John  | 888 | Roger |
+-----+-------+-----+-------+

I've created a pivot table in order to see the number of matches per person (matches are directed and I only care about id1 -> id2 edges). My problem is, because people can have the same name, I need to use id as the primary key in the pivot table. But if I do that I can't easily see which name the id corresponds to. I tried taking the "minimum" of each name in my pivot table, but then I just get the number 0. I just want to take an arbitrary name from any of the matches. Is this possible?

This is my desired output format:

+-----+-------+-------------+
| id1 | name1 | match_count |
+-----+-------+-------------+
| 123 | John  |           2 |
| 456 | Jane  |           1 |
| 111 | John  |           1 |
+-----+-------+-------------+

2 Answers 2

2

To get that output create pivot, add id1 and name1 as Rows, tick off totals (unnecessary) and add name1 to Values with COUNTA formula Pivot

1
1

Please try the following formula:

=QUERY({B2:C;D2:E}, 
     "select Col1,Col2, count(Col1) 
      where Col1 is not null group by Col1, Col2 label count(Col1) ''")

Query formula

Functions used:

5
  • Thank you for the answer, but my question is specific to pivot tables. I guess it can't be done with them? Mar 6, 2020 at 7:43
  • Sorry I misread your question. Glad you worked it out. I still don't understand though why you reverted my edit on it. Is your data on a single column separated by commas? You can also have a look at this post. Mar 7, 2020 at 3:50
  • I reverted because I only wanted directed edges, not undirected as in your edit. And then I was just too lazy to add the table syntax back, haha. Mar 8, 2020 at 2:34
  • 1
    _"...was just too lazy..."_> Totally understand that :) The , was what threw me off though. If you find some time, you could probably edit using this ASCII Tables site. Keep everything on default. You write what you want as a range in your sheet, copy, paste on the site. Copy, paste on the question here. Done Mar 8, 2020 at 6:31
  • Cool, yeah that works. Mar 9, 2020 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.