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

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

| improve this answer | |
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:

| improve this answer | |
  • Thank you for the answer, but my question is specific to pivot tables. I guess it can't be done with them? – Jesse Aldridge Mar 6 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. – marikamitsos Mar 7 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. – Jesse Aldridge Mar 8 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 – marikamitsos Mar 8 at 6:31
  • Cool, yeah that works. – Jesse Aldridge Mar 9 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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