Get minimum value from table using criteria

[Zenru]

Hello,

I have a table which holds some important information, the columns I need to work with are:

Parent
Size
Units

Parent	Size	Units
HOLLOWCO	AB, AS2, AR3, AT3	8717
HOLLOWCO	AS2, AR3	875
HOLLOWCO	AT3	8784
NUDIS	AT3	45

<tbody>
</tbody>

I need a macro that will search in Parent and Size and pull the lowest value found in units. Notice Size has multiple "sizes" concatenated by a comma, I need to be able to individually search each size until a match is found there as well.

The data by which I will do a search goes like this:

ParentC,
SizeC (one size only)
UnitsC (the lowest value in Units)

ParentC	SizeC	UnitsC
HOLLOWCO	AT3	8717
HOLLOWCO	AS2	875
HOLLOWCO	AR3	875
NUDIS	AT3	45

<tbody>
</tbody>

There are some UDF that gives the MINIF and Excel array formula can do the same, but I cant find a way to individually search each size in the first column and find a match.

I am extremely confused in this, if anyone has any idea as to how to deal with this please feel free to share.

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	Parent	Size	Units			ParentC	SizeC	UnitsC
2​	HOLLOWCO	AB, AS2, AR3, AT3	8717​			HOLLOWCO	AT3	8717​
3​	HOLLOWCO	AS2, AR3	875​			HOLLOWCO	AS2	875​
4​	HOLLOWCO	AT3	8784​			HOLLOWCO	AR3	875​
5​	NUDIS	AT3	45​			NUDIS	AT3	45​

In H2 control+shift+enter, not just enter, and copy down:

=MIN(IF($A$2:$A$5=$F2,IF(ISNUMBER(SEARCH($G2&",",$B$2:$B$5&",")),$C$2:$C$5)))

 

[Aladin Akyurek]

By the way, if MINIFS is available on your system...

In H2 just enter and copy down:

=MINIFS($C$2:$C$5,$A$2:$A$5,$F2,$B$2:$B$5,"*"&$G2&"*")

 

[Zenru]

Sadly, MINIF is not allowed. Now, if there is a VBA way to do this I would prefer it. I have bad experiences with performance using arrays. Still, your array looks simple enough so it shouldn’t cause any problems. Thanks!

 

[Peter_SSs]

.. if there is a VBA way to do this I would prefer it.

If the layout was like this

Excel Workbook
	A	B	C	D	E	F	G	H
1	Parent	Size	Units			ParentC	SizeC	UnitsC
2	HOLLOWCO	AB, AS2, AR3, AT3	8717			HOLLOWCO	AT3
3	HOLLOWCO	AS2, AR3	875			HOLLOWCO	AS2
4	HOLLOWCO	AT3	8784			HOLLOWCO	AR3
5	NUDIS	AT3	45			NUDIS	AT3
Min Value

Then try

Sub MinValue()
  Dim d As Object
  Dim a As Variant, b As Variant, e As Variant
  Dim i As Long
  
  a = Range("A1").CurrentRegion.Value
  b = Range("F1").CurrentRegion.Value
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  For i = 2 To UBound(a)
    For Each e In Split(a(i, 2), ", ")
      If d.exists(a(i, 1) & "|" & e) Then
        If a(i, 3) < d(a(i, 1) & "|" & e) Then d(a(i, 1) & "|" & e) = a(i, 3)
      Else
        d(a(i, 1) & "|" & e) = a(i, 3)
      End If
    Next e
  Next i
  For i = 2 To UBound(b)
    If d.exists(b(i, 1) & "|" & b(i, 2)) Then
      b(i, 3) = d(b(i, 1) & "|" & b(i, 2))
    Else
      b(i, 3) = "N/A"
    End If
  Next i
  Range("F1").Resize(UBound(b), UBound(b, 2)).Value = b
End Sub

Result for me:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Parent	Size	Units			ParentC	SizeC	UnitsC
2	HOLLOWCO	AB, AS2, AR3, AT3	8717			HOLLOWCO	AT3	8717
3	HOLLOWCO	AS2, AR3	875			HOLLOWCO	AS2	875
4	HOLLOWCO	AT3	8784			HOLLOWCO	AR3	875
5	NUDIS	AT3	45			NUDIS	AT3	45
Min Value

 

[Aladin Akyurek]

Sadly, MINIF is not allowed. Now, if there is a VBA way to do this I would prefer it. I have bad experiences with performance using arrays. Still, your array looks simple enough so it shouldn’t cause any problems. Thanks!

You are welcome. Yes, it's simple.

 

[Peter_SSs]

Yes, it's simple.

I agree that a formula is simpler & only provided macro since the OP asked for it. However, your formula may be a little too simple and need a small amount of extra complexity by adding some more 'boundaries' around the values. I don't know if it would be possible with the OP's data but if cell B2 is altered to "AB, AS2, AR3, VAT3", your formulas still reports 8717 in H2 when I presume it should then be 8784.

 

[Zenru]

Thank you, Peter! I used your code but I modified it a bit and it worked perfectly!

Now this part:

Range("F1").Resize(UBound(b), UBound(b, 2)).Value = b

Really blew up my mind and I am still not sure how that works. I am sure I will figure it out myself in a while.

Thanks again, Aladin, your formula suits my needs at the moments but like Peter, I am not confident enough to say I know all possible Size data. That is why I wanted a macro.

 

[Aladin Akyurek]

[...]
Thanks again, Aladin, your formula suits my needs at the moments but like Peter, I am not confident enough to say I know all possible Size data. That is why I wanted a macro.

Understandable. Thanks for the update.

 

[Peter_SSs]

Thank you, Peter! I used your code but I modified it a bit and it worked perfectly!

You're welcome. Glad you could modify to suit your needs.

Range("F1").Resize(UBound(b), UBound(b, 2)).Value = b

Really blew up my mind and I am still not sure how that works. I am sure I will figure it out myself in a while.

Best to figure it out yourself, but if you don't, & want further explanation, post back.


BTW, a warning: My code relies on the column B values being delimited by a comma and a space like your samples, even though your original description only mentioned a comma. If there is any variation in the format of column B, the code may need some more tweaking.