validate

K

kyoshirou

can i do a validate in a way so that the
Total value of all rows from column A,B &C is not same from Row 1.

Any ideas?
 
K

kyoshirou

i know normal validate is:
=AND(COUNTIF(C:C,C243)=1,ISTEXT(B243))

can i do something like:
=AND(COUNTIF(CONCATENATE(A37,B37,C37,D37))=1,ISTEXT(B244))
___________________________________________________________

i mean every row should not have the same values.
A B C D
1 2 3 4
2 3 4 5
2 2 2 4
1 2 3 4
2 2 2 4
the last 2 input will promot error coz all the values are same.
Hope i did bring my message clear. Sry for confusing.
 
D

Dave Peterson

I selected A1:D10 and with A1 the activecell, I used this formula in my
Data|Validation formula:

=SUMPRODUCT(--($A1=$A$1:$A$10),
--($B1=$B$1:$B$10),
--($C1=$C$1:$C$10),
--($D1=$D$1:$D$10))<2

All one line.

To look for duplicates.

So I could add the =istext() like:

=AND(SUMPRODUCT(--($A1=$A$1:$A$10),
--($B1=$B$1:$B$10),
--($C1=$C$1:$C$10),
--($D1=$D$1:$D$10))<2,ISTEXT($B1))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
 
K

kyoshirou

i had used:
=AND(COUNTIF(C:C,C243)=1,ISTEXT(B243))

if the field contains repeated value, it will not prompt immediately. But
only when u select the text field again.

Any1 knows whay?
 
K

kyoshirou

i have used:
=AND(COUNTIF(C:C,C243)=1,ISTEXT(B243))

for Validate.

But then it crash with:
=CONCATENATE(A1,B1,C1, D1)

Is there a way to use the VALIDATE first, follow by CONCATENATE function?
 
R

Roger Govier

Hi

Did you try
=IF(ISNA(MODE(A1:D1)),CONCATENATE(A1,B1,C1, D1),"not all different")
 
K

kyoshirou

: Hi Roger,

Do you mean inside Data Validation(Allow Custom),
i fill in this:

=IF(ISNA(MODE(A1:D1)),CONCATENATE(A1,B1,C1, D1),"not all different")

???
 
K

kyoshirou

Or to make it simple, how to do i check there is no duplicate CONCATENATE
values inside 1 column?
 
R

Roger Govier

Hi

I misunderstood what you were trying to do originally.
Reading back through the posts, doesn't Dave Peterson's suggestion work
for you?

If not can you post back with some more detail about what you have in
each of the columns and exactly what it is you are trying to achieve.
 
K

kyoshirou

Hi Roger,
I mean:
i have a column AK. This column data is computed by forumula CONCATENATE
(from columns A,B,C,to AJ).
Next, i would like to check there is no duplicate CONCATENATE data from
columns A,B,C,to AJ. For this i used =AND(COUNTIF(AK:AK,AK2)=1,ISTEXT(A2)
The error prompt out wont work.

Does it sound blur?

(haha)
Thanks!
 
K

kyoshirou

Hi..

wasnt urs the same as =AND(COUNTIF(AK:AK,AK2)=1,ISTEXT(B2)) ?

however, both also cant works.

any tips from here?
 
R

Roger Govier

No, it is not the same.
I have used Absolutes on the column letters.
Try it and see.
 
K

kyoshirou

thanks for ur fast prompt!

=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)
i use inside validate function for comments right? [cant work :( ]
or inside cell? [if inside cell, then i cant use CONCATENATE already]
 
R

Roger Govier

With your Concatenate formula set up in column AK
Mark the range of cells in A2:AJ1000
Data>Validation>Custom>=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)

When you enter data in a row, as soon as you get to AJx, then if that
rows matches any previous rows, you will get the Error dialogue show up.

--
Regards

Roger Govier


kyoshirou said:
thanks for ur fast prompt!

=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)
i use inside validate function for comments right? [cant work :( ]
or inside cell? [if inside cell, then i cant use CONCATENATE already]



Roger Govier said:
No, it is not the same.
I have used Absolutes on the column letters.
Try it and see.
Click to expand...
Click to expand...
 
K

kyoshirou

i know... but then u still need to select that cell so that it will prompt
the error message. Else it wont auto-prompt out.. am i right?

Roger Govier said:
With your Concatenate formula set up in column AK
Mark the range of cells in A2:AJ1000
Data>Validation>Custom>=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)

When you enter data in a row, as soon as you get to AJx, then if that
rows matches any previous rows, you will get the Error dialogue show up.

--
Regards

Roger Govier


kyoshirou said:
thanks for ur fast prompt!

=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)
i use inside validate function for comments right? [cant work :( ]
or inside cell? [if inside cell, then i cant use CONCATENATE already]



Roger Govier said:
No, it is not the same.
I have used Absolutes on the column letters.
Try it and see.

--
Regards

Roger Govier


Hi..

wasnt urs the same as =AND(COUNTIF(AK:AK,AK2)=1,ISTEXT(B2)) ?

however, both also cant works.

any tips from here?


:

Hi

Then maybe
=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)


--
Regards

Roger Govier


