Create Table From Another Table

[carl]

My Data table looks like this:

Product Rep Price1 Price2 Diff
A Jim 30 42 12
A Bob 35 42 7
A Stan 40 42 2
A Bill 45 42 3
A Ed 50 42 8
B Jim 15 28 13
B Bob 20 28 8
B Stan 25 28 3
B Bill 30 28 2
B Ed 35 28 7
C Jim 1 7.5 6.5
C Bob 2 7.5 5.5
C Stan 3 7.5 4.5
C Bill 4 7.5 3.5
C Ed 5 7.5 2.5


I am trying to create this table:

Product Rep
A Stan
B Bill
C Ed

This new table lists each Product, and identifies the Rep that has the
smallest "Diff". If there is a tie, it is not that important which rep is
listed but I only need one listed. The actual data table has on average
40,000 rows - if that makes a difference on the approach for a solution.

Is it possible to do this with VBA and/or excel formulas ?

Thank you in advance.

 

[Ron Coderre]

Try something like this:

With
A1:E16 contains your posted data list

Then
I1: Prod
I2: A
I3: B
I4: C

J1: Rep

Put this ARRAY FORMULA* in
J2:
=INDEX($B$1:$B$16,SUM(($A$2:$A$16=I2)*($E$2:$E$16=MIN(IF($A$2:$A$16=I2,$E$2:$E$16)))*ROW($E$2:$E$16)))

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy J2 and paste into J3:J4

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Lori]

UI method: Choose Data > Sort by Diff Ascending then select the product
column and choose Data > Filter Advanced Filter > Unique Records Only.

You could then copy this to a new sheet - work on a copy of the data if
you are afraid of changing the order.