Find, Max function

V

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,060
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
I have this roster and need to find
1.The max time shift for every emp in No of shift column
2.Need to display the week day off in Week off column
by any change is there a function or VBA code to find the same. below is the sample

SundayMondayTuesdayWednesdayThursdayFridaySaturday
IDName7/11/217/12/217/13/217/14/217/15/217/16/217/17/21No of Shift (Output)Week Off (Output)
1​
aOFF1500-21001500-21001500-21001500-21001500-21001200-21001500-2100Sun
2​
bOFFOFFOFFOFFOFFOFFOFF-sun,Mon,tue,Wed,thu,fri,Sat
3​
cOFF0800-18300800-18300800-18300800-1830OFFOFF0800-1830Sun,Fir,Sat
4​
d0700-15300700-1530OFFOFF0700-15300700-15300700-15300700-1530Tue,Wed
5​
e0700-11001030-1430OFFOFFOFF1030-14300700-11000700-1100 : 1030-1430Tue,Wed,thu
6​
fOFF0900-17000900-17000900-17000900-17000900-1700OFF0900-1700Sun, Sat
7​
g1630-0100OFFOFF1630-01001630-0100No longer employed1630-01001630-0100Mon, Tue
8​
h1000-18301000-18301000-1830OFFOFF1000-1830Suspended1000-1830Wed,thu
13​
mOFFLeave of absenceLeave of absenceLeave of absenceLeave of absenceLeave of absenceOFF-Sun, Sat
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Here is a user defined function you can use for days off.

Steps
  1. Copy the code below into a module in a copy of your workbook to test.
  2. Enter "=DaysOff($C$1:$I$1,C3:I3)" into the "Week Off (Output)" column
    1. Where $C$1:$I$1 is the column headers for days of the week and C3:I3 is the row you are reviewing
  3. Then copy the formula down for as many rows as you want to check
VBA Code: 
Function DaysOff(days As Range, sel As Range) As String
    Dim d As Variant
    Dim s As Variant
    Dim i As Integer
    
    
    If sel.Rows.Count <> 1 Then
        DaysOff = "#ERROR! Select only 1 row of data."
        Exit Function
    End If
    
    If days.Rows.Count <> 1 Then
        DaysOff = "#ERROR! Select only 1 row for days of the week."
        Exit Function
    End If
    
    If days.Columns.Count <> sel.Columns.Count Then
        DaysOff = "#ERROR! Select the same number of columns for days and data."
        Exit Function
    End If
    
    d = days.Value
    s = sel.Value
    
    For i = 1 To sel.Columns.Count
        If s(1, i) = "OFF" Then
            If DaysOff = "" Then
                DaysOff = Mid(d(1, i), 1, 3)
            Else
                DaysOff = DaysOff & ", " & Mid(d(1, i), 1, 3)
            End If
        End If
    Next i
    
End Function

I am looking into the "No of Shift (Output)" next.
 
