duplicate numbers in a column

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.
 
K

Khoshravan

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.
 
S

Sam

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top