Help with which excel formula to use.

2

20rayallen

New Member
Joined
May 17, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

I'm praying someone can help me on how to tackle this? I've attached a schedule (columns D-P) that's displayed by day and time, with team name and team number (did a lookup to bring team name in) and there are cells that are merged. . . Now, what I want to do and can't figure how to do it is - Filtering or populating a schedule by Teams at the time and who they're playing - I have an example in columns R-U. Also, I would like to put if they're playing on that day and/or time would like to put "No". I hope that all makes sense.

Thank you in advance.
sample.jpg
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
I'd do a thing like this - I'd store the data separately in a table. So a flat table of data (A-E) and the team lookup (H-I). Then I'd recreate some kind of overall schedule (K-Q) and a separate team schedule (S-V)
MrExcelPlayground23.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1WeekDateStart TimeAway TeamHome TeamTeam CodeTeam NameWeek1TeamBulls
215/13/20246:30:00 PM9101BullsDate6:30 PM7:30 PM8:30 PMDate6:30 PM7:30 PM8:30 PM
315/13/20247:30:00 PM7102Knicks5/13/20249-Lakers10-Celtics7-Pirates10-Celtics5-Sox11-Avalanche5/13/2024NoNoNo
415/13/20248:30:00 PM5113Pacers5/14/20246-Yankees11-Avalanche1-Bulls3-Pacers1-Bulls2-Knicks5/14/2024NoBulls @ PacersBulls @ Knicks
515/14/20246:30:00 PM6114Wizards5/15/20244-Wizards12-Wild8-Nuggets9-Lakers7-Pirates9-Lakers5/15/2024NoNoNo
615/14/20247:30:00 PM135Sox
715/14/20248:30:00 PM126Yankees
815/15/20246:30:00 PM4127Pirates
915/15/20247:30:00 PM898Nuggets
1015/15/20248:30:00 PM799Lakers
1125/20/20246:30:00 PM81010Celtics
1225/20/20247:30:00 PM71211Avalanche
1325/20/20248:30:00 PM71112Wild
1425/21/20246:30:00 PM512
1525/21/20247:30:00 PM65
1625/21/20248:30:00 PM14
1725/22/20246:30:00 PM24
1825/22/20247:30:00 PM23
1925/22/20248:30:00 PM610
Sheet1
Cell Formulas
RangeFormula
K3:K5K3=UNIQUE(FILTER(Table1[Date],Table1[Week]=L1))
S3:S5S3=UNIQUE(FILTER(Table1[Date],Table1[Week]=L1))
T3:V5T3=LET(a,FILTER(Table1[[Away Team]:[Home Team]],(Table1[Week]=$L$1)*(Table1[Start Time]=T$2)*(Table1[Date]=$S3)*((Table1[Away Team]=XLOOKUP($T$1,Table2[Team Name],Table2[Team Code],"",0))+(Table1[Home Team]=XLOOKUP($T$1,Table2[Team Name],Table2[Team Code],"",0)))),b,XLOOKUP(a,Table2[Team Code],Table2[Team Name],"",0),IFERROR(TEXTJOIN(" @ ",TRUE,,b),"No"))
L3:Q5L3=LET(a,FILTER(Table1[[Away Team]:[Home Team]],(Table1[Week]=$L$1)*(Table1[Start Time]=L$2)*(Table1[Date]=$K3)),b,XLOOKUP(a,Table2[Team Code],Table2[Team Name],"",0),a&"-"&b)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
T1List=$I$2:$I$13
 
Upvote 0
JamesCanale said:
I'd do a thing like this - I'd store the data separately in a table. So a flat table of data (A-E) and the team lookup (H-I). Then I'd recreate some kind of overall schedule (K-Q) and a separate team schedule (S-V)
MrExcelPlayground23.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1WeekDateStart TimeAway TeamHome TeamTeam CodeTeam NameWeek1TeamBulls
215/13/20246:30:00 PM9101BullsDate6:30 PM7:30 PM8:30 PMDate6:30 PM7:30 PM8:30 PM
315/13/20247:30:00 PM7102Knicks5/13/20249-Lakers10-Celtics7-Pirates10-Celtics5-Sox11-Avalanche5/13/2024NoNoNo
415/13/20248:30:00 PM5113Pacers5/14/20246-Yankees11-Avalanche1-Bulls3-Pacers1-Bulls2-Knicks5/14/2024NoBulls @ PacersBulls @ Knicks
515/14/20246:30:00 PM6114Wizards5/15/20244-Wizards12-Wild8-Nuggets9-Lakers7-Pirates9-Lakers5/15/2024NoNoNo
615/14/20247:30:00 PM135Sox
715/14/20248:30:00 PM126Yankees
815/15/20246:30:00 PM4127Pirates
915/15/20247:30:00 PM898Nuggets
1015/15/20248:30:00 PM799Lakers
1125/20/20246:30:00 PM81010Celtics
1225/20/20247:30:00 PM71211Avalanche
1325/20/20248:30:00 PM71112Wild
1425/21/20246:30:00 PM512
1525/21/20247:30:00 PM65
1625/21/20248:30:00 PM14
1725/22/20246:30:00 PM24
1825/22/20247:30:00 PM23
1925/22/20248:30:00 PM610
Sheet1
Cell Formulas
RangeFormula
K3:K5K3=UNIQUE(FILTER(Table1[Date],Table1[Week]=L1))
S3:S5S3=UNIQUE(FILTER(Table1[Date],Table1[Week]=L1))
T3:V5T3=LET(a,FILTER(Table1[[Away Team]:[Home Team]],(Table1[Week]=$L$1)*(Table1[Start Time]=T$2)*(Table1[Date]=$S3)*((Table1[Away Team]=XLOOKUP($T$1,Table2[Team Name],Table2[Team Code],"",0))+(Table1[Home Team]=XLOOKUP($T$1,Table2[Team Name],Table2[Team Code],"",0)))),b,XLOOKUP(a,Table2[Team Code],Table2[Team Name],"",0),IFERROR(TEXTJOIN(" @ ",TRUE,,b),"No"))
L3:Q5L3=LET(a,FILTER(Table1[[Away Team]:[Home Team]],(Table1[Week]=$L$1)*(Table1[Start Time]=L$2)*(Table1[Date]=$K3)),b,XLOOKUP(a,Table2[Team Code],Table2[Team Name],"",0),a&"-"&b)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
T1List=$I$2:$I$13
Click to expand...
I really appreciate your response. I will most likely give this a try on Sunday. . . much appreciated
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
How to find till which row the marco executed. It stopped at some random row without any error and unable to identitfy at what row it has been stopped
Replies
3
Views
187
FormR
FormR
T
  • Question Question
Help with monthly update/tracking
Replies
1
Views
254
awoohaw
awoohaw
S
  • Solved
Way to move values based on another cell around like chess pieces
Replies
4
Views
328
myall_blues
myall_blues
C
  • Question Question
Finding Most Recent Date from Specific Date
Replies
2
Views
245
SanjayGMusafir
SanjayGMusafir
ke6zoy
  • Solved
Looking up next travel date for staff travel schedule
Replies
4
Views
311
felixstraube
F

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top