Upvote 0
If you are interested in non VBA solution, please check the below suggestions:

Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1SundayMondayTuesdayWednesdayThursdayFridaySaturdayUsing Helper CellsHelper CellsUsing Filter Formula (Office 365)
2IDName11-Jul-2112-Jul-2113-Jul-2114-Jul-2115-Jul-2116-Jul-2117-Jul-21No of Shift (Output)Week Off (Output)
31aOFF1500-21001500-21001500-21001500-21001500-21001200-21001500-2100, 1500-2100, 1500-2100, 1500-2100, 1500-2100, 1200-2100,Sunday,1500-21001500-21001500-21001500-21001500-21001200-2100 Sunday      1500-2100, 1500-2100, 1500-2100, 1500-2100, 1500-2100, 1200-2100Sunday
42bOFFOFFOFFOFFOFFOFFOFF,Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday       SundayMondayTuesdayWednesdayThursdayFridaySaturday Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
53cOFF0800-18300800-18300800-18300800-1830OFFOFF0800-1830, 0800-1830, 0800-1830, 0800-1830,Sunday, Friday, Saturday,0800-18300800-18300800-18300800-1830   SundayFridaySaturday    0800-1830, 0800-1830, 0800-1830, 0800-1830Sunday, Friday, Saturday
64d0700-15300700-1530OFFOFF0700-15300700-15300700-15300700-1530, 0700-1530, 0700-1530, 0700-1530, 0700-1530,Tuesday, Wednesday,0700-15300700-15300700-15300700-15300700-1530  TuesdayWednesday     0700-1530, 0700-1530, 0700-1530, 0700-1530, 0700-1530Tuesday, Wednesday
75e0700-11001030-1430OFFOFFOFF1030-14300700-11000700-1100, 1030-1430, 1030-1430, 0700-1100,Tuesday, Wednesday, Thursday,0700-11001030-14301030-14300700-1100   TuesdayWednesdayThursday    0700-1100, 1030-1430, 1030-1430, 0700-1100Tuesday, Wednesday, Thursday
86fOFF0900-17000900-17000900-17000900-17000900-1700OFF0900-1700, 0900-1700, 0900-1700, 0900-1700, 0900-1700,Sunday, Saturday,0900-17000900-17000900-17000900-17000900-1700  SundaySaturday     0900-1700, 0900-1700, 0900-1700, 0900-1700, 0900-1700Sunday, Saturday
97g1630-0100OFFOFF1630-01001630-0100No longer employed1630-01001630-0100, 1630-0100, 1630-0100, 1630-0100,Monday, Tuesday,1630-01001630-01001630-01001630-0100   MondayTuesday     1630-0100, 1630-0100, 1630-0100, 1630-0100Monday, Tuesday
108h1000-18301000-18301000-1830OFFOFF1000-1830Suspended1000-1830, 1000-1830, 1000-1830, 1000-1830,Wednesday, Thursday,1000-18301000-18301000-18301000-1830   WednesdayThursday     1000-1830, 1000-1830, 1000-1830, 1000-1830Wednesday, Thursday
1113mOFFLeave of absenceLeave of absenceLeave of absenceLeave of absenceLeave of absenceOFF,Sunday, Saturday,       SundaySaturday      Sunday, Saturday
Sheet1
Cell Formulas
RangeFormula
J3:J11J3=TRIM(SUBSTITUTE(M3&", "&N3&", "&O3&", "&P3&", "&Q3&", "&R3&", "&S3," ,",""))
K3:K11K3=TRIM(SUBSTITUTE(T3&", "&U3&", "&V3&", "&W3&", "&X3&", "&Y3&", "&Z3," ,",""))
M3:S11M3=IFERROR(INDEX($C3:$I3,AGGREGATE(15,3,($C3:$I3<>"OFF")/($C3:$I3<>"OFF")*($C3:$I3<>"No longer employed")/($C3:$I3<>"No longer employed")*($C3:$I3<>"Suspended")/($C3:$I3<>"Suspended")*($C3:$I3<>"Leave of absence")/($C3:$I3<>"Leave of absence")*(COLUMN($C3:$I3)-COLUMN($B3)),COLUMNS($C3:C3))),"")
T3:Z11T3=IFERROR(INDEX($C$1:$I$1,AGGREGATE(15,3,($C3:$I3="OFF")/($C3:$I3="OFF")*(COLUMN($C3:$I3)-COLUMN($B3)),COLUMNS($C3:C3))),"")
AB3:AB11AB3=TEXTJOIN(", ",TRUE,FILTER(C3:I3,($C3:$I3<>"OFF")*($C3:$I3<>"No longer employed")*($C3:$I3<>"Suspended")*($C3:$I3<>"Leave of absence"),""))
AC3:AC11AC3=TEXTJOIN(", ",TRUE,FILTER($C$1:$I$1,(C3:I3="OFF"),""))


It would be much easier with the new array formulas in MS Office 365 (no need for helper cells)

Let me know

Regards
M. Yusuf
 
Upvote 0
It should display only 1 shift time
in the 1 record, there are 2 shift times, and so need to display the max one that is 1500-2100 (as this count is more)
In the 5 record, there are 2 shift rimes and both the counts are the same so it will be 0700-1100 : 1030-1430 (as both the count are same 2)

I hope I am able to explain.

Crystalyzer said:
I'm not clear on the logic for the below requirement based on your sample data. Can you elaborate?
Click to expand...
 
