Question description:
This user has given permission to use the problem statement for this
blog.
I have a spreadsheet with information on thousands of contacts. Lots of information is mismatched such as phone number in the address column etc.. What is the best way to go about fixing this?
Solved by B. U. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
11/09/2018 - 06:13
Hi
Excelchat Expert
11/09/2018 - 06:14
Vlookup or index and match function could be used for matching them correctly
Excelchat Expert
11/09/2018 - 06:15
If possible please share the file, so that I can help you better
User
11/09/2018 - 06:15
Sure I will share the file.
User
11/09/2018 - 06:15
[Uploaded an Excel file]
Excelchat Expert
11/09/2018 - 06:17
Also, please explain more about the problem
User
11/09/2018 - 06:18
There are lots of errors in this spreadsheet such as duplicate names or addresses in the wrong category
Excelchat Expert
11/09/2018 - 06:19
Could you please give an example
Excelchat Expert
11/09/2018 - 06:21
There?
User
11/09/2018 - 06:21
Penneyo@Cox.Net Douglas Olson Olson Olson Olson Olson Olson 4808370127 4808370127 4808370127 4808370127 4808370127 4808370127 6028038333 Fountain Hills 1 8/18/16 21:32 8/18/16 21:32 10/4/17 12:10 Open House | Local Events Oct 6-8, 2017 04c037213f 124213057 1b3200c3aa
User
11/09/2018 - 06:21
row 4448
User
11/09/2018 - 06:22
row 443
User
11/09/2018 - 06:22
4443
User
11/09/2018 - 06:22
row 4439
User
11/09/2018 - 06:23
there are errors in those rows and similar errors all throughout the worksheet
User
11/09/2018 - 06:24
are you there?
Excelchat Expert
11/09/2018 - 06:24
ye
Excelchat Expert
11/09/2018 - 06:25
*yes
Excelchat Expert
11/09/2018 - 06:25
Was going throught the data
User
11/09/2018 - 06:25
okay
Excelchat Expert
11/09/2018 - 06:27
First of all, I will clean the columns
Excelchat Expert
11/09/2018 - 06:28
if any of the fields match First Name or Last Name, I would replace it with blank
Excelchat Expert
11/09/2018 - 06:28
Also, on more question I have - Did you pull this data from some server?
Excelchat Expert
11/09/2018 - 06:28
and saved it as csv file?
User
11/09/2018 - 06:29
yes I did
Excelchat Expert
11/09/2018 - 06:30
This solution is not excel related but I believe this could be solved while pulling data itself
Excelchat Expert
11/09/2018 - 06:31
If the data in the server contains "," in the fields then while saving it as CSV those values get split into other columns since the delimiter of CSV files are commas
Excelchat Expert
11/09/2018 - 06:31
So, saving them as txt file could solve the problem
Excelchat Expert
11/09/2018 - 06:32
In that way file would have tab as delimiter and not split the fields by commas
Excelchat Expert
11/09/2018 - 06:32
Does this make sense to you?
User
11/09/2018 - 06:34
the original file in the server has these errors aswell
Excelchat Expert
11/09/2018 - 06:34
okay
Excelchat Expert
11/09/2018 - 06:35
While going thorugh the data, I saw that mostly names have spilled into other columns so am replacing those with blanks at first
User
11/09/2018 - 06:36
Okay great, how are you going about doing that?
Excelchat Expert
11/09/2018 - 06:37
=IF(OR(D2=B2,D2=C2),"",D2)
Excelchat Expert
11/09/2018 - 06:37
I am using this formula
Excelchat Expert
11/09/2018 - 06:38
So, what will happen is if the filed matches first name or last name then it will replace it with blank otherwise retain the original value
Excelchat Expert
11/09/2018 - 06:39
Something like this
[Uploaded an Excel file]
User
11/09/2018 - 06:40
okay cool, where are you entering this formula?
Excelchat Expert
11/09/2018 - 06:41
I am creating new columns for each of the fields and entering the formula in the new column
Excelchat Expert
11/09/2018 - 06:41
If you look at the sheet, column E and G have the formula
User
11/09/2018 - 06:43
Okay I see
Excelchat Expert
11/09/2018 - 06:49
Here it is, I did similar thing for rest of the columns
[Uploaded an Excel file]
Excelchat Expert
11/09/2018 - 06:50
Please have a look and let me know if any modifications are required
User
11/09/2018 - 06:53
is there a reason that columns f and g are duplicates?
Excelchat Expert
11/09/2018 - 06:54
First is the original column and the second after treating it with formula
Excelchat Expert
11/09/2018 - 06:55
If you want, I can just keep the treated column and delete the old one
User
11/09/2018 - 06:56
yes that would be great
Excelchat Expert
11/09/2018 - 06:56
okay, working on it
User
11/09/2018 - 06:57
can you also filter out columns B and C and leave it blank if it is just the email repeated?
User
11/09/2018 - 06:57
from column A
Excelchat Expert
11/09/2018 - 06:58
sure
Excelchat Expert
11/09/2018 - 06:58
Basically, same thing apply it on B and C, also
User
11/09/2018 - 06:58
yes exactly
Excelchat Expert
11/09/2018 - 06:58
okay
Excelchat Expert
11/09/2018 - 07:02
Here it is
[Uploaded an Excel file]
User
11/09/2018 - 07:05
this one still has the email from column A repeated into columns B and C
Excelchat Expert
11/09/2018 - 07:05
okay, i might have attached the wrong file
Excelchat Expert
11/09/2018 - 07:05
Let me check
User
11/09/2018 - 07:06
okay
Excelchat Expert
11/09/2018 - 07:06
Also, which row is it happening, i will re-check it
User
11/09/2018 - 07:08
row 313 is the first place I see it
Excelchat Expert
11/09/2018 - 07:08
okay
User
11/09/2018 - 07:09
also in row 1415 it is putting (Last Name, First name) in column B instead of First name in column B and last name in column C
Excelchat Expert
11/09/2018 - 07:10
[Uploaded an Excel file]
Excelchat Expert
11/09/2018 - 07:10
Okay, for this I would have to perform other type cleaning
User
11/09/2018 - 07:11
Okay how does that work?
Excelchat Expert
11/09/2018 - 07:11
If it finds comma in the name then take instance after the comma in the first name and before comma in the last name
User
11/09/2018 - 07:11
okay, what is the formula for this?
Excelchat Expert
11/09/2018 - 07:12
It will use find function
Excelchat Expert
11/09/2018 - 07:13
where it will find comma then use left function for the last name and combination of right and len functions for the first name
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
