Data Validation with repeaters excluded from the source list

H

Hari

Hi,

In sheet 1 I have data in Column A starting from row No 2 like :-

Aw
Er
Aw
Rt
Yu
Ui
Rt
Aw
Ui

Now, the above list which is in column A will have its rows expanding. That
is today the data may be till Row 300 and tomorrow it will/might
increase to 500 and after some time it may go to even 2000 or so.Now many of
the records in column 1 are repeating. Like U may notice "Aw", "Rt" etc more
than once.

The "solution" I desire is in Sheet 2 Column C starting from Row 2, I want
to create a dropdown (validation list) based on the data in column A of
sheet 1
But since there are many repeaters in Column A of sheet 1 I want that the
list in sheet 2 to have only one instance. That is "Aw", "Rt" etc should
figure only once in the dropdown.

Please guide me.

( I went thru Debra Dalgleish's site on Validation ... but couldnt find what
I was looking for --I might have missed something .)

Regards,
Hari
India
 
F

Frank Kabel

Hi
one way: Use a helper column. e.g. column B and enter the following
formulas
B1:
=A1

B2: Enter the array formula (entered with CTRL+SHIFT+ENTER:
=IF(ISERROR(INDEX($A$1:$A$100,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$100),0))),
"",INDEX($A$1:$A$100,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$100),0)))
and copy B2 down as far as needed.
Use this column as your column for the data validation list
 
H

Hari

Hi Frank,

Thanx a lot for ur solution.

(I know that formula will work but just in case it doesnt due to the setup
of my data I will ask for ur help....)

Regards,
Hari
India
 
H

Hari

Hi Frank,

Im having some problems with this formula....

My initial repeater list is in column B starting from row no 3.

I created a helper list in column AQ starting from row no 3.

So in the array formula u have given I changed $A$1 to $B43, $A$100 to
$B$100, B$1 to AQ$3 and B1 to AQ1.

Then i copied this formula till AQ 100.

But the result im getting is a blank. I used excel's evaluate formula
option (I have 2002) and saw that where the countif is being evaluated
(inside Match function) the formula evaluates to N#A for all cells. I dont
understand the logic behind having look up value in Match as Zero. May be
thats why the problem is coming (Im just speculating, pls correct me if my
understanding is woefully inadequate).

Also please note my original repeater list also have some blanks in some of
the rows.

I have pasted my raw (Dummy data ) extending from B3 to B25 below.

Column B

123
123
123
123
123
123
123
123
123
345
345
345
345


345
345
123
123
345
345
345
345



Please guide me.

Regards,
Hari
India
 
H

Hari

Hi Frank,

I thought of a different formula in my case which is working fine.

I have eliminated the helper column.

As said before I have the source data in Column B starting from row no 3 and
Im creating a dropdown list in Column AR starting from rowno3.

For AR 3 I put formula as --> =IF(B3<>0,B3,"")

For all rows starting from row 4 in column AR I put the formula
as -->=IF(COUNTIF($AR$3:AR3,B4)=0,IF(B4="","",B4),"")

Though my problem is solved I have a request from ur side.

I would like to LEARN your logic especially why u have done it that way and
more importantly how your formula achieves what u wanted to do. Like Im not
able to understand how one can have a formula like -->
COUNTIF(B$1:B1,$A$1:$A$100) where both range and critirea's are arrays. I
did not know that criteria could be an array. Also even criteria could be an
array why is it that here range is a smaller array as compared to criteria.
( Logic wise isnt range supposed to be a SUPERSET of criteria).

Also u have used the above Countif along with match. But for the Match u
specify the lookup value as Zero. Why is that zero.

Please shed some light so that I may better understand how u people
manipulate formulas to achieve what u want to do.

Regards,
Hari
India
 
Top