[Min Value lookup across rows and columns in Excel]

[anuradhagrewal]

Hi
I have a problem in finding the MINIMUM hrs worked by an employee week on week. For eg Employee Name "Kishore" appears in A2,A35,J34,G72 but what I need is in the output column the lowest hours Kishore has worked.
This is a repetitive process that has to be done week on week and with lots of data so using the Pivot table option is unfeasible.
The link of the data file is with the Data worksheet and the desired output worksheet here

What I was looking is MIN function based output.

Please guide.

Thanks

Regards

Anuradha

 

[BarryL]

Hello, is VBA an option?

I have tested the below an i think it might suit your requirements.

Sub find_and_send()
Dim lr As Long, rng As Range, r As Range, sh As Worksheet, ws As Worksheet, cell As Range
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Set sh = ThisWorkbook.Sheets("Data")
Set ws = ThisWorkbook.Sheets("Output Desired")
 
On Error GoTo errhandler
 
lr = sh.Cells.Find(What:="*", _
                    after:=sh.Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    
For Each cell In ws.Range("A2:A" & ws.Range("A" & Rows.Count).End(xlUp).Row)


For Each rng In sh.Range("A2:L" & lr)
    If rng.Value = cell.Value Then
        dict.Add Cells(1, rng.Column + 1), rng.Offset(0, 1).Value
    End If
Next

Min = Application.Min(dict.items)

cell.Offset(0, 1).Value = Min

dict.RemoveAll

nextcell:
    Next cell
    
Exit Sub

errhandler:
    cell.Offset(0, 1).Value = "No Value"
    Resume nextcell

End Sub

 

[anuradhagrewal]

Thanks Barry you are an life saver?...The code works..

I had a couple of queries regarding the VB script.
1)If I have data which is beyond column L to say column X then all I need to do is increase the range....plz advice

2)Please consider my foll query :If I need to look for min value in worksheet "data" in specific columns then how can we modify this code. For eg each corresponding to each week there is Avg Hrs Worked. In some new employees the week in which they have joined will have the same avg hrs worked Or in case of employees who are absent for a long duration there avg hrs worked will come across as min value as the value in the corresponding week will be blank.
Hence if it is possible can the VB script look up only in the specific column titled "wk(number)".

That is my concern

But in any case THANKS A TON BARRY