Array formula to extract unique records, sort

[enasnidx]

I'm sorry, I am not very sharp with Excel. I tried watching vids on magic tricks #690 and #759 but didn't quite get to what I wanted to do. Can someone help me figure out a formula to accomplish task?
I am tracking competition scores over several weeks for a list of teams. The team list grows each week/competition with some unique teams added and numerous repeat teams. Is there a formula to take the list of current teams/scores and condense it each week with another list of teams/scores - remove duplicate team names, sort alphabetically, and add the new score to the list?

Example:
Week 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Team[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

Week 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Team
[/TD]
[TD]Score
[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Golf[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

Condensed (goal)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Team
[/TD]
[TD]Score 1
[/TD]
[TD]Score 2
[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]20[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]25[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]15[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Golf[/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!

 

[Leith Ross]

Hello enasnidx,

Here is the sample workbook Extract Unique Records and Sort.xlsm

Let me know if you have any problems.

 

[enasnidx]

Wow, this is incredible. For the past two seasons I've been manually condensing score lists and deleting duplicate teams. It's about a 3 hour process after every contest. Only question I have is if there is a way for a team to get a blank or 0 value for a score if they didn't participate that week? In the workbook, their scores automatically slide over to the next available score count. For example team A gets a score in week 1 and week 3, can it show "score 1", (blank or zero), then "score 3"?

You are awesome Leith!!! Thanks for your help on this.

 

[Leith Ross]

Hello enasnidx,

Here is the amended macro to add zeroes to unused weeks. Basically all weeks are first initialized to zero then overwritten with each weeks score.

Extract Unique Records and Sort ver 2.xlsm

 

[enasnidx]

Great work. You're the best.