| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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
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
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Worksheet function that duplicates End(xlUp)? | Excel Discussion (Misc queries) | |||
| Find Duplicates and Move to New Worksheet | Excel Worksheet Functions | |||
| compare two columns within a worksheet, then highlight duplicates | Excel Worksheet Functions | |||
| When I open a an existing excel worksheet, it duplicates itself a. | Excel Discussion (Misc queries) | |||
| how do i find and delete duplicates in excel worksheet? | Excel Discussion (Misc queries) | |||
Linear Mode
