Awesome video! Super helpful. This video could be slightly expanded to enforce uniqueness on a combination of columns. For example this validation on A6 and B6 would enforce that the combination of Item # and Item Name for a given row isn't duplicated: =(COUNTIF(ARRAYFORMULA(A$6:A&B$6:B),A6&B6)=1). I couldn't find anything on the interwebs that gave this tip.
Very helpful, thank you. How can this be applied across multiple sheets? For example, I have 'xy' allocated in sheet 1 so I would like to prevent the user from allocating that value in sheet 2, sheet 3 etc
Hi, I'd like to ask something: if we have a column with numbers only (e.g. customer phone numbers) and we want while we are entering a new number in this column, a warning message to appear, warning me that this number already exists, and if possible, to show me where (i.e., in which cell) this number is... is this possible in google sheets? And if yes, how can I do that? By means of what function? Thanks in advance.
This was really great. I have a question (Google Sheets) if you would be so kind to help me. I have 4 cells (not in a range; they are independent cells) on a row that can only have the numbers 1, 2, 3 or 4 but with each of these numbers only showing once. How can I do that?
Is there a way to do this but with alternanting cells along the same row? I am using a google sheets for a game. As a tie breaker users can input a number in their column and closest number wins. I would like to prevent users from using the same number without having to visually look through the row to see if there is a duplicate.
Hi Profile Oaktree, I have a question hoping you would be kind enough to help me. I am working on a CSV file right now with 300K+ columns, and I have noticed that there are duplicate entries for some columns. Fortunately, I have successfully removed them (that was exhausting *phew*), what I wanted to know now is that is it possible to use this formula when I am copy-pasting the entry and not inputting it? Thank you. :)
Hi Mate, This is great. What about if you add a new row within that range, will it pick that new row up within the column? Is there a way to make this work for all data and potential data in that particular column? Many thanks,
Great simple video thank you! Question, is there a way to check if the whole row is a duplicate rather than just one cell in the row? Example: =Countif(A6:E25, A6:E6)? to check the whole row of A? I use google forms for booking appointments/ customer orders, and it is submitting duplicated submissions to my google sheets. Can not necessarily narrow it down to just one cell of their submission (I do not use an Item# like you have above).
@@RachelSweeneyMK I need to do the same thing. How do I use the UNIQUE function to accomplish that? Also, does it reject the input when they're completing the form or would that only work if they're filling out a spreadsheet? Thank you!
how do create a Google form to enter the part numbers and prevents you from going forward if a duplicate entry is made or when the submit button is hit it doesn't record the response but produces an error message indicating a duplicate entry was done
Awesome video! Super helpful. This video could be slightly expanded to enforce uniqueness on a combination of columns. For example this validation on A6 and B6 would enforce that the combination of Item # and Item Name for a given row isn't duplicated:
=(COUNTIF(ARRAYFORMULA(A$6:A&B$6:B),A6&B6)=1). I couldn't find anything on the interwebs that gave this tip.
Very helpful, thank you. How can this be applied across multiple sheets? For example, I have 'xy' allocated in sheet 1 so I would like to prevent the user from allocating that value in sheet 2, sheet 3 etc
Thanks for the great tutorial. What if we wanted to exclude any multiple dashes that occur in the range? How would you write that logic? Thx
Thank you, badly needed this
Hi, I'd like to ask something: if we have a column with numbers only (e.g. customer phone numbers) and we want while we are entering a new number in this column, a warning message to appear, warning me that this number already exists, and if possible, to show me where (i.e., in which cell) this number is... is this possible in google sheets? And if yes, how can I do that? By means of what function? Thanks in advance.
same problem here, did you solved it?
Very useful tricks. Thanks for share. (Supper helpful). Your explain clearly.
This was really great. I have a question (Google Sheets) if you would be so kind to help me. I have 4 cells (not in a range; they are independent cells) on a row that can only have the numbers 1, 2, 3 or 4 but with each of these numbers only showing once. How can I do that?
Whoa, not sure. Maybe a custom formula? You can create one now with the new Named function ability.
Thanks! This helped a lot - somehow its hard to find in google.......
That's a good compliment. I try to fit these videos in where they're most needed.
Is there a way to do this but with alternanting cells along the same row? I am using a google sheets for a game. As a tie breaker users can input a number in their column and closest number wins. I would like to prevent users from using the same number without having to visually look through the row to see if there is a duplicate.
This is a great explanation of your solution, thank you very much!
Thanks, I hope you can use it.
@@ProlificOaktree sure did!
super helpful! Thanks!
Exactly What I was looking for!!! Thank you so much!!
Glad I could help!
Thank you, it was very helpful!
Hi Profile Oaktree,
I have a question hoping you would be kind enough to help me. I am working on a CSV file right now
with 300K+ columns, and I have noticed that there are duplicate entries for some columns. Fortunately, I have successfully removed them (that was exhausting *phew*), what I wanted to know now is that is it possible to use this formula when I am copy-pasting the entry and not inputting it? Thank you. :)
I don't think so. Sorry!
I appreciate your response. Thank you.@@ProlificOaktree
Hi Mate,
This is great.
What about if you add a new row within that range, will it pick that new row up within the column?
Is there a way to make this work for all data and potential data in that particular column?
Many thanks,
Thank you so much!!!
Great video, thank you.
Very helpful! Tnank!
how can i apply this to all cells in "A" , so that if i enter any duplicated value , it will stop me from inputting. Any solution ?
have the same issue here... did you find any solution? I know it's been a year since you posted it, but I'm asking just in case...
What if I have a couple of columns to apply the data validation condition. It would be with countifs?
Hard to tell without seeing the spreadsheet but yeah, sounds like you're headed in the right direction.
How i can hight light duplicate values in deferent sheets? Thank you
Very useful video. Thanks.
How can I copy a range and paste it as a picture?
You'd have to do it with a screen shot tool outside of Google Sheets and then put it back in with Insert -> Image or the IMAGE function.
Great simple video thank you! Question, is there a way to check if the whole row is a duplicate rather than just one cell in the row? Example: =Countif(A6:E25, A6:E6)? to check the whole row of A?
I use google forms for booking appointments/ customer orders, and it is submitting duplicated submissions to my google sheets. Can not necessarily narrow it down to just one cell of their submission (I do not use an Item# like you have above).
It may be easier for you to use the UNIQUE function and look at the output.
@@ProlificOaktree Figured that out shortly after commenting. The UNIQUE function worked perfectly! Thank you.
@@RachelSweeneyMK I need to do the same thing. How do I use the UNIQUE function to accomplish that? Also, does it reject the input when they're completing the form or would that only work if they're filling out a spreadsheet? Thank you!
Hey Profile Oaktree hope all is well, what if you don't want duplicate entries however, you would increase the quantity when one is received.
I would think you leave the list as is without doing what is in the video then summarize it with COUNTIF or a pivot table.
thanks for the video
Hi its showing me This cell's contents violate its validation rule
Thanks! now is there a way to combine that + list of items
How to put a color for desame number are duplicate
how to prevent duplicate entries on multiple sheets?
how to use this google form?
how can I prevent duplicate text entries in google sheets, such as names? such as john Doe in line 3 and john Doe in line 20?
This should still work. "John Doe" is equal to "John Doe". Maybe you need to trim whitespace?
how do create a Google form to enter the part numbers and prevents you from going forward if a duplicate entry is made or when the submit button is hit it doesn't record the response but produces an error message indicating a duplicate entry was done
I think you would need someone to write a custom plug in for something like that. Sheets could handle that, but not Forms.
@@ProlificOaktree thanks for your response, do you think that a data entry form could be created to handle that then?
@@davidsookharry8555 I don't really know.
How we link with google form?
See here ua-cam.com/video/vQw2jDlylDU/v-deo.html
thanks
Welcome
cool
Fire
Thank you so much!!
You're welcome!