Merging Lists

[mjs59]

I'm wondering if you will be able to help me...

I am putting together a league table spreadsheet, I have set up th
template for each game, and from this I can calculate the leagu
tables. This is the main use, but I would also like to use the score
information put into each game to produce top scorer lists.

I have done this by simply copying over all player data, whether actua
or blank by linking the cells and then sorting. This is fine for eac
round of games. But I am stumped for how to do cumulative tables.


For example,
Round 1 Top Scorers.
John Smith 3
Richard Jones 1
David Roberts 1

Round 2 Top Scorers
Richard Jones 2
John Smith 1
Paul Davies 1

The cumulative table should read

John Smith 4
Richard Jones 2
David Roberts 1
Paul Davies 1


Does anyone have any ideas on how to do this, preferably automatically

 

[Max]

I might set it up this way ..

Assuming you have

In sheet: Round1
(cols A and B, data in row2 down)

Round 1 Top Scorers.
John Smith 3
Richard Jones 1
David Roberts 1

In sheet: Round2

Round 2 Top Scorers
Richard Jones 2
John Smith 1
Paul Davies 1

In sheet: Round3

Round 2 Top Scorers
Richard Jones 1
John Smith 1
Paul Davies 1

and so on ..

In a sheet: Summary
----------------------------
Create a summary table in cols A to F (assuming 3 rounds)
headers in row1, data from row2 down

Scorer............TieBreak..TotalToDate..Round1..Round2..Round3
John Smith
Richard Jones
David Roberts
Paul Davies
etc

The headers "Round1", "Round2" and "Round3" listed in D1:F1
will be the sheetnames for each round as indicated earlier
(headers listed must match exactly the sheetnames given)

Put in D2 (for "Round1"):

=IF(ISNA(MATCH($A2,INDIRECT("'"&D$1&"'!$A:$A"),0)),"",OFFSET(INDIRECT("'"&D$
1&"'!$A$1"),MATCH($A2,INDIRECT("'"&D$1&"'!$A:$A"),0)-1,1))

Copy D2 across to F2

Put in C2 (for "TotalToDate"): =SUM(D2:F2)
Put in B2 (for "TieBreak"): =IF(C2="","",C2+ROW()/10^10)

Select B2:F2, fill down as many rows as there are scorers in col A

Col C will return the cumulative totals to-date, with details
for each round extracted from the respective sheets in cols D to F

Col B will serve as the tie-breaker col to separate arbitrarily
any identical totals in col C
(Col B can be hidden away from view)

In a sheet: TopScorers
----------------------------
Put headers in A1:B1 : Top Scorers, TotalToDate

Put in A2:

=IF(ISERROR(MATCH(LARGE(Summary!$B:$B,ROW(A1)),Summary!$B:$B,0)),"",OFFSET(S
ummary!$A$1,MATCH(LARGE(Summary!$B:$B,ROW(A1)),Summary!$B:$B,0)-1,COLUMN(A1)
-1))

Copy A2 across to B2,
then fill down as many rows as there are scorers in col A of "Summary"

Cols A and B will return the top scorers in descending order

 

[mjs59]

Thanks, very much for that, very interesting, and very useful. The onl
thing is that I need to enter the player name on the summary sheet
would it be possible for this to be done automatically?

Anyway a very interesting couple of formulas, certainly a change fro
endless VLOOKUPs! Could you reccamend any good web tutorials, as i
seams to be a better method, and I need to extend the original us
slightly.


