find doubling

find doubling

Post by anil kuma » Sat, 01 Oct 2011 17:52:28



Hello suman,

thanks for reply, it works and fulfill my needs. but not that way I want. If
you have better

Idia plz send me.

Thanks & Regards

Anil

 
 
 

find doubling

Post by neil johnso » Sat, 01 Oct 2011 19:48:47


Hi Anil,

Please find the attached sheet. here i am using countif function with if
function . and later on autofilter to draw the value of duplicate value. and
then then later on you can copy that A-Column data.


> Hi everybody,

> I have a file in which some data is double i want to find that data. I used
> short key Alt + AM but it is remove double data insted of  show data i want
> to copy that data.

>  Thanks & Regards

>  Anil

> --

> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip

> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com


> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel

  for_example(1).xlsx
15K Download

  for_example(1).xlsx
15K Download

 
 
 

find doubling

Post by dguillett » Sat, 01 Oct 2011 22:01:57


Just modify my code to this. You could also sort by site ascending with descending date>advance filter to unique>copy but this is easier.
Option Explicit
Sub CopyLastDatetoothersheeetSAS()
'run from source sheet
Dim i As Long
Dim dlr As Long
On Error Resume Next
For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row + 1
If Cells(i, "d") <> Cells(i - 1, "d") Then

With Sheets("sheet2")
dlr = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
Rows(i - 1).Copy .Cells(dlr, 1)
.Columns.AutoFit
End With

End If
Next i
End Sub

From: anil kumar
Sent: Friday, September 30, 2011 3:43 AM

Subject: Re: $$Excel-Macros$$ find doubling

Dear dguillett and noorani,

I don't want to remove double I want show that and copy in other sheet. for removing I use Alt +AM

  Thanks & Regards

  Anil
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip

3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com


<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

 
 
 

find doubling

Post by Suma » Sun, 02 Oct 2011 15:36:12


Hi, Anil
I think u want to keep in ur file that data also which comes 2 or 3 time but
after removing 2nd or 3rd data which is repeating in ur file if i'm ri8.
Then u sud chose A to z in example data then simply write in 2nd column
=A2=A3 then drag them n it will return True of False.
False value wud be Unique n Trure Value means repeating that value in ur
file.


> Hello suman,

> thanks for reply, it works and fulfill my needs. but not that way I want.
> If you have better

> Idia plz send me.

> Thanks & Regards

> Anil

>> --

> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip

> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com


> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel

--

       ??`?.(*?.?(`?.?*?.?)?.?*).?`??
?*?.??.*.....~*--Suman --*~......*.??.?*`?
       ??`?.(?.?(?.? * ?.?)`?.?).?`??

Mob: +91 9810333884

  A_html_m691da4f3.gif
43K Download
 
 
 

find doubling

Post by anil kuma » Mon, 03 Oct 2011 14:35:18


Hi suman,

I have a address software in which data enterd by other opprters and I think
some data is double so i want to export data from there in excel short out
double data than i will remove that data from software. so i wanted to
printout that double address.

Anil

 
 
 

find doubling

Post by Sam Mathai Chack » Mon, 03 Oct 2011 21:15:35


Here's one way of doing it.

Sub CopyDuplicates()

    With Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
        .Formula = "=COUNTIF($A$2:$A$" & Cells(Rows.Count, 1).End(xlUp).Row
& ",A2)"
        .Value = .Value
        .Cells(0) = "COUNT"
        .Cells(0).Resize(.Rows.Count + 1).AutoFilter 1, ">1"
        .Offset(, -1).Resize(, 2).Copy
Sheets.Add(After:=ActiveSheet).Cells(1)
        .AutoFilter
        .Cells(0).Resize(.Rows.Count + 1).Clear
    End With

End Sub

Regards,

Sam Mathai Chacko (GL)


> Hi suman,

> I have a address software in which data enterd by other opprters and I
> think some data is double so i want to export data from there in excel short
> out double data than i will remove that data from software. so i wanted to
> printout that double address.

> Anil

> --

> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip

> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com


> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel

--
Sam Mathai Chacko
 
 
 

find doubling

Post by rajan verm » Tue, 04 Oct 2011 01:30:48


You can Also use Conditional Fomatting in Excel-2007 to Identify Dulicatte
Recort.

GoTo Conditional Formatting -> new Rule -> Format Only Unique Or Duplicate
Values .

Regards
Rajan


> Here's one way of doing it.

> Sub CopyDuplicates()

>     With Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
>         .Formula = "=COUNTIF($A$2:$A$" & Cells(Rows.Count, 1).End(xlUp).Row
> & ",A2)"
>         .Value = .Value
>         .Cells(0) = "COUNT"
>         .Cells(0).Resize(.Rows.Count + 1).AutoFilter 1, ">1"
>         .Offset(, -1).Resize(, 2).Copy
> Sheets.Add(After:=ActiveSheet).Cells(1)
>         .AutoFilter
>         .Cells(0).Resize(.Rows.Count + 1).Clear
>     End With

> End Sub

> Regards,

> Sam Mathai Chacko (GL)


>> Hi suman,

>> I have a address software in which data enterd by other opprters and I
>> think some data is double so i want to export data from there in excel short
>> out double data than i will remove that data from software. so i wanted to
>> printout that double address.

>> Anil

>> --

>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip

>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com


>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel

> --
> Sam Mathai Chacko

> --

> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip

> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com


> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel

--
Regards
Rajan verma
+91 9158998701
 
 
 

find doubling

Post by Suma » Tue, 04 Oct 2011 15:18:04


Use this =countif(A:A,A2)>1 in conditional formating. Its compares all of
the values in column A to the contents of cell A2 the condition is TRUE if
there r more den one. After dat set the format if there r duplicate. Then
use filter by color.


> Hi suman,

> I have a address software in which data enterd by other opprters and I
> think some data is double so i want to export data from there in excel short
> out double data than i will remove that data from software. so i wanted to
> printout that double address.

> Anil

> --

> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip

> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com


> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel

--

       ??`?.(*?.?(`?.?*?.?)?.?*).?`??
?*?.??.*.....~*--Suman --*~......*.??.?*`?
       ??`?.(?.?(?.? * ?.?)`?.?).?`??

Mob: +91 9810333884

  A_html_m691da4f3.gif
43K Download
 
 
 

find doubling

Post by rajan verm » Thu, 06 Oct 2011 00:06:28


Hi
sort your Data and Insert A Coloum and insert this Function :

=Countif($A$1:A1,A1)


> Use this =countif(A:A,A2)>1 in conditional formating. Its compares all of
> the values in column A to the contents of cell A2 the condition is TRUE if
> there r more den one. After dat set the format if there r duplicate. Then
> use filter by color.


>> Hi suman,

>> I have a address software in which data enterd by other opprters and I
>> think some data is double so i want to export data from there in excel short
>> out double data than i will remove that data from software. so i wanted to
>> printout that double address.

>> Anil

>> --

>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip

>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com


>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel

> --

>        ??`?.(*?.?(`?.?*?.?)?.?*).?`??
> ?*?.??.*.....~*--Suman --*~......*.??.?*`?
>        ??`?.(?.?(?.? * ?.?)`?.?).?`??

> Mob: +91 9810333884

>   --

> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip

> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com


> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel

--
Regards
Rajan verma
+91 9158998701

  A_html_m691da4f3.gif
43K Download