[Misc] Excel Wizards - help required

[Perkino]

I have a simple data base, which I want to use the data is one table to populate information in the second table.

Table one is a list of values indicating the amount of goals scored. Table two shows the teams that an individual scored against, I want this to self populate based on the team name above filling the relevant cell. My ultimate aim is to compile a list of the sides that each individual scored against in a short list

Any suggestions of the excel command required to populate table two. I obviously need to use this for work related purposes and not just a football scenario

 

[WASH]

Try this in the 2nd table in cell J2 for the Welbeck v Man Utd one: =IF(B2>0,$B$1,""). Then you can copy the formula into the other cells by dragging the formula from J2 down each column and it should work the same. Formula for K2 would be =IF(K2>0,$K$1,"") etc.

 

[Titanic]

Not sure I understand the need for Table 2... surely that information is available from Table 1 ?

 

[GingerBeerMan]

=IF(ISBLANK(B2), "", B$1)

This is from memory (not tested), but something like this should work.

 

[Titanic]

 

[Official Old Man]

You've forgotten the Wellbeck goal against Liverpool.

 

[Perkino]

Try this in the 2nd table in cell J2 for the Welbeck v Man Utd one: =IF(B2>0,$B$1,""). Then you can copy the formula into the other cells by dragging the formula from J2 down each column and it should work the same. Formula for K2 would be =IF(K2>0,$K$1,"") etc.

Thank you this does exactly what I wanted

 

[Papak]

Look who we score the most goals against

 

[Perkino]

View attachment 155937

this is brilliant

 

[GingerBeerMan]

View attachment 155937

This is sexy code. Very elegant.

 

[dazzer6666]

this is brilliant

Would be even better if the player’s names were all correct

 

[clapham_gull]

I think you've made the common mistake of storing your source data in presentational format (a matrix with row and column headers) then giving yourself the complication of transforming it.

The above range formula is neat, but will need to updated as you add a column for new team

A matrix also adds unnecessary noise of recording when players don't score (e.g. Undav against Man Utd) which is irrelevant to the result.

Much better to store it as a true database with the columns:

[Player], [Team], [Goals Scored]

.. ignoring entries where players don't score.

And then use Power Query to get the result you want

 

[Perkino]

I think you've made the common mistake of storing your source data in presentational format (a matrix with row and column headers) then giving yourself the complication of transforming it.

The above range formula is neat, but will need to updated as you add a column for new team

A matrix also adds unnecessary noise of recording when players don't score (e.g. Undav against Man Utd) which is irrelevant to the result.

Much better to store it as a true database with the columns:

[Player], [Team], [Goals Scored]

.. ignoring entries where players don't score.

And then use Power Query to get the result you want

The football data is simply a mock of two tables transferring data. I have been presented with the actual data in numerical values in the initial table but wish to produce an itemised listing of the information stored within table one. The raw data arrives from an external source electronically and I'm writing a document that will automatically pull the most relevant data and share it with the users in the most practical way.

Additional columns will be added to my data on a weekly basis but staff are loosing hours trying to analyse the data each week to have meaningful information

 

[clapham_gull]

The football data is simply a mock of two tables transferring data. I have been presented with the actual data in numerical values in the initial table but wish to produce an itemised listing of the information stored within table one. The raw data arrives from an external source electronically and I'm writing a document that will automatically pull the most relevant data and share it with the users in the most practical way.

Additional columns will be added to my data on a weekly basis but staff are loosing hours trying to analyse the data each week to have meaningful information

If I had time I could automate the whole thing for you with a script.

( ask me in a couple of weeks )