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,

Does your post faithfully reflect your data layout on the worksheet?

 

[enasnidx]

Hello enasnidx,

Does your post faithfully reflect your data layout on the worksheet?

If I understand your question correctly, yes it does. I am getting a new data set every week: column A is team list, column B is their score that week. I'd like to be able to merge the new score sheet every week into the old list so that I can have a worksheet with a running total: column A is team list (duplicates removed), column B is score from week 2, column C is score from week 3, and so on. If a team didn't compete that week there is a blank cell for that column.

 

[Leith Ross]

Hello enasnidx,

That is what I wanted to know. Also, do you have the week included in the data or is just the header TEAM repeated in column "A"?

 

[enasnidx]

Just "team" in column A. Column B is "score". Not labeled with "week X".

 

[Leith Ross]

Hello enasnidx,

Okay, I get the picture. I can do this for you in VBA. It may be possible to use formulas but to the complexity of your request, I doubt it. So, is VBA a option for you?

 

[enasnidx]

I don't think I have access to the Developer function in Excel here at work. I may on my home PC, but need to check. Is VBA still an option for me?

 

[Leith Ross]

Hello enasnidx,

If you can run workbooks with XLSM extensions at work then VBA is available.

 

[enasnidx]

yes i can

 

[Leith Ross]

Excellent, I'll put a sample workbook together and post the link so you download it.