look up the max value in column 2 for a unique values in column 1

I have two columns with Names in column one and in column two are numbers. The names in Column 1 can be repeated. I want to find the highest number in column 2 corresponding to each unique name in column 1 and the lowest number in column 2 corresponding to the unique name in column 1.

Thanks for your help
Vivek

See my attached sheet for two different methods of accomplishing this.

Method 1 used Advanced Filter to list just the unique names from the list in column A, then used MAX and MIN array functions (applied with Ctrl+Shift+Enter) to return the highest and lowest values associated with each unique name in the list. Only problem with Advanced Filter is it is not dynamic by default. If the original list changes, the unique list won't change until you run the Advanced Filter>Unique Values again. This can be overcome in VBA, but adds more to the task.

Method 2 used 2 array (Ctrl+Shift+Enter) formulas:
In cell H2:

Formula:

=INDEX($A$1:$A$1000,MATCH(0,COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0))

In cell H3 and filled down:

Formula:

=IF(COUNTIF($A$1:$A$1000,">"&H2),INDEX($A$1:$A$1000,MATCH(COUNTIF($A$1:$A$1000,"<="&H2),COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0)),"")

Then used the same MAX and MIN array formulas as in Method 1.

Hope it helps! Let me know if you have any questions.

- Moo

Thanks for your prompt reply.

The first method works fine with method 2 your excel sheet gives the unique names list that does not correspond to the max min in the same row is there a way to get the max min in method 2 to correspond to the unique name in column H?.

Thanks in advance.
Vivek

OOPS! Yes... for the MAX and MIN formulas in columns I and J, change the reference from D2 to H2 (see example below)

Originally in I2: =MAX(IF($A$2:$A$15=$D2,$B$2:$B$15))
Change to: =MAX(IF($A$2:$A$15=$H2,$B$2:$B$15))

Originally in J2: =MIN(IF($A$2:$A$15=$D2,$B$2:$B$15))
Change to: =MIN(IF($A$2:$A$15=$H2,$B$2:$B$15))

Hope that helps.

- Moo

Doing that gives 0 in both max and min columns for all cells in method 2

Pivot table. See the attached

Pivot table is another good idea, but like Advanced Filter, does not auto update without a little VBA help. Using Teethless Mama's sheet, with the PT, I added the following code to Sheet1's code page:

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Column = 1) Or (Target.Column = 2) Then
ActiveSheet.PivotTables(1).RefreshTable
End If

End Sub

With that code added, anytime a cell is changed in column A or B, the pivot table will refresh.

- Moo

I am just so new to this and pardon me for my ignorance but how do you add this code is there a website for beginners if this is too trivial a question that you could point me to. Thanks

If you are using Teethless Mama's file, follow these steps:

- Right-click on the tab name that says "Sheet1" and select 'View Code'
- Copy my code from above and paste it into the open area on the right of the code window
- Press Ctrl+S to save the file. A message box will pop up saying "The following features cannot be saved in macro-free workbooks..."
- Click the 'NO' button. A Save As dialog box will appear.
- Give the file a new name, such as Max and Min - VBA
- Click next to 'Save as Type' and select 'Excel Macro-Enabled Workbook
- Choose where you want to save it
- Click Save

You can now close out the VBA window. Sheet1 will now refresh anytime a cell is changed in column A or B.

- Moo