G
Guest
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?
Total value of all rows from column A,B &C is not same from Row 1.
Any ideas?
G
Guest
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.
=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.
R
Roger Govier
Hi
Maybe
=IF(ISNA(MODE(A1
1)),"all different","not all different")
Maybe
=IF(ISNA(MODE(A1
1)),"all different","not all different")
D
Dave Peterson
I selected A110 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
10 and with A1 the activecell, I used this formula in myData|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
G
Guest
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?
=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?
G
Guest
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?
=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(A11)),CONCATENATE(A1,B1,C1, D1),"not all different")
Did you try
=IF(ISNA(MODE(A1
1)),CONCATENATE(A1,B1,C1, D1),"not all different")
G
Guest
: Hi Roger,
Do you mean inside Data Validation(Allow Custom),
i fill in this:
=IF(ISNA(MODE(A11)),CONCATENATE(A1,B1,C1, D1),"not all different")
???
Do you mean inside Data Validation(Allow Custom),
i fill in this:
=IF(ISNA(MODE(A1
1)),CONCATENATE(A1,B1,C1, D1),"not all different")???
G
Guest
Or to make it simple, how to do i check there is no duplicate CONCATENATE
values inside 1 column?
values inside 1 column?
G
Guest
Any tips again?
kyoshirou said:
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.
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.
G
Guest
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!
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!
R
Roger Govier
Hi
Then maybe
=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)
Then maybe
=AND(COUNTIF($AK:$AK,$AK2)=1,ISTEXT($A2)
G
Guest
Hi..
wasnt urs the same as =AND(COUNTIF(AK:AK,AK2)=1,ISTEXT(B2)) ?
however, both also cant works.
any tips from here?
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.
I have used Absolutes on the column letters.
Try it and see.
G
Guest
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]
=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
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:
G
Guest
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?
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:
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
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?
G
Guest
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?