lookup highest and lowest from range

[huddsterrier83]

Hi All,

I have a spread sheet where Column B contains a number of repeated numbers. I want to know if it is possible to look at these and return the highest and lowest value from Column A. part of the table is shown below.

I basically need it to either delete all List ID CN3620969 apart from AP20A01, AR71A01 and CN3620969, merge them all into one row or move them all to another sheet, and also do this for the rest of the 'List ID' numbers.

There is no set number of times the 'List ID' can be repeated. I have pre-sorted the list which I don't mind doing as it is just a jumbled mess when first exported.

I would rather someone explained how to do this then made it for me so I can understand how it works. If this is even possible.

I have looked at using Vlookups, index and match and can't get it to do it.
[TABLE="width: 280"]
<TBODY>[TR]
[TD]From Location</SPAN>[/TD]
[TD]List ID</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AP20A01</SPAN>[/TD]
[TD]CN3620969</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQ50A01</SPAN>[/TD]
[TD]CN3620969</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQ62A01</SPAN>[/TD]
[TD]CN3620969</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQ65A01</SPAN>[/TD]
[TD]CN3620969</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQ67A01</SPAN>[/TD]
[TD]CN3620969</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQ79A01</SPAN>[/TD]
[TD]CN3620969</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AR05A01</SPAN>[/TD]
[TD]CN3620969</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AR23A01</SPAN>[/TD]
[TD]CN3620969</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AR33A01</SPAN>[/TD]
[TD]CN3620969</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AR37A01</SPAN>[/TD]
[TD]CN3620969</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AR61A01</SPAN>[/TD]
[TD]CN3620969</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AR71A01</SPAN>[/TD]
[TD]CN3620969</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA13A01</SPAN>[/TD]
[TD]CN3620970</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AM80A01</SPAN>[/TD]
[TD]CN3620970</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AN06A01</SPAN>[/TD]
[TD]CN3620970</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AN08A01</SPAN>[/TD]
[TD]CN3620970</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AN13A01</SPAN>[/TD]
[TD]CN3620970</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AN17A01</SPAN>[/TD]
[TD]CN3620970</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AP21A01</SPAN>[/TD]
[TD]CN3620970</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AP33A01</SPAN>[/TD]
[TD]CN3620970</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AP59A01</SPAN>[/TD]
[TD]CN3620970</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AP63A01</SPAN>[/TD]
[TD]CN3620970</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AP08A01</SPAN>[/TD]
[TD]CN3620971</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AP50A01</SPAN>[/TD]
[TD]CN3620971</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AP54A01</SPAN>[/TD]
[TD]CN3620971</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AP74A01</SPAN>[/TD]
[TD]CN3620971</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQ05A01</SPAN>[/TD]
[TD]CN3620971</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQ08A01</SPAN>[/TD]
[TD]CN3620971</SPAN>[/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

 

[gaz_chops]

When you say "highest and lowest value" do you mean the 1st and Last for each ID?

 

[huddsterrier83]

When you say "highest and lowest value" do you mean the 1st and Last for each ID?

Hi Gaz,

I could sort so it was always the top and bottom, but as the raw data is exported, it wouldn't always be. this list has been sorted.

 

[gaz_chops]

If it is sorted, then this will give you the 1st & Last record, all the others showing Blank, which you could easily filter.
But, I'm sure someone will have a better solution......

=IF(COUNTIF($B$2:$B2,B2)=1,"1st",IF(COUNTIF($B$2:$B2,B2)=COUNTIF($B$2:$B$29,B2),"Last",""))

 

[huddsterrier83]

Hi Gaz,

Thanks for that, but I can only get it to show all blanks and no last or all last's and no blanks. the 1st part works perfect

 

[gaz_chops]

Hi, did you copy the formula exactly? As in without the absolute refs in the 1st countif's?

I get the following

[TABLE="width: 195"]
<tbody>[TR]
[TD="width: 65"]From Location[/TD]
[TD="width: 65"]List ID[/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD]AP20A01[/TD]
[TD]CN3620969[/TD]
[TD]1st[/TD]
[/TR]
[TR]
[TD]AQ50A01[/TD]
[TD]CN3620969[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AQ62A01[/TD]
[TD]CN3620969[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AQ65A01[/TD]
[TD]CN3620969[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AQ67A01[/TD]
[TD]CN3620969[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AQ79A01[/TD]
[TD]CN3620969[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AR05A01[/TD]
[TD]CN3620969[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AR23A01[/TD]
[TD]CN3620969[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AR33A01[/TD]
[TD]CN3620969[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AR37A01[/TD]
[TD]CN3620969[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AR61A01[/TD]
[TD]CN3620969[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AR71A01[/TD]
[TD]CN3620969[/TD]
[TD]Last[/TD]
[/TR]
[TR]
[TD]AA13A01[/TD]
[TD]CN3620970[/TD]
[TD]1st[/TD]
[/TR]
[TR]
[TD]AM80A01[/TD]
[TD]CN3620970[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AN06A01[/TD]
[TD]CN3620970[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AN08A01[/TD]
[TD]CN3620970[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AN13A01[/TD]
[TD]CN3620970[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AN17A01[/TD]
[TD]CN3620970[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AP21A01[/TD]
[TD]CN3620970[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AP33A01[/TD]
[TD]CN3620970[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AP59A01[/TD]
[TD]CN3620970[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AP63A01[/TD]
[TD]CN3620970[/TD]
[TD]Last[/TD]
[/TR]
[TR]
[TD]AP08A01[/TD]
[TD]CN3620971[/TD]
[TD]1st[/TD]
[/TR]
[TR]
[TD]AP50A01[/TD]
[TD]CN3620971[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AP54A01[/TD]
[TD]CN3620971[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AP74A01[/TD]
[TD]CN3620971[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AQ05A01[/TD]
[TD]CN3620971[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]AQ08A01[/TD]
[TD]CN3620971[/TD]
[TD]Last[/TD]
[/TR]
</tbody>[/TABLE]