Filter similar contact numbers.

[Nimit Mehta]

Hello,

A B C
1 Name Address Ph.no
2 xyz xyz 2357226,
3 abc abc 2357226,9825513891
4 abc ncb 2357226/9227144460
5 fbh bxv 2245633,9865123256
6 nvc ndh 9825513891,235714
7 mng bnc 9825513891/
8 dfsd sfdf 3154665
9 slkdjf skldfj 9227144460
I have 3 coloumns of database of 18000 clients. Name Address and contact nos.
Some of them have same contact numbers as other family members from same
address have purchased a product from us. Also note that land line numbers
begin with 2 and mobile numbers begin with 9. Numbers are seperated either by
a comma or single back slash. "/" I want to delete entire row containing same
" MOBILE" numbers. In the above example, i want to delete rows 3,6,7. (
contains 9825513891 ) and rows 4,9 ( contains 9227744460 ). All mobile
numbers are 10 digits in length. I tried using several functions but none
worked. Any function / macro that would help?

TIA
Nimit.

 

[Dave Peterson]

I'd do this.

Copy column C to column D to keep the original data.

Select column D and change all the commas to slashes to make the separator
character unique.

Then select column D
Data|Text to columns
Delimited by /
Make sure each field is treated as text
Then insert a new column D
put this array formula in D1:
=max(len(e1:x1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Drag it down the column.

Then apply data|filter|autofilter to that column
Show the rows that have a max length of more than 9.
Delete the visible rows.