- Download from Broadstripes a report containing Name, Unique IDs, Preferred Language, and Opted In Phones. Ensure Multiple Values Separator is the pipe character “|”
- Freeze top row and turn on autofilters
- Remove all characters from Opted-In Phones column that aren’t numerals or “|”
- Add column to right of Opted-In Phones called LEN. Count the number of characters in each cell with formula =LEN(K2) (where “K2” is each cell name in Opted-In Phones column)
- Sort sheet ascending by LEN and delete rows where LEN<10
- Filter for rows where LEN=11 and delete leading 1’s from phone numbers that appear. Delete any remaining rows with a phone number whose LEN=11 that doesn’t have a leading 1.
- Sort sheet descending by LEN column and filter for rows where LEN is NOT 10, 21, 32, 43, or 54.
- From the phones that appear after filtering, delete leading 1’s, email addresses, and characters that aren’t numerals or “|”. Values in the LEN column will automatically change.
- Re-sort descending by LEN and again filter for rows where LEN is NOT 10, 21, 32, 43, or 54. Delete individual phone numbers in the Opted-In Phones column that have more than or fewer than 10 digits and the pipe characters that separate them from other phone numbers.
- Continue to correct until every LEN value is 10, 21, 32, 43, or 54
- Clear the filter in the LEN column. Copy the LEN column and Paste Values
- Add four (or more) additional columns between Opted-In Phones and LEN and use Text to Columns to separate phone numbers in Opted-In Phones using the pipe character “|” as the delimiter.
- Name each new column: Opted-In Phones 1, Opted-In Phones 2, etc.
- Add a new column before Opted In Phones 1 and name it COMPARE. Sort sheet descending by LEN
- Populate the COMPARE column using this formula to identify duplicate phone numbers: =if(k2=l2,”DUPE”,”OK”) (where “k2” and “l2” are two cells with phone numbers in them)
- Sort the sheet ascending by COMPARE. In any rows where the value of COMPARE is “Dupe,” delete the duplicate phone number. Move other good numbers into next-left-most column (ensuring that if someone goes from four phone numbers to three unique phone numbers that all three unique phone numbers are in the first three phone number columns). Change the value in the LEN column as appropriate.
- Continue comparing, sorting, and deleting as necessary for each column combination until every record has only unique phone numbers.
- Sort the sheet descending by LEN
- For every row with multiple phone numbers, copy and paste that row so you have as many identical rows as you do unique phone numbers. Cut and paste phone numbers until each copied row has a different unique phone number for that person in the Opted-In Phones 1 column. Delete all other phone numbers for those rows.
Support and Training Center