CODEWITHSUNDEEP
excelvba
Mar Vr
Mar Vr

Reputation: 11

VBA: Dynamic arrays when you meet certain criteria

Here is my problem.

I have data like this in my excel

Manufactuer     Model Capacity

x                   1000                    
x                   3000   
y                   1800                 
y                   4000 
z                   1300

I want to loop through these data and when the manufactuer is the same I want to get the minimum capacity and copy it to another sheet. The problem is that I don't know the exact amount of the models of each manufactuer. For example, one time the X manufactuer can be found 5 times and another time can be found 4.

Upvotes: 1

Views: 73

Answers (2)

Pspl
Pspl

Reputation: 1474

If your Manufacturer column is A:A and your Model Capacity column is B:B then the formula

{=MIN(IF(A:A="y";B:B))}

will give you the minimum value of y Manufacturer (1800).

Change the letter y to change Manufacturer and press Ctrl+Shift+Enter to place the braces.

Upvotes: 3

user4039065
user4039065

Reputation:

Try this after modifying to suit your own ranges and worksheet names.

sub minCapacities()

    dim i as long, arr as variant, dict as object

    set dict = createobject("scripting.dictionary")
    dict.comparemode = vbtextcompare

    with worksheets("sheet1")
        arr = .range(.cells(2, "A"), .cells(.rows.count, "B").end(xlup)).value2
    end with

    for i=lbound(arr, 1) to ubound(arr, 1)
        if dict.exists(arr(i, 1)) then
            dict.item(arr(i, 1)) = application.min(dict.item(arr(i, 1)), arr(i, 2))
        else
            dict.item(arr(i, 1)) = arr(i, 2)
        end if
    next i

    with worksheets("sheet2")
        .cells(1, "A").resize(1, 2) = array("Manufactuer", "Min Capacity")
        .cells(2, "A").resize(dict.count, 1) = application.transpose(dict.keys)
        .cells(2, "B").resize(dict.count, 1) = application.transpose(dict.items)
    end with

end sub

Upvotes: 1

Related Questions