Grabbing any two characters instead of defined.

S

SSr00

New Member
Joined
Jul 14, 2023
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
I am having trouble getting the below duty roster to pick up any two characters, instead of only "DD". The goal is to count how many days since a person has last done a weekday or weekend duty from the end of the month, the input can be any two characters. Bonus points for anyone who can also figure out a way to get it to run without using the LET function, as this does not work with excel at work.

Any help at all would be hugely appreciated.

Excel Formula: 
=LET(days,FILTER(F4:AJ4,LEFT(F3:AJ3)<>"S"),maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))

Duties.DoNotEdit.1 - Maseter.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
101 Sep 2301020304050607080910111213141516171819202122232425262728293001
3FriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSun
4Pte 10DD75
5Pte 01DD211
6Pte 01DD210
7Pte 10DD05
8Pte 00215
9Pte 00215
10Pte 00215
11Pte 00215
12Pte 00215
13Pte 00215
14Pte 00215
15Pte 00215
Template
Cell Formulas
RangeFormula
F1F1=A1
G1:AJ1G1=F1+1
D4:D15D4=SUMPRODUCT((WEEKDAY($F$2:$AI$2,2)<6)*(F4:AI4 = "DD"))
E4:E15E4=SUMPRODUCT((WEEKDAY($F$2:$AI$2,2)>5)*(F4:AI4 = "DD"))
AK4AK4=LET(days,FILTER(F4:AJ4,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL4AL4=ROUNDDOWN((LET( days, FILTER(F4:AJ4, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK5AK5=LET(days,FILTER(F5:AJ5,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL5AL5=ROUNDDOWN((LET( days, FILTER(F5:AJ5, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK6AK6=LET(days,FILTER(F6:AJ6,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL6AL6=ROUNDDOWN((LET( days, FILTER(F6:AJ6, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK7AK7=LET(days,FILTER(F7:AJ7,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL7AL7=ROUNDDOWN((LET( days, FILTER(F7:AJ7, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK8AK8=LET(days,FILTER(F8:AJ8,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL8AL8=ROUNDDOWN((LET( days, FILTER(F8:AJ8, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK9AK9=LET(days,FILTER(F9:AJ9,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL9AL9=ROUNDDOWN((LET( days, FILTER(F9:AJ9, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK10AK10=LET(days,FILTER(F10:AJ10,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL10AL10=ROUNDDOWN((LET( days, FILTER(F10:AJ10, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK11AK11=LET(days,FILTER(F11:AJ11,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL11AL11=ROUNDDOWN((LET( days, FILTER(F11:AJ11, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK12AK12=LET(days,FILTER(F12:AJ12,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL12AL12=ROUNDDOWN((LET( days, FILTER(F12:AJ12, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK13AK13=LET(days,FILTER(F13:AJ13,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL13AL13=ROUNDDOWN((LET( days, FILTER(F13:AJ13, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK14AK14=LET(days,FILTER(F14:AJ14,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL14AL14=ROUNDDOWN((LET( days, FILTER(F14:AJ14, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK15AK15=LET(days,FILTER(F15:AJ15,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL15AL15=ROUNDDOWN((LET( days, FILTER(F15:AJ15, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
Named Ranges
NameRefers ToCells
DutyRange=Template!$F$2:$AG$20D4:E15
MyRange=Template!$F$4:$J$4,Template!$M$4:$Q$4,Template!$T$4:$X$4,Template!$AA$4:$AE$4AK4:AL4, D4:E4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E4:E50Cell Value>1textNO
D4:D50Cell Value>2textNO


EDIT:
Also, the headers in AK and AL should read "Last Weekday" + "Last Weekend" respectively.
 

Attachments

  • Screenshot 2023-07-21 143438.png
    Screenshot 2023-07-21 143438.png
    25.1 KB · Views: 6
Last edited by a moderator:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
For part 1 use
Excel Formula: 
=LET(days,FILTER(F4:AJ4,LEFT(F3:AJ3)<>"S"),maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("??",days,2,-1),0))
 
Upvote 1
Fluff said:
For part 1 use
Excel Formula: 
=LET(days,FILTER(F4:AJ4,LEFT(F3:AJ3)<>"S"),maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("??",days,2,-1),0))
Click to expand...
You are a hero. I tried to use wildcard characters before but it didn't seem to work?
 
Upvote 0
For part 2 you could try
Excel Formula: 
=NETWORKDAYS(IFNA(LOOKUP(2,1/((LEFT($F$3:$AJ$3)<>"s")*(F4:AJ4<>"")),$F$1:$AJ$1),$F$1),$AJ$1)-1
 
Upvote 1
Fluff said:
For part 2 you could try
Excel Formula: 
=NETWORKDAYS(IFNA(LOOKUP(2,1/((LEFT($F$3:$AJ$3)<>"s")*(F4:AJ4<>"")),$F$1:$AJ$1),$F$1),$AJ$1)-1
Click to expand...
You have no idea how grateful I am. Thank you so much!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 1
How about
Excel Formula: 
=FLOOR((NETWORKDAYS.INTL(IFNA(LOOKUP(2,1/((LEFT($F$3:$AJ$3)="s")*(F4:AJ4<>"")),$F$1:$AJ$1),$F$1),$AJ$1,"1111100")-1)/2,1)
 
Upvote 1
Fluff said:
How about
Excel Formula: 
=FLOOR((NETWORKDAYS.INTL(IFNA(LOOKUP(2,1/((LEFT($F$3:$AJ$3)="s")*(F4:AJ4<>"")),$F$1:$AJ$1),$F$1),$AJ$1,"1111100")-1)/2,1)
Click to expand...
Bingo.
If you ever need a kidney or anything, give me a shout lol. Thanks again!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Counting blank cells from specific range.
Replies
1
Views
217
Fluff
Fluff
E
  • Question
Repeating Unicode Character Issue (Publication calendar)
Replies
0
Views
148
EDV
E
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
358
Agnarr
Agnarr
M
  • Question
Recursive LAMBDA for progressive SMALL calculations
Replies
1
Views
88
macfuller
M
G
  • Solved
How to filter a table with two email columns against a list of emails
Replies
7
Views
354
dreid1011
dreid1011

Forum statistics

Threads
1,215,124
Messages
6,123,189
Members
449,090
Latest member
bes000

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