created table of Refs and lookup maximum for repeated references.

excelnovicekid

New Member
Joined
Jul 17, 2013
Messages
4
Hi everyone.

I've been having a few serious problems with excel and given I'm rather new to it all I'm really unsure how to figure things out.

I've been searching the internet for a way to solve my problem and everything I have tried hasn't worked.

I'm unsure which combination of match, index, lookup and max I need to solve this problem.

I want to look up and return the corresponding maximum value from the 6th (Orange) column. for the 1st column. The first colum has many repeated values.

I want to create a table corresponding maximum values; given I'm dealing with a few thousand rows I need to do this automatically.

Later I will use this to create a grid and 3d plot of x against y with Y as the z co-ordinate.

Ref B R G X Y Z x y

0.39-0.3620310540933.6776870595.91860.390.36
0.39-0.3620310550936.0497880597.75090.390.36
0.39-0.3720310560938.4219890599.58330.390.37
0.39-0.3720310570940.794900601.41570.390.37
0.38-0.3720310580943.1662910603.24810.380.37
0.38-0.3720310590945.5384920605.08050.380.37
0.38-0.3720310600947.9105930606.91290.380.37
0.38-0.3820310610950.2827940608.74530.380.38
0.38-0.3820310620952.6548950610.57770.380.38
0.38-0.3820310630955.027960612.410.380.38
0.38-0.3820310640957.3992970614.24240.380.38
0.38-0.3820310650959.7713980616.07480.380.38
0.37-0.3920310660962.1435990617.90720.370.39
0.37-0.3920310670964.51561000619.73960.370.39
0.37-0.3920310680966.88781010621.5720.370.39
0.37-0.3920310690969.261020623.40440.370.39
0.37-0.3920310700971.63211030625.23670.370.39
0.37-0.3920310710974.00431040627.06910.370.39
0.37-0.420310720976.37641050628.90150.370.4
0.37-0.420310730978.74861060630.73390.370.4
0.37-0.420310740981.12071070632.56630.370.4
0.36-0.420310750983.49291080634.39870.360.4
0.36-0.420310760985.86511090636.23110.360.4
0.36-0.420310770988.23721100638.06340.360.4
0.36-0.4120310780990.60941110639.89580.360.41
0.36-0.4120310790992.98151120641.72820.360.41
0.36-0.4120310800995.35371130643.56060.360.41
0.36-0.4120310810997.72591140645.3930.360.41
0.36-0.41203108201000.0981150647.22540.360.41
0.36-0.41203108301002.471160649.05780.360.41
0.36-0.41203108401004.8421170650.89020.360.41
0.35-0.42203108501007.2141180652.72250.350.42
0.35-0.42203108601009.5871190654.55490.350.42
0.35-0.42203108701011.9591200656.38730.350.42
0.35-0.42203108801014.3311210658.21970.350.42
0.35-0.42203108901016.7031220660.05210.350.42
0.35-0.42203109001019.0751230661.88450.350.42

<COLGROUP><COL style="WIDTH: 48pt" span=9 width=64><TBODY>
</TBODY>
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,114,112
Messages
5,546,012
Members
410,720
Latest member
SSL
Top