Modify Unique List Array

=IF(SUM(IF($A$2:$A$20<>"",--ISERROR(MATCH($A$2:$A$20,$B$1:B1,0)))),
    INDEX($A$2:$A$20,
        MIN(IF($A$2:$A$20<>"",IF(ISERROR(MATCH($A$2:$A$20,$B$1:B1,0)),
            ROW($A$2:$A$20)-ROW($A$2)+1)))),"")

This the code. It goes through the data in a2-a20 and inputs it in row b2. I need it changed to go from A6-A200 and put into H6. When I change the values it gets all wonky

Originally Posted by DonkeyOte

Try this alternative approach:

I1: =SUMPRODUCT(--($A$6:$A$200<>""),1/COUNTIF($A$6:$A$200,$A$6:$A$200&""))

H6: =IF(ROWS(H$6:H6)>$I$1,"",INDEX($A$6:$A$200,MATCH(1,INDEX(($A$6:$A$200<>"")*ISNA(MATCH($A$6:$A$200,$H$5:$H5,0)),0),0)))
copied down

I1 can be moved to a more appropriate cell, just change reference to it in H6 accordingly... better to perform this calculation only once and refer to the result thereafter in other calcs as opposed to recalculating it in every formula in H6 onwards.

That works perfect. Great work

I also need a similar list that will generate it based on a condition.

For instance if a row is highlighted the list will form for all entries in the highlighted row and before

IE Row 50 is highlighted the list will generate based on all values within the range a20-a50

Highlighted

The user will manually highlight a row. The range will be from cell a3 including the highlighted row.

Bump is this possible

Agreed.

But the condition that the highlighting depends upon is the results of an auction data. Which would need to be entered manually. Thats why I thought the simpliest solution would be to simply just high light the row. If you have any suggestions then I would be happy to hear that

Okay let me rephrase

Cell E1 will contain a percentage i.e., 97.25

The code I am using is

I1: =SUMPRODUCT(--($A$6:$A$200<>""),1/COUNTIF($A$6:$A$200,$A$6:$A$200&""))

H6: =IF(ROWS(H$6:H6)>$I$1,"",INDEX($A$6:$A$200,MATCH(1,INDEX(($A$6:$A$200<>"")*ISNA(MATCH($A$6:$A$200,$H$5:$H5,0)),0),0)))
copied down

What needs to happen... based on the value of cell e1. The codes range needs to be set to include all values less than and equal to e1. The row with the value equal to e1 needs to be automatically highlighted


Thanks sorry for the confusion

I1: =SUMPRODUCT(--($A$6:$A$200<>""),1/COUNTIF($A$6:$A$200,$A$6:$A$200&""))

H6: =IF(ROWS(H$6:H6)>$I$1,"",INDEX($A$6:$A$200,MATCH(1,INDEX(($A$6:$A$200<>"")*ISNA(MATCH($A$6:$A$200,$H$5:$H5,0)),0),0)))
copied down

Right now the range is set from $A$6:$A$200. What needs to happen is the range needs to modified based on the value of e1.

All cells are arranged in decending order by column F.

I need it so that based on the value of e1. The range will be targeted to include all values that are equal and less then e1.

So range =e1 and numbers less then e1.

Then based on the range the unique list will be created based on the values from column a.

Ideally H5 and the unqiue list will be in h10:h500

Any ideas?

Originally Posted by DonkeyOte

I don't understand where you're looking for the value (97.25%) ? Will the value in E1 ALWAYS be listed in "the" range ? If not are the values listed in order ?

A sample would invariably quicken the process in terms of finding a resolution.

The rates are already arranged in ascending order.

The value will always be listed in E1. Which will set the range