-1

I have an Excel sheet where values are in the column A and B. Column A has duplicate values. I need to find the smallest value in column B for a given value in column A, and copy that value into a new column. How can I do this?

Example

Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
jimbo
  • 1
  • 1
  • Possible duplicate of [Populate unique values into a VBA array from Excel](http://stackoverflow.com/questions/5890257/populate-unique-values-into-a-vba-array-from-excel) among many others. – Zev Spitz Nov 30 '15 at 12:10

1 Answers1

0

If you want to do this in VBA, you could use a Scripting.Dictionary, with each iteration checking if the new value is less than the previous value for a given key.

Then, since the keys and values are exposed on the Dictionary as arrays, you can simply write the keys/values to a destination range.

Alternatively, you could issue the following SQL statement against the Excel worksheet:

SELECT Field1, MIN(Field2)
FROM [$Sheet1]
GROUP BY Field1

Presumably you would do this via an ADO or DAO Recordset, which you can paste into the destination range using the CopyFromRecordset method.

Zev Spitz
  • 13,950
  • 6
  • 64
  • 136