Finding Duplicate information in 2 seperate sheets

[MegFleming]

I have 2 files of data. One is a list of people who were sent a piece of direct mail. The second file is a list of people who actually purchased. The first file has full first and last name of the individuals in one cell Column A. The second file has the first name in column A and the Last name in column B. I need to compare these two files to determine who actually purchased that were sent a piece of direct mail. I am trying to determine ROI on this mailer. Please advise on ways to tackle this task! I am also working off of Excel 2003 and using a Mac.

 

[JPM]

I suggest that the first thing you do is to concatenate the first and last names in your second file to make the names identical. Then I would use Vlookup and find the matches from each file.

If you need help with the concatenate and vlookup functions, use the Help files in Excel, they are very good in this case or post again.

I hope this helps you.

 

[Haldoug]

Merge your first and last names together with a space by +a1&" "&b1
Tranpose the list from a column to Row 1 starting in column B
Put the other list in column A starting in row 2.
IN b2 enter +$b1=A$2. Use conditional formating to make the cell a brilliant color that stands out if the condition is true. Right double click the botoom right corner of b2 to copy it down the rest of column B. Drag the bottom right corner of those results to cover all of the column. The matched will say "TRUE", and be highlighted by the conditional formatting color. The process took me two minutes.

 

[shemayisroel]

Hi & Welcome,

A simpel way of doing it would be as follows:

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0">SENT DM PIECE</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0">PURCHASED?</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0">PURCHASE F NAME</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0">PURCHASE L NAME</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0">PURCHASED FL NAME</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>John Smith</TD><TD>NO</TD><TD>Peter</TD><TD>Pan</TD><TD>Peter Pan</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Mary parker</TD><TD>NO</TD><TD>Jo</TD><TD>Me</TD><TD>Jo Me</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Peter Pan</TD><TD>YES</TD><TD>Bruce</TD><TD>Lee</TD><TD>Bruce Lee</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Bruce Willis</TD><TD>NO</TD><TD>Jamie</TD><TD>Smith</TD><TD>Jamie Smith</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Jet Li</TD><TD>NO</TD><TD> </TD><TD> </TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Jo Me</TD><TD>YES</TD><TD> </TD><TD> </TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Morgan Freeman</TD><TD>NO</TD><TD> </TD><TD> </TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Samson Lee</TD><TD>NO</TD><TD> </TD><TD> </TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>Bruce Lee</TD><TD>YES</TD><TD> </TD><TD> </TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>Jamie Smith</TD><TD>YES</TD><TD> </TD><TD> </TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=IF(ISERROR(VLOOKUP(A2,$E$2:$E$11,1,FALSE)),"NO","YES")</TD></TR><TR><TD>E2</TD><TD>=C2&" "&D2</TD></TR><TR><TD>B3</TD><TD>=IF(ISERROR(VLOOKUP(A3,$E$2:$E$11,1,FALSE)),"NO","YES")</TD></TR><TR><TD>E3</TD><TD>=C3&" "&D3</TD></TR><TR><TD>B4</TD><TD>=IF(ISERROR(VLOOKUP(A4,$E$2:$E$11,1,FALSE)),"NO","YES")</TD></TR><TR><TD>E4</TD><TD>=C4&" "&D4</TD></TR><TR><TD>B5</TD><TD>=IF(ISERROR(VLOOKUP(A5,$E$2:$E$11,1,FALSE)),"NO","YES")</TD></TR><TR><TD>E5</TD><TD>=C5&" "&D5</TD></TR><TR><TD>B6</TD><TD>=IF(ISERROR(VLOOKUP(A6,$E$2:$E$11,1,FALSE)),"NO","YES")</TD></TR><TR><TD>E6</TD><TD>=C6&" "&D6</TD></TR><TR><TD>B7</TD><TD>=IF(ISERROR(VLOOKUP(A7,$E$2:$E$11,1,FALSE)),"NO","YES")</TD></TR><TR><TD>E7</TD><TD>=C7&" "&D7</TD></TR><TR><TD>B8</TD><TD>=IF(ISERROR(VLOOKUP(A8,$E$2:$E$11,1,FALSE)),"NO","YES")</TD></TR><TR><TD>E8</TD><TD>=C8&" "&D8</TD></TR><TR><TD>B9</TD><TD>=IF(ISERROR(VLOOKUP(A9,$E$2:$E$11,1,FALSE)),"NO","YES")</TD></TR><TR><TD>E9</TD><TD>=C9&" "&D9</TD></TR><TR><TD>B10</TD><TD>=IF(ISERROR(VLOOKUP(A10,$E$2:$E$11,1,FALSE)),"NO","YES")</TD></TR><TR><TD>E10</TD><TD>=C10&" "&D10</TD></TR><TR><TD>B11</TD><TD>=IF(ISERROR(VLOOKUP(A11,$E$2:$E$11,1,FALSE)),"NO","YES")</TD></TR><TR><TD>E11</TD><TD>=C11&" "&D11</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>