Prev: how can I create a dummy variable in Excel?
Next: Blackberry and Excel
From: Jeremy on 19 Apr 2010 14:14
Is there a way I can write a script or conditional format to identify
duplicate data. The data can be marked by highlight if need be.

Ex
Since A1 and A3 are both the same numbers both locations become highlighted.
I have a large range of data to perform this in. The numbers being
identified are lager than 2 digits.
A
1 12
2 13
3 12


Thank you
From: Luke M on 19 Apr 2010 14:19
Conditional format would work fine:
With cell A1:
Formula is,
=COUNTIF(A:A,A1)>1
Note that if you put this formula in the column next to your data, you could
copy it down and then AutoFilter based on TRUE and whatever value is in
column A to find all the duplicates.
--
Best Regards,

Luke M
"Jeremy" <Jeremy(a)discussions.microsoft.com> wrote in message
news:606E9B70-CEDC-4651-B0B2-38823CF47091(a)microsoft.com...
> Is there a way I can write a script or conditional format to identify
> duplicate data. The data can be marked by highlight if need be.
>
> Ex
> Since A1 and A3 are both the same numbers both locations become
> highlighted.
> I have a large range of data to perform this in. The numbers being
> identified are lager than 2 digits.
> A
> 1 12
> 2 13
> 3 12
>
>
> Thank you


From: Bernard Liengme on 19 Apr 2010 14:25
Conditional Formatting
a) all version of Excel;
Select the column of numbers (I will assume this is column A) and open the
Conditional Formatting dialog. The formula you want to use is
=COUNTIF(A:A,A1)>1 and give a suitable format (light red fill, for example)
b) in Excel 2007: select the column of data, open the Conditional Formatting
dialog and locate "Format unique/duplicate..." and proceed from there.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Jeremy" <Jeremy(a)discussions.microsoft.com> wrote in message
news:606E9B70-CEDC-4651-B0B2-38823CF47091(a)microsoft.com...
> Is there a way I can write a script or conditional format to identify
> duplicate data. The data can be marked by highlight if need be.
>
> Ex
> Since A1 and A3 are both the same numbers both locations become
> highlighted.
> I have a large range of data to perform this in. The numbers being
> identified are lager than 2 digits.
> A
> 1 12
> 2 13
> 3 12
>
>
> Thank you

 | 
Pages: 1
Prev: how can I create a dummy variable in Excel?
Next: Blackberry and Excel