Help with comparing 2 lists of data

[JasHebb]

Hello everyone!

I have a problem that I am sure can be helped with excel any help would be much appreciated!

List one is a list of names and a number relating to where they came from ie

Mr Jason Smith 456
Mrs ayleen Smith 122

etc in alphabetical order, several thousand records.

I then have another list of customers who purchased something this is just the customer name (several hundered records)

I would like to be able to quickly match up MR Jason Smith 456 with the corresponding sale so I can work out if there is a trend between the customers that made a purchase and where they come from

at the moment I put the lists side by side and search each name individually then make a note of the source number, this is quite time consuming!

Any ideas on how I could do this more efficiently?

Thanks

Jason

 

[Latchmaker]

Question 1: Is the Customer Name the exact same in both lists of data....for example is "Mr Jason Smith" in both lists as "Mr Jason Smith"?
Question 2: Is the only piece of information you need to add to the list with purchase data is the "where they came from" code?
Question 3: Is there multiple "Mr Jason Smith" 's or any other name in the "where they came from" list?

 

[JasHebb]

Hi Latchmaker,

1. Usually it is yes, however some names in list 2 will not be in list one at all and so will not be matchable.
2. Yes customer name matched up to the purchase data "where they came from" code is all I need, even if the names in the 1st list were just highlighted this would be use full!
3. No each is unique.

Thanks!

 

[Latchmaker]

Try this
List 1....in Formula sheet named List 1
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Name</th><th>ID#</th></tr>
<tr><td>Mr Jason Smith</td><td>456</td></tr>
<tr><td>Mrs Ayleen Smith</td><td>122</td></tr>
</table>

List 2 in Formula sheet named List 2
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Name</th><th>ID#</th><th>Item</th><th>Cost</th><th>Qty</th></tr>
<tr><td>Mr Jason Smith</td><td>=VLOOKUP(A2,'List 1'!A:B,2,0)</td><td>WidgetA 23</td><td>2</td><td>1</td></tr>
<tr><td>Mrs Ayleen Smith</td><td>=VLOOKUP(A3,'List 1'!A:B,2,0)</td><td>WidgetA 24</td><td>2</td><td>2</td></tr>
<tr><td>Mr Jason Smith</td><td>=VLOOKUP(A4,'List 1'!A:B,2,0)</td><td>WidgetA 25</td><td>2</td><td>3</td></tr>
<tr><td>Mrs Ayleen Smith</td><td>=VLOOKUP(A5,'List 1'!A:B,2,0)</td><td>WidgetA 26</td><td>2</td><td>4</td></tr>
<tr><td>Mr Jason Smith</td><td>=VLOOKUP(A6,'List 1'!A:B,2,0)</td><td>WidgetA 27</td><td>2</td><td>5</td></tr>
<tr><td>Mrs Ayleen Smith</td><td>=VLOOKUP(A7,'List 1'!A:B,2,0)</td><td>WidgetA 28</td><td>2</td><td>6</td></tr>
</table>