Upvote 0
mamady said:
If you are interested in non VBA solution, please check the below suggestions:

Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1SundayMondayTuesdayWednesdayThursdayFridaySaturdayUsing Helper CellsHelper CellsUsing Filter Formula (Office 365)
2IDName11-Jul-2112-Jul-2113-Jul-2114-Jul-2115-Jul-2116-Jul-2117-Jul-21No of Shift (Output)Week Off (Output)
31aOFF1500-21001500-21001500-21001500-21001500-21001200-21001500-2100, 1500-2100, 1500-2100, 1500-2100, 1500-2100, 1200-2100,Sunday,1500-21001500-21001500-21001500-21001500-21001200-2100 Sunday      1500-2100, 1500-2100, 1500-2100, 1500-2100, 1500-2100, 1200-2100Sunday
42bOFFOFFOFFOFFOFFOFFOFF,Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday       SundayMondayTuesdayWednesdayThursdayFridaySaturday Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
53cOFF0800-18300800-18300800-18300800-1830OFFOFF0800-1830, 0800-1830, 0800-1830, 0800-1830,Sunday, Friday, Saturday,0800-18300800-18300800-18300800-1830   SundayFridaySaturday    0800-1830, 0800-1830, 0800-1830, 0800-1830Sunday, Friday, Saturday
64d0700-15300700-1530OFFOFF0700-15300700-15300700-15300700-1530, 0700-1530, 0700-1530, 0700-1530, 0700-1530,Tuesday, Wednesday,0700-15300700-15300700-15300700-15300700-1530  TuesdayWednesday     0700-1530, 0700-1530, 0700-1530, 0700-1530, 0700-1530Tuesday, Wednesday
75e0700-11001030-1430OFFOFFOFF1030-14300700-11000700-1100, 1030-1430, 1030-1430, 0700-1100,Tuesday, Wednesday, Thursday,0700-11001030-14301030-14300700-1100   TuesdayWednesdayThursday    0700-1100, 1030-1430, 1030-1430, 0700-1100Tuesday, Wednesday, Thursday
86fOFF0900-17000900-17000900-17000900-17000900-1700OFF0900-1700, 0900-1700, 0900-1700, 0900-1700, 0900-1700,Sunday, Saturday,0900-17000900-17000900-17000900-17000900-1700  SundaySaturday     0900-1700, 0900-1700, 0900-1700, 0900-1700, 0900-1700Sunday, Saturday
97g1630-0100OFFOFF1630-01001630-0100No longer employed1630-01001630-0100, 1630-0100, 1630-0100, 1630-0100,Monday, Tuesday,1630-01001630-01001630-01001630-0100   MondayTuesday     1630-0100, 1630-0100, 1630-0100, 1630-0100Monday, Tuesday
108h1000-18301000-18301000-1830OFFOFF1000-1830Suspended1000-1830, 1000-1830, 1000-1830, 1000-1830,Wednesday, Thursday,1000-18301000-18301000-18301000-1830   WednesdayThursday     1000-1830, 1000-1830, 1000-1830, 1000-1830Wednesday, Thursday
1113mOFFLeave of absenceLeave of absenceLeave of absenceLeave of absenceLeave of absenceOFF,Sunday, Saturday,       SundaySaturday      Sunday, Saturday
Sheet1
Cell Formulas
RangeFormula
J3:J11J3=TRIM(SUBSTITUTE(M3&", "&N3&", "&O3&", "&P3&", "&Q3&", "&R3&", "&S3," ,",""))
K3:K11K3=TRIM(SUBSTITUTE(T3&", "&U3&", "&V3&", "&W3&", "&X3&", "&Y3&", "&Z3," ,",""))
M3:S11M3=IFERROR(INDEX($C3:$I3,AGGREGATE(15,3,($C3:$I3<>"OFF")/($C3:$I3<>"OFF")*($C3:$I3<>"No longer employed")/($C3:$I3<>"No longer employed")*($C3:$I3<>"Suspended")/($C3:$I3<>"Suspended")*($C3:$I3<>"Leave of absence")/($C3:$I3<>"Leave of absence")*(COLUMN($C3:$I3)-COLUMN($B3)),COLUMNS($C3:C3))),"")
T3:Z11T3=IFERROR(INDEX($C$1:$I$1,AGGREGATE(15,3,($C3:$I3="OFF")/($C3:$I3="OFF")*(COLUMN($C3:$I3)-COLUMN($B3)),COLUMNS($C3:C3))),"")
AB3:AB11AB3=TEXTJOIN(", ",TRUE,FILTER(C3:I3,($C3:$I3<>"OFF")*($C3:$I3<>"No longer employed")*($C3:$I3<>"Suspended")*($C3:$I3<>"Leave of absence"),""))
AC3:AC11AC3=TEXTJOIN(", ",TRUE,FILTER($C$1:$I$1,(C3:I3="OFF"),""))


