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?