|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 4th, 2009, 02:54 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet duplicates
Hi, i need to find duplicates in all sheets and result to be, "yes" or "no".
In "C" column i have numbers, and in "D" column i need the code for duplicates. If any number from "C" column is find in any sheet, the code to display in "D", "yes" or "no". Ex C D 123 yes 321 no Can this be done? Thanks! 
|
|
#2
May 4th, 2009, 03:07 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Worksheet duplicates
Suppose you have the same numbers in Sheet2 Col C and Sheet 3 ColC.
And from Sheet1 you need find whether there is a duplicate; try the below Suppose in Sheet1 C1 = 123 D1 = IF(COUNTIF(Sheet2!C:C,C1)+COUNTIF(Sheet3!C:C,C1)0 ,"Yes","No") If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need to find duplicates in all sheets and result to be, "yes" or "no". In "C" column i have numbers, and in "D" column i need the code for duplicates. If any number from "C" column is find in any sheet, the code to display in "D", "yes" or "no". Ex C D 123 yes 321 no Can this be done? Thanks!
|
|
#3
May 4th, 2009, 03:08 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Worksheet duplicates
Hi,
You'll have to be more specific, a worksheet (depending on version) has around 16777216 cells so with (say) 3 worksheets that's more than 50 million cells which is a demanding search to say the least. Mike "puiuluipui" wrote: Hi, i need to find duplicates in all sheets and result to be, "yes" or "no". In "C" column i have numbers, and in "D" column i need the code for duplicates. If any number from "C" column is find in any sheet, the code to display in "D", "yes" or "no". Ex C D 123 yes 321 no Can this be done? Thanks!
|
|
#4
May 4th, 2009, 03:09 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Worksheet duplicates
Sub findem()
For Each c In Range("c2:c14") For Each ws In Worksheets If ws.Name "Sheet4" Then Set fc = ws.Cells.Find(c) If fc Is Nothing Then c.Offset(, 1) = "Yes" End If Next ws Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i need to find duplicates in all sheets and result to be, "yes" or "no". In "C" column i have numbers, and in "D" column i need the code for duplicates. If any number from "C" column is find in any sheet, the code to display in "D", "yes" or "no". Ex C D 123 yes 321 no Can this be done? Thanks!
|
|
#5
May 4th, 2009, 03:15 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Worksheet duplicates
It's working, but if i don't have any number in ..let's say "C25", the code
display "no". if there is no number in a cell in "C" column, then the code to display nothing. The cell to be empty. Can this be done? Thanks allot! "Jacob Skaria" a scris: Suppose you have the same numbers in Sheet2 Col C and Sheet 3 ColC. And from Sheet1 you need find whether there is a duplicate; try the below Suppose in Sheet1 C1 = 123 D1 = IF(COUNTIF(Sheet2!C:C,C1)+COUNTIF(Sheet3!C:C,C1)0 ,"Yes","No") If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need to find duplicates in all sheets and result to be, "yes" or "no". In "C" column i have numbers, and in "D" column i need the code for duplicates. If any number from "C" column is find in any sheet, the code to display in "D", "yes" or "no". Ex C D 123 yes 321 no Can this be done? Thanks!
|
|
#6
May 4th, 2009, 03:18 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Worksheet duplicates
Better in that it is looking for whole numbers
Sub findwholenumbersinworkbook() lr = Cells(Rows.Count, "a").End(xlUp).Row For Each c In Range("a1:a" & lr) For Each ws In Worksheets If ws.Name "Sheet3" Then Set fc = ws.Cells.Find(c, lookat:=xlWhole) If Not fc Is Nothing Then c.Offset(, 1) = "Yes" End If Next ws Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i need to find duplicates in all sheets and result to be, "yes" or "no". In "C" column i have numbers, and in "D" column i need the code for duplicates. If any number from "C" column is find in any sheet, the code to display in "D", "yes" or "no". Ex C D 123 yes 321 no Can this be done? Thanks!
|
|
#7
May 4th, 2009, 03:19 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Worksheet duplicates
=IF(C1="","",IF(COUNTIF(Sheet2!C:C,C1)+COUNTIF(She et3!C:C,C1)0,"Yes","No"))
If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: It's working, but if i don't have any number in ..let's say "C25", the code display "no". if there is no number in a cell in "C" column, then the code to display nothing. The cell to be empty. Can this be done? Thanks allot! "Jacob Skaria" a scris: Suppose you have the same numbers in Sheet2 Col C and Sheet 3 ColC. And from Sheet1 you need find whether there is a duplicate; try the below Suppose in Sheet1 C1 = 123 D1 = IF(COUNTIF(Sheet2!C:C,C1)+COUNTIF(Sheet3!C:C,C1)0 ,"Yes","No") If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need to find duplicates in all sheets and result to be, "yes" or "no". In "C" column i have numbers, and in "D" column i need the code for duplicates. If any number from "C" column is find in any sheet, the code to display in "D", "yes" or "no". Ex C D 123 yes 321 no Can this be done? Thanks!
|
|
#8
May 4th, 2009, 07:00 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Worksheet duplicates
It's working! Beautiful!
Thanks allot! "Jacob Skaria" wrote: =IF(C1="","",IF(COUNTIF(Sheet2!C:C,C1)+COUNTIF(She et3!C:C,C1)0,"Yes","No")) If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: It's working, but if i don't have any number in ..let's say "C25", the code display "no". if there is no number in a cell in "C" column, then the code to display nothing. The cell to be empty. Can this be done? Thanks allot! "Jacob Skaria" a scris: Suppose you have the same numbers in Sheet2 Col C and Sheet 3 ColC. And from Sheet1 you need find whether there is a duplicate; try the below Suppose in Sheet1 C1 = 123 D1 = IF(COUNTIF(Sheet2!C:C,C1)+COUNTIF(Sheet3!C:C,C1)0 ,"Yes","No") If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need to find duplicates in all sheets and result to be, "yes" or "no". In "C" column i have numbers, and in "D" column i need the code for duplicates. If any number from "C" column is find in any sheet, the code to display in "D", "yes" or "no". Ex C D 123 yes 321 no Can this be done? Thanks!
|
|
#9
May 14th, 2009, 11:07 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Worksheet duplicates
Hi, i need a little more help. Your code is searching for the duplicates in
all sheets, except "sheet1". I need the code to search in "sheet1" except cell with value, that need to be found ("C1"). Can this be done? Thanks! "Jacob Skaria" a scris: =IF(C1="","",IF(COUNTIF(Sheet2!C:C,C1)+COUNTIF(She et3!C:C,C1)0,"Yes","No")) If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: It's working, but if i don't have any number in ..let's say "C25", the code display "no". if there is no number in a cell in "C" column, then the code to display nothing. The cell to be empty. Can this be done? Thanks allot! "Jacob Skaria" a scris: Suppose you have the same numbers in Sheet2 Col C and Sheet 3 ColC. And from Sheet1 you need find whether there is a duplicate; try the below Suppose in Sheet1 C1 = 123 D1 = IF(COUNTIF(Sheet2!C:C,C1)+COUNTIF(Sheet3!C:C,C1)0 ,"Yes","No") If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need to find duplicates in all sheets and result to be, "yes" or "no". In "C" column i have numbers, and in "D" column i need the code for duplicates. If any number from "C" column is find in any sheet, the code to display in "D", "yes" or "no". Ex C D 123 yes 321 no Can this be done? Thanks!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
