duplicate numbers in a column

  • Thread starter Thread starter crazygregie
  • Start date Start date
C

crazygregie

I have a list of 7 digit numbers . I am going to parse the last four digits
into column "b" I want to identify the duplicate 3 digit numbers in column A
move them and the 4 digit trailers to column "c" and "d' respectively.
 
If your number is in A1
last four digits into column "b": Right(A1,4)
identify the duplicate 3 digit numbers: in column C type: Left(A1,3)
Apply a filter to column C and sort and it will give you the duplicates.
 
If your number is in A1
last four digits into column "B1": Right(A1,4) and drag formula down
identify the duplicate 3 digit numbers: in column "C" type: Left(A1,3) and
drag formula down

Now you have your 7digit number in A colomn, last 4 digits in B colomn &
first 3 digits in C colomn.
Now select all & paste special as value.
Now index (smaller to greater) on colomn C
Now put the formula in "D2" =C2=C1
you will get some True & some false, True's would be duplicate tripplet's

Or
Also
You can find duplicate tripplet's by using conditional fomatting
Supouse you have values in C2 to C10
select cells C2 to C10 by using shift+ctrl+down key
In conditinal formating, use formula option put the formula
=countif($C$2:$C$10,C2)>1
Now click on format buttom and give your desire format click ok ok & ok
Duplicate tripplets will shonw with your selected format.

Thanks
Sachin Goel
PAFEX
Delhi / India
 
Back
Top