It would be much easier with the new array formulas in MS Office 365 (no need for helper cells)

Let me know

Regards
M. Yusuf
Click to expand...
Hi Yusuf,
I have tried this function and it is perfect (done some modifications)

Just one change is required, if you can check and modify (my original thread ref Id no 5.) 2 times should be displayed.

Thanks.
Excel Formula: 
=IFERROR(INDEX($D3:$J3,AGGREGATE(15,3,($D3:$J3<>"OFF")/($D3:$J3<>"OFF")*($D3:$J3<>"No longer employed")/($D3:$J3<>"No longer employed")*($D3:$J3<>"Suspended")/($D3:$J3<>"Suspended")*($D3:$J3<>"Leave of absence")/($D3:$J3<>"Leave of absence")*($D3:$J3<>"ABS - COVID19")/($D3:$J3<>"ABS - COVID19")*($D3:$J3<>"SUTO")/($D3:$J3<>"SUTO")*($D3:$J3<>"Unpaid FMLA")/($D3:$J3<>"Unpaid FMLA")*(COLUMN($D3:$J3)-COLUMN($C3)),COLUMNS($C3:C3))),"")
 
Upvote 0
vmjan02 said:
It should display only 1 shift time
in the 1 record, there are 2 shift times, and so need to display the max one that is 1500-2100 (as this count is more)
In the 5 record, there are 2 shift rimes and both the counts are the same so it will be 0700-1100 : 1030-1430 (as both the count are same 2)

I hope I am able to explain.
Click to expand...
@Crystalyzer

Any luck or update
thanks
 
Upvote 0
No of Shifts Function

Steps
  1. Copy the code below into a module in a copy of your workbook to test.
  2. Enter "=MaxShifts(C2:I2)" into the "No of Shift (Output)" column
    1. Where C2:I2 is the row you are reviewing
  3. Then copy the formula down for as many rows as you want to check

VBA Code: 
Function MaxShifts(sel As Range) As String
    Dim arr As New Collection, a
    Dim s As Variant
    Dim d As Variant
    Dim i As Integer
    Dim j As Integer
    Dim tmpMax As Long
    
    
    If sel.Rows.Count <> 1 Then
        MaxShifts = "#ERROR! Select only 1 row of data."
        Exit Function
    End If
    
    s = sel.Value
    
    On Error Resume Next
    For Each a In s
        If a <> "OFF" Then
            arr.Add a, a
        End If
    Next
    On Error GoTo 0
    
    Select Case arr.Count
    Case Is = 1
        MaxShifts = arr(1)
    Case Is > 1
        ReDim d(1, arr.Count - 1)
        For i = 0 To arr.Count - 1
            d(0, i) = arr(i + 1)
            d(1, i) = WorksheetFunction.CountIf(sel, "=" & arr(i + 1))
        Next i
        
        For j = LBound(d, 1) To UBound(d, 1)
            If d(1, j) > tmpMax Then tmpMax = d(1, j)
        Next j
        
        For j = LBound(d, 1) To UBound(d, 1)
            If MaxShifts = "" Then
                If d(1, j) = tmpMax Then MaxShifts = d(0, j)
            Else
                If d(1, j) = tmpMax Then MaxShifts = MaxShifts & " : " & d(0, j)
            End If
        Next j
    Case Else
        MaxShifts = ""
    End Select
    
    If InStr(1, MaxShifts, "-", vbTextCompare) = 0 Then MaxShifts = ""
    
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Macro for copying data from multiple sheets to a common sheet based on a cell value
Replies
0
Views
220
jmk15315
J
J
  • Question
FIND AND REPLACE
Replies
1
Views
198
Muhammad_Usman
Muhammad_Usman
M
  • Question
Count occurrences - Backwards compatible solutions
Replies
3
Views
223
morrowj04
M
J
  • Solved
Formula Filled Dates
Replies
4
Views
337
Fluff
Fluff
A
  • Question
Help Needed Please Formula
Replies
5
Views
305
KRice
K

Forum statistics

Threads
1,215,143
Messages
6,123,287
Members
449,094
Latest member
GoToLeep

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