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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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

T
  • Question
Help with monthly update/tracking
Replies
1
Views
60
awoohaw
awoohaw
C
  • Question
Finding Most Recent Date from Specific Date
Replies
2
Views
57
SanjayGMusafir
SanjayGMusafir
ke6zoy
  • Solved
Looking up next travel date for staff travel schedule
Replies
4
Views
177
felixstraube
F
I
  • Solved
Generate list of players for four school teams
2
Replies
16
Views
545
imcl75
I
G
  • Question
Find teams using a found average
Replies
5
Views
182
Burrgogi
Burrgogi

Forum statistics

Threads
1,217,488
Messages
6,136,931
Members
450,033
Latest member
germani40

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