Find minimum for all values with same identifier within an array

**dhs** · 09-13-2011, 07:15 PM

I am trying to determine (and label) the minimum value for all points within in array that have the same identifier. As an example, If column A is the identifier (in my case a point number) and column N is the variable (in my case a minimum distance between two points) e.g.

A .......N
7000, 0.22
7000, 0.10
7000,. 0.04
7010, 0.34
7010, 0.25
7010, 0.06
7010, 0.12
7020, 0.03
7020, 0.02

I would like to run a macro that gives me this (label in new column O):

A.......N..........O
7000, 0.22
7000, 0.10
7000, 0.04, minimum
7010, 0.34
7010, 0.25
7010, 0.06, minimum
7010, 0.12
7020, 0.03
7020, 0.02, minimum

This would allow me to easily filter the minimum column and average other values I am interested in.

Thanks in advance for any help. I can post sample file if need be.

Long time appreciator, first time poster.

**JBeaucaire** · 09-13-2011, 08:04 PM

Only adjust the range of this formula as much as needed, it is an array formula and is very calc-intense, too wide a range or too many of these cells and you'll turn your workbook into a doorstop.

Put this formula in O2, then confirm it by pressing CTRL-SHIFT-ENTER:

=IF($N2=MIN(IF($A$2:$A$100=$A2, $N$2:$N$100)), "minimum", "")

...then copy that cell down through O100.

**dhs** · 09-13-2011, 09:06 PM

Thanks for your help JBeaucaire,

When I use your formula I seem to only get one minimum label at the minimum value from the entire range between N2 and N100 rather than a minimum value for each A column identifier i.e. One minimum label for all points with ID 7000, one minimum label for all points with ID 7010 etc.

Please also note that the number of points with the same ID will vary and in my current case I have approximately 400 unique ID's (column A) with each ID corresponding to anywhere between 0-15 values (column N).

I have attached a sample of an Excel file I am looking at. I have added comments in green. The red values are the Point identifiers I want to use to sort. The blue values are the variables i want to find the minimum for. And the thin black lines provide an example (ID 7200, 7210, 7220, 7230) of the groups I want to find a minimum N column value for.

Hope this clarifies and as I mentioned, appreciate all help.

DHS

**JBeaucaire** · 09-13-2011, 10:36 PM

the formula I gave will provide a "minimum" flag for each value in column A if you enter it with CTRL-SHIFT-ENTER to activate the array. Else it will not. You'll know the array is active when you see Excel has put braces around your formula. { }

In your new sheet, I don't see where you put in the formula so I can't comment on your attempt.

**JBeaucaire** · 09-13-2011, 10:43 PM

The formula for O18 in your new sheet is the exact same formula, just range adjusted.

=IF($N18=MIN(IF($A$18:$A$2258=$A18, $N$18:$N$2258)), "minimum", "")
...confirmed with CTRL-SHIFT-ENTER, then copied down.

Unfortunately, that's 2,240 calculations per cell, times 2240 cells, so that's 5 million calculations that have to update anytime you make a change in column A or column N, takes about 12 seconds on my system.

I would think you would need to enter this formula, copy it down, let it update, then highlight all these cells, do a COPY > EDIT > PASTE SPECIAL > VALUES to remove the formulas and leave the results behind.

You can do that anytime you need to update the column O values. Maybe leave the original formula in O18 so you can just copy down to redo it in the future.