Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter similar contact numbers.
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter similar contact numbers.
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. Nimit Mehta wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help on filter or something similar | Excel Worksheet Functions | |||
HOW DO I CHECK FOR SIMILAR NUMBERS IN A WORK SHEET | Excel Worksheet Functions | |||
find similar numbers | Excel Discussion (Misc queries) | |||
Find similar numbers from two columns of numbers | Excel Discussion (Misc queries) | |||
Finding similar data or numbers in two columns | New Users to Excel |