|
validate
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? |
validate
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? |
validate
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? |
validate
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 |
validate
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? |
validate
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? |
validate
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? |
validate
: 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? |
validate
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? |
validate
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? |
validate
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? |
validate
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? |
validate
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? |
validate
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? |
validate
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? |
validate
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? |
validate
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? |
validate
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? |
validate
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? |
| All times are GMT +1. The time now is 04:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com