Plug-in scores to a results sheet

I've just had a quick look at this and I confess I can't entirely fathom what's happening... for ex. if we filter the weekly_scores sheet such that we're looking only at QK as home side (Col A) and then try and tie that back to the AT: QK section on the results...

It would "appear" that the number suffix post side (eg QK1, QK4) should match the number suffix post # sign on weekly scores sheet (Cols F & I), correct ?

However, if you look at the results summary for AT: QK and compare to weekly_scores data we find the sides don't actually tie out.

For ex. in row 21 you have a result between QK2 and LA2 ... LA are not listed as away assoc on your scores sheet ?

I would in fact have expected this line:

Please Login or Register  to view this content.

to read

Please Login or Register  to view this content.

given the flag number is #1 for both sides and the opponent is seemingly PA not LA.

Can you provide any further insights as to how this works ?
(ie am I right and this is an error in your results sheet or am I misreading how this is meant to work ?)

OK... but that doesn't really answer my question.

Let's change tack... and look at row 29 on results sheet
(with row 28 being AT: PD)

M29: 80-N
N29: PD1
P29: NS1

Having looked at the data that is listed on results sheet thus far I interpret the requirements in terms of matching to weekly_scores as being:

Column A = PD (@ PD)
Column D = 5 (week 5)
Column F = 80 #1 ... 80 from M29 plus # from N29 (where FL-N becomes Flag # etc...)
Column H = NS (remove the # from string in P29)
Column I = 80 #1 ... 80 from M29 plus # from P29 (where FL-N becomes Flag # etc..)

So where we find

Please Login or Register  to view this content.

we retrieve scores from columns G & J respectively (Home & Away)

Is the above all correct ?

The above is all based on my interpretation of your existing results. In your posts you fail to explain the relationship between these values and thus given the vague nature of the data and narrative most will have looked at this attachment and left it given it's not clear how you're getting the results. This is made all the worse if there are results that do not appear to tie out to source data as highlighted in my prior post.

I'm honestly not sure - there seem to be quite a few inconsistencies.

Attached is about as close as I could get based on your sample data - if you compare the two results sheets you will see there are a few differences, I don't know if this is because my sheet (results_DO) is more or less accurate than the original... for ex. you show results for LA / PT at home whereas I can find no records for these home associations on your weekly_scores sheet.

Technically speaking you can do without the key but it reduces the workload significantly ie multi criteria lookups are generally inefficient.

The layout of the fixtures table(s) is obviously not overly conducive to any type of analysis which is why the formula is quite convoluted ... with a better layout you could no doubt streamline but it should work ok on reasonably sized datasets.

Has dawned on me rather belatedly that you could dispense with this section

C7 etc: ...LOOKUP(2,1/(A$6:A6="AT :"),B$6:B6) and replace with just LEFT(B7,2) assuming all associates are always @@

New question - new thread please.

In your new thread it would also be in your interests to outline the structure of your MySQL db - Table, Fields & Types
perhaps include a 2nd sheet which has a column for each field etc with header to denote type

In terms of getting info. from XL to MySQL: there are various routes open to you ranging from VBA/ADO to .csv/.txt files with MySQL command line prompts.

In the most general of terms the process itself is not overly complex once/if the source data is stored in a coherent manner.