Create football standings table from raw data in excel

Question

0.00/5 (No votes)

See more:

I have a table containing a some football data, such as Country, League, Teams, Standing table information, such as total matches played, wins, draws, losses, goals scored and conceded, and so on.

Here's a file download link:

It contains two sheets:
STANDINGS_EXTENDED - containing formatted standings tables for Overall, Home and Away teams.
STANDINGS - row data for use in STANDINGS_EXTENDED worksheet.

My aim is that once I fill LeagueId and Group Id (which is optional) fields then inside all three tables will be produced the data as in this sample.

I wonder if it is possible to achieve this without VBA. But i have no clue on where to start from. x

Looking forward to your help.

Thank you!

What I have tried:

I tried is several different ways, but i get only first result from STANDINGS worksheet for any league i enter.
So far I could get the count of rows related to Overall, Home and Away using these formulas:

=COUNTIFS(STANDINGS!E:E;STANDINGS_EXTENDED!E1;STANDINGS!F:F;"StandingsOverall")

=COUNTIFS(STANDINGS!E:E;STANDINGS_EXTENDED!$E$1;STANDINGS!F:F;"StandingsHome")

=COUNTIFS(STANDINGS!E:E;STANDINGS_EXTENDED!$E$1;STANDINGS!F:F;"StandingsAway")

Also, what I can get is the first row of these results using this formula:

=VLOOKUP($E$1;STANDINGS!$E:$V;4;FALSE)

What I need to figure out is how to modify above formulas so that I fill tables with remaining rows.

Posted 25-Dec-20 0:03am

Zaur Bahramov

Updated 25-Dec-20 2:56am

v2

Add a Solution

2 solutions

Solution 1

We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]

Posted 25-Dec-20 0:05am

OriginalGriff

Comments

Zaur Bahramov 25-Dec-20 6:11am

Thank you! Of course I don't want someone to do this for me. I will not learn anything this way. As I have written in my question "I wonder if it is possible to achieve this without VBA. But i have no clue on where to start from."
By this I mean if someone could give me a hint on where to start from. For example, first is if it is doable without need to write VBA code. If it is, then I would appreciate some hint on where to start from or which are the functions that can help me to resolve. So, I wanted someone to help me identify the right roadmap, absolutely not do this for me. Hope this clarifies the point of my question. Thank you again!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Zaur Bahramov · Accepted Answer · 2020-12-25T02:56:00

Resolved as was suggested elsewere:

In order to do this you need a formula in every single field of your 3 tables that link it to data on the Standings tab. That would be 13 x 3 x 20 formulas. Therefore one would try to create formulas that can be copied, in the best case less than 13 original ones, but definitely one formula for each field.

Each formula would look for a unique identifier in the Standings list. I can't see any unique identifiers there but you might create them by concatenation, such as "League" + "Country" + "Position". The more detail you need the larger the formula. The key is: without a unique identifier for each row you can't retrieve data. But once a row has been identified you can get the value from any of its columns.

If your tables sometimes have 12 rows, sometimes 20, and sometimes 25 you must provide space for the possible maximum and then design your formulas to return a blank if there is nothing to display.

In conclusion, the core of your system is in the Standings table. It must be set up so that data can be retrieved from it. Ideally, your selection on the Standings Extended sheet would generate a concatenated unique identifier for a list to which you can add the fixed number in the Pos column to identify individual rows in the Standing table. As long as you can't identify rows no data can be retrieved.

Using VBA gives you more flexibility but doesn't relieve you of the task to create uniquely identifiable rows.