[Solved] Rearrange NFL Score Results as Visitor Team Score and Home Team Score

[Boy282828]

Dear Lutfor,



I spent hours recording the NFL Scores in my Excel workbook which drove me crazy.

Could you write VBA Code so that once I select the whole Teams Score Array and

Run the VBA, it will turn into the Second Array I desired below the Original Array?

Please keep the original Array for me to check or use in the future.



The Problem of the Original Array is it shows all winners in one Column, and shows

all losers in another Column disregard Visitor Teams or Home Teams. For each Row

I must think and find which Home team is winner or loser and which Visitor team is winner

or loser then record them in my Visitor and Home Score Column, it consumes too much time.

On the original Array, Could you get rid off the Box Score column, the Original L,M,N,O

Columns ( Their Titles are YdsW TOW YdsL TOL ) , turn Winner Points Score

PtsW column and Losser Point Score PtsL column into Away Team column score and

Home team column score on the second Array as I desired? On the worksheet TMS,

There is team and symbol conversion, I created “TM” and “SYMBOL” list with the

same row numbers for you to convert using XLOOKUP Function. As to the Time Column,

The original use East Time(ET), could you convert to West Pacific Time(WT) since I live

in California which use Pacific West Time. You may wonder why the teams from the top to

the bottom are different? That is because before the game starts and at the end of the game

scores they are arranged not the same sequence, I record score results in the original sequence,

you don’t need to do the same sequence as I did. Between ET & WT Time, there is 3 hours

difference. ET runs 3 hours ahead of WT, so ET minus 3 hours is WT. Hope you understand

what I desire in the second Array. If it is not clear to you, feel free to ask me for more

explanation. Thanks and regards,



Dawson 9/25/2025

 

[shamimarita]

Hello,

You can follow the following steps;

Identify Home & Away Teams:

• In your current table, the Winner is in column F and the Loser in column H.
• The scores are in columns J (PtsW) and K (PtsL).
• To map them as Home vs Away, you need the @ indicator in column G.
  • If @ is with the Winner, then Winner = Away, Loser = Home.
  • If @ is with the Loser, then Loser = Away, Winner = Home.

Create Helper Columns: Add formulas to classify Home and Away teams & scores. For example, in row 4:

• Away Team:

=IF(G4="@",F4,H4)

• Home Team:

=IF(G4="@",H4,F4)

• Away Score:

=IF(G4="@",J4,K4)

• Home Score:

=IF(G4="@",K4,J4)

Add Symbols (Abbreviations): Use VLOOKUP (or XLOOKUP if available) against the TMS sheet to pull the short codes:

=VLOOKUP([@[Away Team]],TMS!B:C,2,FALSE)
=VLOOKUP([@[Home Team]],TMS!B:C,2,FALSE)

This way, “Buffalo Bills” becomes “BUF,” “Miami Dolphins” → “MIA,” etc.

Final Table:

Build your output with:
| Week | Date | Away Symbol | At | Home Symbol | Away Score | Home Score |

All automatically linked to the SCORES sheet.

If you want this fully automated, Power Query is the best choice. You can load the SCORES and TMS tables into PQ, merge them, and restructure columns. When new data comes in, just click Refresh.

 

[Boy282828]

Dear Shamimarita,



Thanks for your kind help. The problem has been resolved.

Please close the case. Last Year Lutfor helped me a lot, please

give my appreciation and regards to him if he still works in

your company. If he no longer works in your company,

I wish him all the Best.

Thanks,

Boy282828 9/26/2025

 

[shamimarita]

Dear Boy282828,

Thank you for letting us know, I’m glad the issue has been resolved.
I’ll make sure to pass along your appreciation and kind regards for Lutfor. Your thoughtful message will surely mean a lot to him.