CODEWITHSUNDEEP
vbaexcel
joes
joes

Reputation: 31

Find multiple matching values in column 1, then get their MIN corresponding values from column 3

I am trying to find values that occur more than once in Column 1, then get their MIN corresponding values from column 3. So for example if column 1 has 3 matching values of 124L, and their corresponding values in column 3 is 120, 90 and 60, then overwrite column 3 of the matching values with the 60. So at the end of the day all my 124L should have 60 in Column 3.

I would appreciate any help or direction, am currently stumped especially on the searching for multiple matching values. Thank you. Below is the code which am ashamed to even post...

  Sub minFinder()

  'declare variables
   Dim minCartMx As Long, multiLocsCartMxFound As Long
   Dim searchMultiLocs As Range, foundMultiLocs As Range
   Dim FinalRow As Long
   FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To FinalRow
  'search multiple occurances of values in column 1
   Set searchMultiLocs = Cells(i, 1)
   For Each searchMultiLocs In searchMultiLocs

  'I dont think this works but am trying to get values of corresponding  
   'to foundMultiLocs in column 3 (cells(i,3)

  Set multiLocsCartMxFound = searchMultiLocs.Find(what:=searchMultiLocs, 
  LookIn:=xlWhole, _ searchorder:=xlByRows, searchDirection:=xlNext, _ 
  MatchCase:=False, searchformat:=False)


  'determine the minumum value of column 3 of the foundMultiLocs
  minCartMx = Application.WorksheetFunction.Min(multiLocsCartMxFound)

' overwrite the column 3 of the matching values with MIN
  minCartMx = Cells(i, 3)


 End Sub

enter code here

Upvotes: 0

Views: 90

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

In Vba:

Sub minFinder()
Dim ws As Worksheet
Dim rng As Range
Dim minRng As Range
Dim cel As Range

Set ws = Sheets("Sheet5") 'Change to your sheet

With ws
    Set rng = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
    Set minRng = .Range(.Cells(2, 3), .Cells(.Rows.Count, 3).End(xlUp))
End With
For Each cel In rng
    cel.Offset(, 2).Value = ws.Evaluate("=MIN(IF(" & cel.Address(0, 0) & "=" & rng.Address(0, 0) & "," & minRng.Address(0, 0) & "))")
Next cel


End Sub

But you could easily do this with the formula from the code only.

in an empty column put:

=MIN(IF(A2=$A$2:$A$7,$C$2:$C$7))

This is an array formula and must be confirmed with ctrl-Shift-Enter.

enter image description here

Then it is just a matter of copying and pasting the values.

Upvotes: 1

Kyle
Kyle

Reputation: 564

Find only returns a single cell. Use Evaluate instead to find the minimum value. This example returns the minimum value from column C where column A is "value".

minValue = Application.Evaluate("min(if(A1:A10 = ""value"",  C1:C10, """"))")

Also, on the last line of your code, your operands are flipped. You meant to write Cells(i,3) = minCartmx

Upvotes: 1

Related Questions