Hi Roger,
I mean:
i have a column AK. This column data is computed by forumula
CONCATENATE
(from columns A,B,C,to AJ).
Next, i would like to check there is no duplicate CONCATENATE
data
from
columns A,B,C,to AJ. For this i used
=AND(COUNTIF(AK:AK,AK2)=1,ISTEXT(A2)
The error prompt out wont work.

Does it sound blur?

(haha)
Thanks!


:

Hi

I misunderstood what you were trying to do originally.
Reading back through the posts, doesn't Dave Peterson's
suggestion
work
for you?

If not can you post back with some more detail about what you
have
in
each of the columns and exactly what it is you are trying to
achieve.

--
Regards

Roger Govier


message
Any tips again?

:

Or to make it simple, how to do i check there is no
duplicate
CONCATENATE
values inside 1 column?

:

can i do a validate in a way so that the
Total value of all rows from column A,B &C is not same
from
Row
1.

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
R

Roger Govier

Yes, you're right. You won't get any message until you have tried to
enter the last cell in the row.

You could have Data Validation provide an input message telling the user
what they must do though

--
Regards

Roger Govier


kyoshirou said:
i know... but then u still need to select that cell so that it will
prompt
the error message. Else it wont auto-prompt out.. am i right?

Roger Govier said:
With your Concatenate formula set up in column AK
Mark the range of cells in A2:AJ1000
Data>Validation>Custom>=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)

When you enter data in a row, as soon as you get to AJx, then if that
rows matches any previous rows, you will get the Error dialogue show
up.

--
Regards

Roger Govier


kyoshirou said:
thanks for ur fast prompt!

=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)
i use inside validate function for comments right? [cant work :( ]
or inside cell? [if inside cell, then i cant use CONCATENATE
already]



:

No, it is not the same.
I have used Absolutes on the column letters.
Try it and see.

--
Regards

Roger Govier


Hi..

wasnt urs the same as =AND(COUNTIF(AK:AK,AK2)=1,ISTEXT(B2)) ?

however, both also cant works.

any tips from here?


:

Hi

Then maybe
=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)


--
Regards

Roger Govier


message
Hi Roger,
I mean:
i have a column AK. This column data is computed by forumula
CONCATENATE
(from columns A,B,C,to AJ).
Next, i would like to check there is no duplicate CONCATENATE
data
from
columns A,B,C,to AJ. For this i used
=AND(COUNTIF(AK:AK,AK2)=1,ISTEXT(A2)
The error prompt out wont work.

Does it sound blur?

(haha)
Thanks!


:

Hi

I misunderstood what you were trying to do originally.
Reading back through the posts, doesn't Dave Peterson's
suggestion
work
for you?

If not can you post back with some more detail about what
you
have
in
each of the columns and exactly what it is you are trying to
achieve.

--
Regards

Roger Govier


message
Any tips again?

:

Or to make it simple, how to do i check there is no
duplicate
CONCATENATE
values inside 1 column?

:

can i do a validate in a way so that the
Total value of all rows from column A,B &C is not same
from
Row
1.

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
K

kyoshirou

Alright then.. thanks!

Roger Govier said:
Yes, you're right. You won't get any message until you have tried to
enter the last cell in the row.

You could have Data Validation provide an input message telling the user
what they must do though

--
Regards

Roger Govier


kyoshirou said:
i know... but then u still need to select that cell so that it will
prompt
the error message. Else it wont auto-prompt out.. am i right?

Roger Govier said:
With your Concatenate formula set up in column AK
Mark the range of cells in A2:AJ1000
Data>Validation>Custom>=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)

When you enter data in a row, as soon as you get to AJx, then if that
rows matches any previous rows, you will get the Error dialogue show
up.

--
Regards

Roger Govier


thanks for ur fast prompt!

=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)
i use inside validate function for comments right? [cant work :( ]
or inside cell? [if inside cell, then i cant use CONCATENATE
already]



:

No, it is not the same.
I have used Absolutes on the column letters.
Try it and see.

--
Regards

Roger Govier


Hi..

wasnt urs the same as =AND(COUNTIF(AK:AK,AK2)=1,ISTEXT(B2)) ?

however, both also cant works.

any tips from here?


:

Hi

Then maybe
=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)


--
Regards

Roger Govier


message
Hi Roger,
I mean:
i have a column AK. This column data is computed by forumula
CONCATENATE
(from columns A,B,C,to AJ).
Next, i would like to check there is no duplicate CONCATENATE
data
from
columns A,B,C,to AJ. For this i used
=AND(COUNTIF(AK:AK,AK2)=1,ISTEXT(A2)
The error prompt out wont work.

Does it sound blur?

(haha)
Thanks!


:

Hi

I misunderstood what you were trying to do originally.
Reading back through the posts, doesn't Dave Peterson's
suggestion
work
for you?

If not can you post back with some more detail about what
you
have
in
each of the columns and exactly what it is you are trying to
achieve.

--
Regards

Roger Govier


message
Any tips again?

:

Or to make it simple, how to do i check there is no
duplicate
CONCATENATE
values inside 1 column?

:

can i do a validate in a way so that the
Total value of all rows from column A,B &C is not same
from
Row
1.

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Top