Detailed Excel Work

Project Goal:

I have two spreadsheets that need to be compared to find matches in data, one with my customer purchase data and another with data submitted by users of my website. I need to find out which of my customers submitted data on my website, along with all the data they submitted. This has to be done by comparing the customer purchase data spreadsheet with the website user data spreadsheet. Both spreadsheets typically have two similar pieces of data, first and last name and address.

There are two challenges with comparing the data: 1.) The address and first and last name data is not formatted in the same way in each spreadsheet. 2.) The address and first and last name data does not always appear in the website user data spreadsheet.

Project Overview:

The first spreadsheet, "Customer Purchase Data Spreadsheet", contains information about my customers. Each row in the Customer Purchase Data Spreadsheet contains information about a single customer. There are 1,047 rows that contain 8 columns of data in the Customer Purchase Data Spreadsheet. Each of the 1,047 rows in the Customer Purchase Data Spreadsheet need to be compared with the second spreadsheet, "Website User Data Spreadsheet", to determine if there are matches. The Website User Data Spreadsheet has 20,560 rows of data.

Important Project Information about inconsistent name data in each spreadsheet:

1.)The Customer Purchase Data spreadsheet will always contain the customer's name data.

2.)The Website User Data spreadsheet will inconsistently contain the user's name data.

3.)The Customer Purchase Data spreadsheet will commonly have a last name and multiple first names (husband and wife) in this format: last, first & first

4.)The Customer Purchase Data spreadsheet will commonly have a last name, first name and middle name or middle initial in this format: last, first middle name or initial

5.)The Customer Purchase Data spreadsheet will commonly have last names that have a space in them, which will need to be considered when comparing with the Website User Data spreadsheet. Example: MC CAIN, First & First

6.)IMPORTANT: The Customer Purchase Data Spreadsheet will sometimes have a business name in the space where a typical customer name would be. This means that all names in the Customer Data Spreadsheet need to be checked against the "Company" column in the Website User Data spreadsheet.

Important Project Information about non-exact match data (name, address & company):

It is important to note that the name, address and company data may not be an exact match. By using your best judgement, please include near exact matches in the third spreadsheet if this is possible.

Example of non-exact match data to include in third spreadsheet:

- Customer Purchase Data spreadsheet includes this address: 1234 Winding Road

- Website User Data spreadsheet includes this address: 1234 Winding Rd.

- Near matches should be included in the third spreadsheet

Project Deliverable:

A third spreadsheet needs to be created to show all the matches between the two sheets. Each match found needs to be placed in the third spreadsheet containing all the information available in both of the provided spreadsheets. A template for the third spreadsheet is attached to this project.

Example of data in Customer Purchase Data Spreadsheet:

Customer Name Address City State Zip Code Year Brand Type

SMITH, JOHN & MONICA 1234 WINDING LN. RALEIGH NC 27616 2014 MA DP

Example of data in Website User Data Spreadsheet:

First Name Last Name Street Address City State/Region Postal Code Country Email

Monica Smith 1234 WINDING LN. RALEIGH NC 27616 [url removed, login to view]

The format required for the matches in spreadsheet three is attached to this project description.

数据输入 数据处理 Excel

项目ID： #5710123

关于项目