CODEWITHSUNDEEP
excelvba
Shivster
Shivster

Reputation: 83

Excel- finding min value of multiple items

Column A represents serial numbers of different parts, but these parts come from different suppliers so they have different prices. Column B holds the price for each part.

I need a macro that will essentially go down Column A, compare the column B price for duplicates, and return the min price for the part in column C.

Upvotes: 0

Views: 2490

Answers (2)

user667489
user667489

Reputation: 9569

Alternatively, you could:

  1. Create a pivot table, selecting columns a and b as your source
  2. Set serial number as a row field (drag it to the left of the table)
  3. Set price as a data field (drag it to the middle)
  4. Choose to display min of price rather than sum (the default)

Working out the formula was much more entertaining, though!

Upvotes: 1

user667489
user667489

Reputation: 9569

You can do this without even needing array formulas:

=SUMPRODUCT(($B$1:$B$5)*($B$1:$B$5=MIN($B$1:$B$5+(A1<>$A$1:$A$5)*MAX($B1:$B5)))) /SUMPRODUCT(1*($B$1:B$5=MIN($B$1:$B$5+(A1<>$A$1:$A$5)*MAX($B1:$B5))))

The important insight here is that you can do array operations inside a SUMPRODUCT, and inside functions inside a SUMPRODUCT. The first SUMPRODUCT gives the sum of all the prices that are equal to the minimum price for each component, and the second one gives the number of listings that have that price. Divide one by the other and you have the minimum price for each component!

First SUMPRODUCT:

  • ($B$1:$B$5) is a column vector of your prices.
  • We're then creating another column vector whose entries will be coerced to 1 if the corresponding entry in $B$1:$B$5 is equal to MIN($B$1:$B$5+(A1<>$A$1:$A$5)*MAX($B$1:$B$5)) and zero otherwise.
  • The part inside the MIN function starts out as the original vector of prices, but if the serial number from each row of column a is not the same as the one in the current row, we add on the largest price from anywhere in column B to the current row in column B.
  • This prevents any parts with different serial numbers from being counted as having the minimum price while leaving all prices for the current part unchanged.
  • This means we've constructed a column vector where all the entries are either zero or equal to the lowest price for the component in the row of the cell in which the formula is entered.

The second SUMPRODUCT is similar to the first, except this time we just add up the number of times the condition is met without multiplying by the price.

Upvotes: 0

Related Questions