Matching formula over multiple pages

Hi guys,

Please refer to post #8, 10, 11, 12 below for a description of what i am trying to achieve along with an updated workbook with mock-up information. Post 12 gives detailed explanation of an example.

thank you in advance for anyone who takes the time to help me!


I made a "crosspost" in another forum https://www.mrexcel.com/forum/excel-...ml#post4807341

This looks like a crosspost: https://www.mrexcel.com/forum/excel-...ml#post4807341

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Look at the dates of the post. The post here at Excelforum was made yesterday. I was in no means trying to violate the rules. I haven't seen any replies so i tried another forum about 30 minutes ago.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a description of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Here is an updated version of the workbook to show a mock up as AliGW indicated.

Please refer to the page titled "Conference Stats" and look for the highlighted cells as examples.

The basic questions are these:

How many times has a specific team listed in column A played against teams from each conference listed from D:DZ?

How many times has a specific team listed in column A won against teams from each conference listed from D:DZ?

How many times has a specific team listed in column A lost against teams from each conference listed from D:DZ?

*The first 8 pages record every game played as indicated in the range T:AB. The "Team IDs" page shows which conference each team belongs to.

Forgive my ignorance (I'm just a Limey), but can you please explain the difference between the first eight tabs? I suspect that part of what is stopping people from helping is the sheer complexity of the spreadsheet, and I am struggling to work out where the figures in the yellow cells are coming from. Perhaps if you could explain (talk us through) the few examples you've given, we'd have a better idea of the type of formula needed.

It's a valid question. This is why it was hard to convey the message in a short post, so i was kind of stuck between a rock and a hard place. My apologies.

This workbook is about college basketball. Each year there is a tournament of 64 teams. They are divided into 4 divisions with 16 teams in each division. Each team in each division is given a "seed #" (1-16) based on their regular season record; 1 is the best and 16 is the worst. A #1 seed team plays #16 seed team, a #2 seed team plays a #15 seed team, a #3 seed team plays a #14 seed team and so on. Because this format happens in each division there will be (4) of each kind of match-up, so there will be 4 games where a #1 seed team plays a #16 seed team. If you look on page #1 vs #16 in cells T4:AB7 you can see the four match-ups that occurred in 1985. Every four rows going forward show another set of four games for the following year.

So with this in mind, page #2 vs #15 shows the match-ups between those seeds, page #3 vs #14 shows that match-up between seeds and so on.

So if you take each page and look at cells T4:AB7 you will find 64 teams or 32 match-ups for each year since 1985.

Does that make sense?

Let me explain the highlighted cells.

Step 1
Go to "Conference Stats" page.

Cell A5 = the team "Air Force"
Cell B5 = The total amount of times Air Force has made an appearance over each of the first 8 sheets (#1 vs #16, #2 vs #15, etc)
*Air force has only made 2 appearances over the first 8 sheets dating back to 1985.

Step 2
Go to "Seed Stats" page:
Cell V5 = the amount of times Air Force has been ranked a 13th seed.
Cell AF5 = The amount of times Air Force has been ranked an 11th seed.

Step 3
Go to #4 vs #13 page:
Cell T88:AB88 = The single time Air Force played against a #4 seed team while ranked as a #13 seed. They lost this game as indicated in Y88:AB88. The team Air Force played and lost against was Illinois. Remember this team.

Step 4
Go to #6 vs #11 page:
Cell T82:AB82 = The single time Air Force played against a #6 seed team while ranked as an #11 seed. They lost this game as indicated in Y82:AB82. The team they played was North Carolina. Remember this team.

* these are the only times that Air Force has ever played over the first 8 pages of this workbook. Once against Illinois and once against North Carolina. They lost both games.

Step 5
Go to Team IDs page:
Cell A91:F91 = Illinois and the conference they belong to which is the "Big Ten".
Cell A29:F29 = North Carolina and the conference they belong to which is the "ACC".

Step 6
Go back to "Conference Stats" page:
K5 = The amount of times Air Force has played against teams from the "ACC" conference (North Carolina).
M5 = The amount of times Air Force has lost against teams from the "ACC" conference.
* The workbook has the cell L5 highlighted and this is a mistake, it should be M5 because they lost.

AI5 = the amount of times Air Force has played against teams from the "Big Ten" conference (Illinois).
AK5 - The amount of times Air Force has lost against teams from the "Big Ten" conference.

The same process is used for the team Arizona State shown in A15 of the "Conference Stats" page and for every other team in Column A.

The goal is this: In the future I'd like to open this workbook and find out how well a team such as "Air Force" has done when playing against a certain conference.

Let's do this:

Go to page #1 vs #16. Look for the team "Florida" in Table 3, game 89 and game 117 (found in cells T92:AB92 & T120:AB120). Florida's opponents were Jackson State & Albany. Since I'm looking for how well Florida has succeeded against each conference (shown in cells C2:DZ2 of the "Conference Stats" page), we need to identify whether or not Florida has won or lost these two games and which conferences Albany and Jackson State belong to.

We can tell from cell AB92 of page "#1 vs #16" that Florida won their game against Jackson State. We also know from cell AB120 that Florida won their game against Albany. Positive numbers in the Margin of Victory Column (Cells AB:AB) indicate the team in Column V has won, while a negative number indicates they have lost. We can see that Florida has won both games. Now we go to the "Team IDs" page and look for both Albany and Jackson State to identify which conference each team belongs to.

Cells A2:D2 in "Team IDs" page shows Albany belongs to the conference called "American East". Cells A309:D309 show that Jackson State belongs to the "SWAC" conference. Since we know that Florida beat both teams we can say that Florida has played an American East team 1 time and has won so they are 1-0 against American East teams. We can say the same thing for Florida against SWAC teams.

With this in mind we can now go back to the "Conference Stats" page and find Florida at cell A89. We scroll over to cell C89 and enter 1 because Florida competed 1 time against a team (Albany) who belongs to the "American East" conference (Cell C89:F89 are the section for the American East Conference). Since Florida beat Albany we enter 1 into D89 (the win column). From there we repeat this same procedure for the SWAC conference which is found DG89:DJ89. We'd enter 1 in DG89 and 1 in DH89 because Florida competed and won against a team (Jackson State) from the SWAC conference.

So as of right now, this example shows that Florida is 1-0 against the American East conference and 1-0 against the SWAC conference. Those numbers may change as we find the other remaining 18 games Florida has played (we know Florida has had 20 tournament appearances from cell B89 of the "Conference Stats" page). We simply go to the next page (#2 vs #15) and repeat the same process as i illustrated above.

After we've gone through all 8 pages and every game is tallied for Florida, we will know how well Florida has done against each division listed on the "Conference Stats" page. Florida has appeared 20 times over the first 8 pages, but there are 32 conferences. Knowing this we will know ahead of time that Florida will not have played against each conference. Remember, the wins, losses, and appearances Florida has had against the American East and the SWAC may change or stay the same depending on which teams Florida has played against. We might find that on say, page #4 vs #13 Florida played another team from the SWAC conference and won thus increasing cells DG89 & DH89 in the "Conference Stats" page.

Does this make sense and is this possible to do?

Rachel - I've called in the cavalry - I hope that someone will be able to help you. I'm tied up this evening, I'm afraid.

Thank you AliGW i appreciate all your effort! I hope someone can find a solution.

I think this project is much easier for someone who inherently understands the NCAA setup - kudos to the non-Americans who gave it a look; it's a mess.

In my approach, I've simplified all of your lookups by adding the Conference information to each of your seeding tables. This enables me to then use the following in Conference Stats!C4 to get the count:

=COUNTIFS(Table3[1st Seed],[@Team],Table3[Conference16],C$2)+COUNTIFS(Table3[16th Seed],[@Team],Table3[Conference1],C$2)+COUNTIFS(Table5[2nd Seed],[@Team],Table5[Conference15],C$2)+COUNTIFS(Table5[15th Seed],[@Team],Table5[Conference2],C$2)+COUNTIFS(Table7[3rd Seed],[@Team],Table7[Conference14],C$2)+COUNTIFS(Table7[14th Seed],[@Team],Table7[Conference3],C$2)+COUNTIFS(Table9[4th Seed],[@Team],Table9[Conference13],C$2)+COUNTIFS(Table9[13th Seed],[@Team],Table9[Conference4],C$2)+COUNTIFS(Table11[5th Seed],[@Team],Table11[Conference12],C$2)+COUNTIFS(Table11[12th Seed],[@Team],Table11[Conference5],C$2)+COUNTIFS(Table13[6th Seed],[@Team],Table13[Conference11],C$2)+COUNTIFS(Table13[11th Seed],[@Team],Table13[Conference6],C$2)+COUNTIFS(Table15[7th Seed],[@Team],Table15[Conference10],C$2)+COUNTIFS(Table15[10th Seed],[@Team],Table15[Conference7],C$2)+COUNTIFS(Table17[8th Seed],[@Team],Table17[Conference9],C$2)+COUNTIFS(Table17[9th Seed],[@Team],Table17[Conference8],C$2)

And in D4 to get wins:
=COUNTIFS(Table3[1st Seed],[@Team],Table3[Conference16],C$2,Table3[Margin of Victory],">0")+COUNTIFS(Table3[16th Seed],[@Team],Table3[Conference1],C$2,Table3[Margin of Victory],"<0")+COUNTIFS(Table5[2nd Seed],[@Team],Table5[Conference15],C$2,Table5[Margin of Victory],">0")+COUNTIFS(Table5[15th Seed],[@Team],Table5[Conference2],C$2,Table5[Margin of Victory],"<0")+COUNTIFS(Table7[3rd Seed],[@Team],Table7[Conference14],C$2,Table7[Margin of Victory],">0")+COUNTIFS(Table7[14th Seed],[@Team],Table7[Conference3],C$2,Table7[Margin of Victory],"<0")+COUNTIFS(Table9[4th Seed],[@Team],Table9[Conference13],C$2,Table9[Margin of Victory],">0")+COUNTIFS(Table9[13th Seed],[@Team],Table9[Conference4],C$2,Table9[Margin of Victory],"<0")+COUNTIFS(Table11[5th Seed],[@Team],Table11[Conference12],C$2,Table11[Margin of Victory],">0")+COUNTIFS(Table11[12th Seed],[@Team],Table11[Conference5],C$2,Table11[Margin of Victory],"<0")+COUNTIFS(Table13[6th Seed],[@Team],Table13[Conference11],C$2,Table13[Margin of Victory],">0")+COUNTIFS(Table13[11th Seed],[@Team],Table13[Conference6],C$2,Table13[Margin of Victory],"<0")+COUNTIFS(Table15[7th Seed],[@Team],Table15[Conference10],C$2,Table15[Margin of Victory],">0")+COUNTIFS(Table15[10th Seed],[@Team],Table15[Conference7],C$2,Table15[Margin of Victory],"<0")+COUNTIFS(Table17[8th Seed],[@Team],Table17[Conference9],C$2,Table17[Margin of Victory],">0")+COUNTIFS(Table17[9th Seed],[@Team],Table17[Conference8],C$2,Table17[Margin of Victory],"<0")

In E4: =C4-D4
In F4: =IFERROR(D4/C4,"-")

Fill down, copy to the other conferences.

Adding the extra columns to your seeding tables threw off your data in D:R of each seeding table, so I redid those to get them back on track. There were a few other little things as well - "Mountain" in "Mountain West" was misspelled, which created a prolonged headache, and I froze your panes on the Conference sheet to make sifting through data easier.

I've spot checked a few teams and it seems to hold up, but you'll definitely want to double check everything. Unfortunately, the resulting workbook is too big to be posted here, so I'm attaching a partial version with the seeding tables to show the changes made there. Just use the formulas above on the Conference calculation sheet and you should have the complete picture. Let me know if you have any issues.

Currently, the F4 formula (winning %) shows a dash if there would be a DIV/0 error while the others show zeros. The E4 and F4 formulas can easily be tweaked to show blanks if zero, but attaching an IF clause to either of the two big formulas would make them monstrously long and slow the calculation even more than necessary. My advice would be to create a conditional formatting rule for the matrix on 'Conference Stats' that turns the font of any cell equal to zero white. That way the cell will appear blank, giving you the clean look of your 'Seed Stats' sheet while retaining the relative efficiency of the current formula. Alternatively, you could use File > Options > Advanced and uncheck the "Show a zero in cells that have zero value" check box.

Haha, sorry - I was referring to the NCAA's conference and tournament setup, not the workbook. I don't envy anybody unfamiliar with NCAA basketball trying to figure out the structure of March Madness by examining a spreadsheet. To a fan, though, your workbook and objective are instantly clear. I didn't see anything in your workbook that cried out for rearrangement, I just wish my end product had been small enough to post. Let me know if you have any difficulty recreating what I came up with.

True. That occurs because your table lists the higher seed first, whereas columns D and E list the lower seed first. You could switch D and E and their respective formulas so that the higher seed is shown first, or alternatively you could just switch the two INDEX returns of your formula in F4 so that the lower seed's score is shown first, so replace this current formula in F4:

=IFERROR(INDEX($AA:$AA,SMALL(IF(Table3[Year]=Table2[@Year],IF(Table3[Seed 16 Score]>Table3[Seed 1 Score],ROW(Table3[Year]))),INT((COLUMNS($D4:D4)-1)/4)+1))&" - "&INDEX($AB:$AB,SMALL(IF(Table3[Year]=$A4,IF(Table3[Seed 16 Score]>Table3[Seed 1 Score],ROW(Table3[Year]))),INT((COLUMNS($D4:D4)-1)/4)+1)),"")

With this (must still be array-entered):

=IFERROR(INDEX($AB:$AB,SMALL(IF(Table3[Year]=Table2[@Year],IF(Table3[Seed 16 Score]>Table3[Seed 1 Score],ROW(Table3[Year]))),INT((COLUMNS($D4:D4)-1)/4)+1))&" - "&INDEX($AA:$AA,SMALL(IF(Table3[Year]=$A4,IF(Table3[Seed 16 Score]>Table3[Seed 1 Score],ROW(Table3[Year]))),INT((COLUMNS($D4:D4)-1)/4)+1)),"")

Fill down, copy to columns J, N, and R.

Cantosh:

Do you remember which spelling changes you had to make?

"mountain" in CA2 of the "Conference Stats" sheet was missing an "n". It took me way too long to find...

You'll want to double check all of those headings on "Conference Stats" one more time. You're matching the conference names against the seeding sheets, so a typo will result in a failed match, which can almost imperceptibly effect your data. I got lucky - I only noticed the "mountain" issue because I happened to spot check a team with a victory over a Mountain West squad and I discovered that their numbers were off by one.

I think that's the only typo I found, but if you're relying on this data for something important, I would give it all another look.

I'm not sure exactly which formulas you're referring to, but the only significant calculation drag will come from the formulas on the Conference Stats sheet that I proposed in post #16. Even then, though, recalculating only took a few seconds on my computer. With the sheer number of formulas on that sheet, a little bit of lag will be unavoidable.

Glad I could help, good luck!

I would recommend a new thread - you'll get more eyes on the problem. I start (and hopefully end) jury duty today, so I don't know about my availability in the coming days. If the problem requires a sample workbook (most do), then try to recreate the issue on a miniature scale. If it involves extensive familiarity with the current workbook, you're welcome to post a link to the new thread here and I'll look when I get the chance if you haven't already received a solution.

The two types of formulas that tend to drastically increase processing times are array formulas and volatile formulas, and the solutions we've used on the Conference Stats sheet don't include either, though you have some array formulas on your seed sheets.

One MAJOR issue that may or may not have been addressed on the workbook you're using (and that I should have mentioned earlier - sorry!) is that table 18 (The Team IDs table) in your originally posted workbook is sized to cover A2:F1048576, which is enormous, and would drastically add to your processing time. In the partial sample that I sent back to you, I resized it to include only the needed rows. If you added my changes to your existing workbook, then please check the size of table 18 and resize it if necessary. That might make a big difference.

When I open my version of your workbook, it takes about 8-10 seconds to open on my (sorta crusty) work computer. That's not horrible given the calculations involved. Once the table sizing has been addressed, I'm not sure there are many changes you could make to the Conference Stats sheet to cut down on the processing speed and still get the desired results. On the other sheets, though, there are a few little tricks that may help - you might consider replacing some formulas with the values they generate. The array formulas you used on your seed sheets in columns D:R produce historical results that aren't likely to change or require much adaptability. Once you've verified that the formulas produced the desired results, copy the cells and paste only the values to replace the formulas with the values they generated. You would lose some flexibility, but gain processing speed since you're replacing array formulas with values. You could do the same thing with the conference lookups I added in columns W and Z of each seed sheet - copy the cells, then paste only the value over them. This would slightly help processing times (you're only replacing non-array formulas), but it might be inadvisable since you'd lose flexibility that you might want to keep for the next time a Conference USA team jumps to the Big East. Hope this helps!

After reading this post i immediately went back and resized table 18. Thank you for that tip. Unfortunately it didn't change the lag when entering data or the load time.

To test where the problem was i tried this:

Removed columns D:R on every seed page = Workbook loaded in about 7-8 seconds, but still had the lag when entering info in the tables.

Kept columns D:R on every seed page and deleted the conference stat sheet and the workbook loaded in about 3 seconds and no lag when entering data in the tables.

So it was definitely the conference stats page that's causing the lag. Would combining all the tables from the seed pages into 1 page and altering the countifs to calculate from 1 page rather than 8 reduce the lag? I'm not sure if it's even possible to link all 8 tables from 8 pages into 1 separate page.

If the lag is prohibitive, it might be worth a try. I have to confess that I'm not an expert in processing times, so my advice here is largely guesswork, but if you're willing to try merging your sheets, it might be instructive. I'm not quite clear on whether you're proposing combining the seed sheets into an additional master sheet or replacing the seed sheets with one master sheet, but either one might be worth trying. I would imagine that replacing your seed sheets with a mega-sheet would be faster, but I don't know how big the difference would be. Keep in mind that there are over 45,000 formula cells on your Conference Stats sheet, so you might just have to live with a bit of lag; by the same token, though, shortening the formulas saves you 45000 x However many milliseconds are saved.

If you're up for it, it might be worth a try. I would be a little wary of hoping to speed things by adding more tables and formulas, but it's possible that using that new table to shorten the formulas on the Conference Sheet could make the additions worthwhile. When I mentioned replacing your seed sheets, I envisioned one master table with every entry from your seed sheets, but with two new columns to track the respective seeds. the end result would be very similar to what you proposed, but it would allow you to delete all of the old seed sheets and just work from the new master result sheet. It wouldn't look as nice, but you could do the same shortening of the Conference Stats formulas while also reducing your overall number of tables, sheets, and formulas. Again, though, you know your project better than anyone, so only experiment with what seems doable and don't eliminate anything you might need. And, of course, don't forget that whatever you try still might not make an appreciable difference.