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?

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,ISTEX T(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.




"kyoshirou" wrote:

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?

Hi

Maybe

=IF(ISNA(MODE(A1:D1)),"all different","not all different")
--
Regards

Roger Govier


"kyoshirou" wrote in message
...
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,ISTEX T(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.




"kyoshirou" wrote:

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?

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

kyoshirou wrote:

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,ISTEX T(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.

"kyoshirou" wrote:

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?

--

Dave Peterson

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?

"kyoshirou" wrote:

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?

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?


"kyoshirou" wrote:

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?

"kyoshirou" wrote:

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?

Hi

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

--
Regards

Roger Govier


"kyoshirou" wrote in message
...
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?


"kyoshirou" wrote:

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?

"kyoshirou" wrote:

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?

: 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")

???

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"kyoshirou" wrote in message
...
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?


"kyoshirou" wrote:

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?

"kyoshirou" wrote:

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?

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

"kyoshirou" wrote:

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?

Any tips again?

"kyoshirou" wrote:

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

"kyoshirou" wrote:

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?

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


"kyoshirou" wrote in message
...
Any tips again?

"kyoshirou" wrote:

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

"kyoshirou" wrote:

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?

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!


"Roger Govier" wrote:

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


"kyoshirou" wrote in message
...
Any tips again?

"kyoshirou" wrote:

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

"kyoshirou" wrote:

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?

Hi

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


--
Regards

Roger Govier


"kyoshirou" wrote in 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!


"Roger Govier" wrote:

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


"kyoshirou" wrote in message
...
Any tips again?

"kyoshirou" wrote:

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

"kyoshirou" wrote:

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?

Hi..

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

however, both also cant works.

any tips from here?


"Roger Govier" wrote:

Hi

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


--
Regards

Roger Govier


"kyoshirou" wrote in 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!


"Roger Govier" wrote:

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


"kyoshirou" wrote in message
...
Any tips again?

"kyoshirou" wrote:

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

"kyoshirou" wrote:

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?

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

--
Regards

Roger Govier


"kyoshirou" wrote in message
...
Hi..

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

however, both also cant works.

any tips from here?


"Roger Govier" wrote:

Hi

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


--
Regards

Roger Govier


"kyoshirou" wrote in 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!


"Roger Govier" wrote:

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


"kyoshirou" wrote in message
...
Any tips again?

"kyoshirou" wrote:

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

"kyoshirou" wrote:

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?

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" wrote:

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

--
Regards

Roger Govier


"kyoshirou" wrote in message
...
Hi..

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

however, both also cant works.

any tips from here?


"Roger Govier" wrote:

Hi

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


--
Regards

Roger Govier


"kyoshirou" wrote in 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!


"Roger Govier" wrote:

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


"kyoshirou" wrote in message
...
Any tips again?

"kyoshirou" wrote:

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

"kyoshirou" wrote:

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?

With your Concatenate formula set up in column AK
Mark the range of cells in A2:AJ1000
DataValidationCustom=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" wrote in message
...
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" wrote:

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

--
Regards

Roger Govier


"kyoshirou" wrote in message
...
Hi..

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

however, both also cant works.

any tips from here?


"Roger Govier" wrote:

Hi

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


--
Regards

Roger Govier


"kyoshirou" wrote in 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!


"Roger Govier" wrote:

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


"kyoshirou" wrote in
message
...
Any tips again?

"kyoshirou" wrote:

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

"kyoshirou" wrote:

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?

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" wrote:

With your Concatenate formula set up in column AK
Mark the range of cells in A2:AJ1000
DataValidationCustom=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" wrote in message
...
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" wrote:

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

--
Regards

Roger Govier


"kyoshirou" wrote in message
...
Hi..

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

however, both also cant works.

any tips from here?


"Roger Govier" wrote:

Hi

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


--
Regards

Roger Govier


"kyoshirou" wrote in 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!


"Roger Govier" wrote:

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


"kyoshirou" wrote in
message
...
Any tips again?

"kyoshirou" wrote:

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

"kyoshirou" wrote:

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?

Alright then.. thanks!

"Roger Govier" wrote:

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" wrote in message
...
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" wrote:

With your Concatenate formula set up in column AK
Mark the range of cells in A2:AJ1000
DataValidationCustom=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" wrote in message
...
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" wrote:

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

--
Regards

Roger Govier


"kyoshirou" wrote in message
...
Hi..

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

however, both also cant works.

any tips from here?


"Roger Govier" wrote:

Hi

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


--
Regards

Roger Govier


"kyoshirou" wrote in
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!


"Roger Govier" wrote:

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


"kyoshirou" wrote in
message
...
Any tips again?

"kyoshirou" wrote:

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

"kyoshirou" wrote:

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?