created table of Refs and lookup maximum for repeated references.

[excelnovicekid]

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.36	20	310	540	933.6776	870	595.9186	0.39	0.36
0.39-0.36	20	310	550	936.0497	880	597.7509	0.39	0.36
0.39-0.37	20	310	560	938.4219	890	599.5833	0.39	0.37
0.39-0.37	20	310	570	940.794	900	601.4157	0.39	0.37
0.38-0.37	20	310	580	943.1662	910	603.2481	0.38	0.37
0.38-0.37	20	310	590	945.5384	920	605.0805	0.38	0.37
0.38-0.37	20	310	600	947.9105	930	606.9129	0.38	0.37
0.38-0.38	20	310	610	950.2827	940	608.7453	0.38	0.38
0.38-0.38	20	310	620	952.6548	950	610.5777	0.38	0.38
0.38-0.38	20	310	630	955.027	960	612.41	0.38	0.38
0.38-0.38	20	310	640	957.3992	970	614.2424	0.38	0.38
0.38-0.38	20	310	650	959.7713	980	616.0748	0.38	0.38
0.37-0.39	20	310	660	962.1435	990	617.9072	0.37	0.39
0.37-0.39	20	310	670	964.5156	1000	619.7396	0.37	0.39
0.37-0.39	20	310	680	966.8878	1010	621.572	0.37	0.39
0.37-0.39	20	310	690	969.26	1020	623.4044	0.37	0.39
0.37-0.39	20	310	700	971.6321	1030	625.2367	0.37	0.39
0.37-0.39	20	310	710	974.0043	1040	627.0691	0.37	0.39
0.37-0.4	20	310	720	976.3764	1050	628.9015	0.37	0.4
0.37-0.4	20	310	730	978.7486	1060	630.7339	0.37	0.4
0.37-0.4	20	310	740	981.1207	1070	632.5663	0.37	0.4
0.36-0.4	20	310	750	983.4929	1080	634.3987	0.36	0.4
0.36-0.4	20	310	760	985.8651	1090	636.2311	0.36	0.4
0.36-0.4	20	310	770	988.2372	1100	638.0634	0.36	0.4
0.36-0.41	20	310	780	990.6094	1110	639.8958	0.36	0.41
0.36-0.41	20	310	790	992.9815	1120	641.7282	0.36	0.41
0.36-0.41	20	310	800	995.3537	1130	643.5606	0.36	0.41
0.36-0.41	20	310	810	997.7259	1140	645.393	0.36	0.41
0.36-0.41	20	310	820	1000.098	1150	647.2254	0.36	0.41
0.36-0.41	20	310	830	1002.47	1160	649.0578	0.36	0.41
0.36-0.41	20	310	840	1004.842	1170	650.8902	0.36	0.41
0.35-0.42	20	310	850	1007.214	1180	652.7225	0.35	0.42
0.35-0.42	20	310	860	1009.587	1190	654.5549	0.35	0.42
0.35-0.42	20	310	870	1011.959	1200	656.3873	0.35	0.42
0.35-0.42	20	310	880	1014.331	1210	658.2197	0.35	0.42
0.35-0.42	20	310	890	1016.703	1220	660.0521	0.35	0.42
0.35-0.42	20	310	900	1019.075	1230	661.8845	0.35	0.42

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