If anyone is interested, I have posted the file. It will (in time) be
automatic league table/data calculator for the Zurich Premiership i
England, a 12 team, 22 round (home and away) Rugby Union league. So fa
I have just the first 4 rounds (using last seasons data).
'Zurich Premiership Excel File
(http://www.caldecote57.freeserve.co.uk/ZP200405.xls)

Any advice on best practice would be greatly appreciated!

Many thanks,
Mic

 

[Max]

One correction, sorry:

In the sheet: TopScorers
----------------------------
Formula in A2 remains unchanged

But, after copying A2 across to B2,
just amend the last part of the formula in *B2*
from: ... COLUMN(B1)-1
to: .. COLUMN(B1)
(i.e. delete the "-1")

This correction will ensure that we're extracting
from the correct column C ["TotalToDate"] in "Summary"
(instead of the wrong column B ["TieBreak"])

And as for entering the names in col A of "Summary"
why not just do a one-time copy/paste of *all* the players ?
(the suggested set-up allows for this)

The sheet "TopScorers" will only show & rank those players
who scored at least 1 goal, so it'll still be a clean display

 

[Max]

In the sheet: TopScorers

----------------------------
Formula in A2 remains unchanged

But, after copying A2 across to B2,
just amend the last part of the formula in *B2*
from: ... COLUMN(B1)-1
to: .. COLUMN(B1)
(i.e. delete the "-1")

Missed out mentioning (just in case <g>):
After amending the formula in B2 as given above,
re-select A2:B2, and fill down

 

[mjs59]

Thanks very much.

Re: Typing the names in, the idea is that the spreadsheet will be buil
up week by week, so a complete list will not be available at the start
although thinking about it, I was planning to have a team workshee
with statistics for each team, so listing the squad shouldn't be to
difficult.

Thanks,
Mic

 

[Max]

Could you recommend any good web tutorials ..

Here's a couple you may want to check out ..
(List is certainly *not* exhaustive .. )

David McRitchie's .. :
http://www.mvps.org/dmcritchie/excel/excel.htm#tutorials
http://www.mvps.org/dmcritchie/excel/excel.htm#vbtutorials

Debra Dalgleish's:
http://www.contextures.com/tiptech.html

Chip Pearson's:
http://www.cpearson.com/excel/topic.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/index.html

John Walkenbach's:
http://j-walk.com/ss/excel/index.htm

Tushar Mehta's:
http://www.tushar-mehta.com/

Jon Peltier's:
http://www.geocities.com/jonpeltier/

Bob Phillip's:
http://www.xldynamic.com/source/xld.UsingMenu.html

Ron de Bruin's [Google search Add-In]:
http://www.rondebruin.nl/Google.htm

And don't forget these wonderful excel newsgroups <g>

 

[Max]

Thanks very much. ..

You're welcome, Mich !
Pleased to help

 

[mjs59]

Thanks very much for the links.

Thinking around the dupication problems, is there anyway that you ca
stop a lower value in a list from being shown, if the same is above.

For example;

Mark Davies
John Smith
David Jones
Mark Davies

Here a way to remove duplication would be for all cells to display
zero value, if they were duplicated above. The there any way to do
formula similar to:

=IF(would be value imported into the cell = any values above in thi
column, zero, would be value imported into the cell)

If you get my drift!

Many thanks,
Mic

 

[Max]

The list of players in col A of "Summary" *is* assumed to be unique (no
duplicates)

But if you're asking how to check for duplicates in a list and delete these,
say for the sample data below assumed in A2:A5, one way ..:

Mark Davies
John Smith
David Jones
Mark Davies

Put in B2: =IF(COUNTIF($A$2:A2,A2)>1,1,"")
Copy down to B5
This'll mark any duplicates in col A with a "1" in col B

Now do an autoFilter on col B > select "1"
This will filter out all the duplicate rows
Select all these duplicate rows (all the "blue" row headers)
Right-click > Delete Rows
Then just remove the autofilter and you should be left with a list of
uniques in col A

 

[mjs59]

Thank for all of the help, i'm starting to get to grips with it.

I was wondering if you would be able to help with another problem tha
I am encountering.

In the spreadsheet ('Zurich Premiership.xls
(http://www.caldecote57.freeserve.co.uk/ZP200405.xls) ), I have a wee
table for each round of matches. I am trying to chart each team
position over the season, but i'm not sure how to do it as a norma
VLOOKUP won't work, due to the order of the data. I guess a combinatio
of INDEX and MATCH would work, but so far I haven't got it to work.

As an aside, i'm sure a similar method could be used to calculate th
tables, currently i'm adding two VLOOKUPs one from the previou
cumulative and one from the current round, but an alphabetically sorte
version. Any ideas on this?

The attached file should clear up my descriptions, although it is wort
noting that Week 1 tables, takes a slightly different form to th
others.

Many thanks,
Mic

 

[Max]

Thank for all of the help, i'm starting to get to grips with it...

You're welcome, Mich.

But I would suggest that you re-post your questions afresh to avail
your posts to the wider audience, following some principles below.

To effectively tap the resources of these excel newsgroups:
a. Post only in plain text, *one* question per post, with sufficient
descriptives of your set-up, current formulas used and intent.
b. Do *not* post any attachments, nor rely on providing links to files
as a substitute for (a). Many will not download files for obvious reasons.

Read Chip Pearson's posting guidelines for new posters:
http://www.cpearson.com/excel/newposte.htm

All the best!