Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to make an Excel Workbook that contains the statistics of an XBox Tournament that our office is doing. Winner of the whole Tournament wins the XBox! It's fairly simple rules. Everyone will play everyone in a game of horseshoes. The winner of the Horseshoes round will earn 10 points.

There will be two other games where the employees can earn points. However, I would like to focus on just the horseshoes for now.

The total of the points will determine the winner of the Tournament.

I have 4 tabs. The first tab is an overall summary of the game.

enter image description here

The second tab is a grid showing who has played who. This is where I will need some assistance. I would like the Workbook to automatically fill out an X next to those who have already played each other.

enter image description here

The third tab is the raw data that is manually entered.

enter image description here

The fourth tab is my calculations tab that has a few pivot tables. As you can see from the second pivot table, it technically has the data that I need for the Summary by Game tab. However, I'm at a little loss how I can transpose the data from the pivot table to a user friendly grid.

enter image description here

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Assuming your raw data is in Sheet2, use the below formula in your cross tab cell B3 and copy across and down

=IF((COUNTIFS(Sheet2!$C:$C,B$2,Sheet2!$D:$D,$A3)+COUNTIFS(Sheet2!$C:$C,$A3,Sheet2!$D:$D,B$2))>0,"X","")

share|improve this answer
    
Had to change a small bit of your answer, but this worked nicely. I was trying to make it too complicated with vlookups. Wasn't the right function for the task. Kudos. –  kobaltz Oct 30 '12 at 18:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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