How to detech same data in one sheet
how to know that I'm not key-in the data twice or more in one worksheet? In
my daily job, there is thousands of data and the data cannot duplicate. Thanks a lot friend. |
How to detech same data in one sheet
ShamsulZ wrote: how to know that I'm not key-in the data twice or more in one worksheet? In my daily job, there is thousands of data and the data cannot duplicate. Thanks a lot friend. Please, be more specific. |
How to detech same data in one sheet
Here is a very simple way, ShamsulZ!
Imagine you are typing in column A. Add a new column B, and use the formula =COUNTIF(A:A,A2) in cell B2. Extend it downwards. This will tell you how many times each item occurs. You can then sort or filter on column B for anything that is not 1. -- Allllen "ShamsulZ" wrote: how to know that I'm not key-in the data twice or more in one worksheet? In my daily job, there is thousands of data and the data cannot duplicate. Thanks a lot friend. |
How to detech same data in one sheet
Could you use a countif formula?
eg in [B1]: =countif(A:A,A1) would give more than one if you had a duplucate of A1 elsewhere in colum A "ShamsulZ" wrote: how to know that I'm not key-in the data twice or more in one worksheet? In my daily job, there is thousands of data and the data cannot duplicate. Thanks a lot friend. |
How to detech same data in one sheet
Thanks to both Rob & Allen which gave me the same advice. Morris pls refer to
their reply. I also did ask in this comm for Auto spelled no to sentence. Perhaps you guy can help. Pls refer to the other question. Thanks a lot friend "Morris" wrote: ShamsulZ wrote: how to know that I'm not key-in the data twice or more in one worksheet? In my daily job, there is thousands of data and the data cannot duplicate. Thanks a lot friend. Please, be more specific. |
How to detech same data in one sheet
Hi,
if you want in vba like this : Sub findx() Dim Wk As Worksheet, Mydata, firstfind As String Mydata = "Your data" For Each Wk In Worksheets Set find1 = Wk.Cells.Find(Mydata, LookIn:=xlValues) If Not find1 Is Nothing Then MsgBox find1.Parent.Name Set findnext2 = find1 Do Set findnext1 = Wk.Cells.FindNext(after:=findnext2) If Not findnext1 Is Nothing Then If find1.Address < findnext1.Address Then MsgBox findnext1.Parent.Name End If Set findnext2 = findnext1 Loop Until findnext1.Address = find1.Address End If Next Wk End Sub -- Regards, Halim "ShamsulZ" wrote: how to know that I'm not key-in the data twice or more in one worksheet? In my daily job, there is thousands of data and the data cannot duplicate. Thanks a lot friend. |
How to detech same data in one sheet
There is a lot more information here including other approaches that don't
require an adjacent column. http://www.cpearson.com/excel/duplicat.htm -- Regards, Tom Ogilvy "ShamsulZ" wrote: Thanks to both Rob & Allen which gave me the same advice. Morris pls refer to their reply. I also did ask in this comm for Auto spelled no to sentence. Perhaps you guy can help. Pls refer to the other question. Thanks a lot friend "Morris" wrote: ShamsulZ wrote: how to know that I'm not key-in the data twice or more in one worksheet? In my daily job, there is thousands of data and the data cannot duplicate. Thanks a lot friend. Please, be more specific. |
All times are GMT +1. The time now is 02:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com