Macro to Populate 3 Columns Based on ONE Criterion

[boldcode]

Hello,


In the excel file that I am using I have 3 worksheets and I will explain their purpose below:

1. Before Macro – This worksheet represents the actual data that I will be importing into excel from an external data source
<table style="width: 785px; height: 210px;" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:2787;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:1507;width:32pt" width="42"> <col style="mso-width-source:userset;mso-width-alt:1223;width:26pt" width="34"> <col style="mso-width-source:userset;mso-width-alt:1792;width:38pt" width="50"> <col style="mso-width-source:userset;mso-width-alt:2218;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:1962;width:41pt" width="55"> <col style="width:48pt" width="64"> <tbody><tr style="height:14.4pt" height="19"> <td class="xl71" style="height:14.4pt;width:59pt" width="78" height="19">Report Date</td> <td class="xl66" style="border-left:none;width:58pt" width="78">EmployeeID</td> <td class="xl72" style="border-left:none;width:32pt" width="42">Name</td> <td class="xl66" style="border-left:none;width:26pt" width="34">BW</td> <td class="xl66" style="border-left:none;width:38pt" width="50">Class</td> <td class="xl66" style="border-left:none;width:47pt" width="62">Make</td> <td class="xl66" style="border-left:none;width:41pt" width="55">Function</td> <td class="xl67" style="border-left:none;width:48pt" width="64">Hours</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">2</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">2</td> <td class="xl65" style="border-top:none;border-left:none">Tim</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">4</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">Jen</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">5</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none">Ben</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">9</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">1</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none">Scott</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">6</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">7</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">2</td> <td class="xl65" style="border-top:none;border-left:none">Tim</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">8</td> </tr> </tbody></table>

2. After Macro – This worksheet shows what the data will look like after I run the macro
<table style="width: 792px; height: 273px;" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:2787;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:1507;width:32pt" width="42"> <col style="mso-width-source:userset;mso-width-alt:1223;width:26pt" width="34"> <col style="mso-width-source:userset;mso-width-alt:1792;width:38pt" width="50"> <col style="mso-width-source:userset;mso-width-alt:1422;width:30pt" width="40"> <col style="mso-width-source:userset;mso-width-alt:3982;width:84pt" width="112"> <col style="mso-width-source:userset;mso-width-alt:1479;width:31pt" width="42"> <tbody><tr style="height:14.4pt" height="19"> <td class="xl71" style="height:14.4pt;width:59pt" width="78" height="19">Report Date</td> <td class="xl66" style="border-left:none;width:58pt" width="78">EmployeeID</td> <td class="xl72" style="border-left:none;width:32pt" width="42">Name</td> <td class="xl66" style="border-left:none;width:26pt" width="34">BW</td> <td class="xl66" style="border-left:none;width:38pt" width="50">Class</td> <td class="xl66" style="border-left:none;width:30pt" width="40">Make</td> <td class="xl66" style="border-left:none;width:84pt" width="112">Function</td> <td class="xl67" style="border-left:none;width:31pt" width="42">Hours</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">2</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">2</td> <td class="xl65" style="border-top:none;border-left:none">Tim</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> <td class="xl65" style="border-top:none;border-left:none">4</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">Jen</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">Accounting</td> <td class="xl65" style="border-top:none;border-left:none">5</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none">Ben</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">9</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> <td class="xl65" style="border-top:none;border-left:none">1</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none">Scott</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">Engineering</td> <td class="xl65" style="border-top:none;border-left:none">6</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">7</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">2</td> <td class="xl65" style="border-top:none;border-left:none">Tim</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> <td class="xl65" style="border-top:none;border-left:none">8</td> </tr> </tbody></table>

3. DataList – This worksheet contains a list of items that the macro will use (I ACTUALLY CREATED NAMED LIST THAT GOES BY "TheList" THAT THE MACRO WILL REFERENCE INSTEAD OF THE WORKSHEET)

<table style="width: 354px; height: 168px;" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:1223;width:26pt" width="34"> <col style="mso-width-source:userset;mso-width-alt:1792; width:38pt" width="50" span="2"> <col style="mso-width-source:userset;mso-width-alt:4664;width:98pt" width="131"> <tbody><tr style="height:14.4pt" height="19"> <td class="xl67" style="height:14.4pt;width:26pt" width="34" height="19">BW</td> <td class="xl68" style="border-left:none;width:38pt" width="50">Class</td> <td class="xl68" style="border-left:none;width:38pt" width="50">Make</td> <td class="xl69" style="border-left:none;width:98pt" width="131">Function</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl66" style="height:14.4pt;border-top:none" height="19">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl66" style="height:14.4pt;border-top:none" height="19">2</td> <td class="xl65" style="border-top:none;border-left:none">NC 8</td> <td class="xl65" style="border-top:none;border-left:none">NC 8</td> <td class="xl65" style="border-top:none;border-left:none">Project Management</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl66" style="height:14.4pt;border-top:none" height="19">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl66" style="height:14.4pt;border-top:none" height="19">4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">Engineering</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl66" style="height:14.4pt;border-top:none" height="19">5</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">Accounting</td> </tr> </tbody></table>

I created the Before Macro and After Macro worksheets just to illustrate what happens to the data when the Macro is executed. In reality, this excel workbook would only contain 2 worksheets (The first worksheet will go by the name "Dataset" and 2nd worksheet will go by the name "DataList").

MACRO Explanation: What I want the Macro to do is populate the cells under 3 columns “Class”, “Make”, “Function” from the list on the “DataList” worksheet (I created a Named List called “TheList” that the macro can reference as oppose to referencing the worksheet) based on the value under the column “BW”.

I’ve used a lookup function to accomplish this task and it works fine, but I would like to use a Macro instead.

Thanks,
BC

 

[hiker95]

boldcode,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[boldcode]

I am using Excel 2007

 

[boldcode]

Hiker95,

I just found out that you can copy for example the data below into excel as text and excel will separate the data into its own column.


<table style="width: 792px; height: 273px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2787;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:1507;width:32pt" width="42"> <col style="mso-width-source:userset;mso-width-alt:1223;width:26pt" width="34"> <col style="mso-width-source:userset;mso-width-alt:1792;width:38pt" width="50"> <col style="mso-width-source:userset;mso-width-alt:1422;width:30pt" width="40"> <col style="mso-width-source:userset;mso-width-alt:3982;width:84pt" width="112"> <col style="mso-width-source:userset;mso-width-alt:1479;width:31pt" width="42"> </colgroup><tbody><tr style="height:14.4pt" height="19"> <td class="xl71" style="height:14.4pt;width:59pt" width="78" height="19">Report Date</td> <td class="xl66" style="border-left:none;width:58pt" width="78">EmployeeID</td> <td class="xl72" style="border-left:none;width:32pt" width="42">Name</td> <td class="xl66" style="border-left:none;width:26pt" width="34">BW</td> <td class="xl66" style="border-left:none;width:38pt" width="50">Class</td> <td class="xl66" style="border-left:none;width:30pt" width="40">Make</td> <td class="xl66" style="border-left:none;width:84pt" width="112">Function</td> <td class="xl67" style="border-left:none;width:31pt" width="42">Hours</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">2</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">2</td> <td class="xl65" style="border-top:none;border-left:none">Tim</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> <td class="xl65" style="border-top:none;border-left:none">4</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">Jen</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">Accounting</td> <td class="xl65" style="border-top:none;border-left:none">5</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none">Ben</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">9</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> <td class="xl65" style="border-top:none;border-left:none">1</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none">Scott</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">Engineering</td> <td class="xl65" style="border-top:none;border-left:none">6</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">7</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;border-top:none" height="19">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">2</td> <td class="xl65" style="border-top:none;border-left:none">Tim</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> <td class="xl65" style="border-top:none;border-left:none">8</td></tr></tbody></table>

 

[hiker95]

boldcode,

I can not tell what columns/cells your data is in.


To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[boldcode]

BEFORE MACRO:

<table style="width: 928px; height: 252px;" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:2417;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2787;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2616; width:55pt" width="74" span="6"> <tbody><tr style="height:14.4pt" height="19"> <td class="xl68" style="height:14.4pt;width:51pt" width="68" height="19">
</td> <td style="width:59pt" width="78">COLUMN A</td> <td style="width:58pt" width="78">COLUMN B</td> <td style="width:55pt" width="74">COLUMN C</td> <td style="width:55pt" width="74">COLUMN D</td> <td style="width:55pt" width="74">COLUMN E</td> <td style="width:55pt" width="74">COLUMN F</td> <td style="width:55pt" width="74">COLUMN G</td> <td style="width:55pt" width="74">COLUMN H</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 1</td> <td class="xl69">Report Date</td> <td class="xl64" style="border-left:none">EmployeeID</td> <td class="xl70" style="border-left:none">Name</td> <td class="xl64" style="border-left:none">BW</td> <td class="xl64" style="border-left:none">Class</td> <td class="xl64" style="border-left:none">Make</td> <td class="xl64" style="border-left:none">Function</td> <td class="xl65" style="border-left:none">Hours</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 2</td> <td class="xl71" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl63" style="border-top:none;border-left:none">Mike</td> <td class="xl63" style="border-top:none;border-left:none">3</td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none">2</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 3</td> <td class="xl71" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">2</td> <td class="xl63" style="border-top:none;border-left:none">Tim</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none">4</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 4</td> <td class="xl71" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">3</td> <td class="xl63" style="border-top:none;border-left:none">Jen</td> <td class="xl63" style="border-top:none;border-left:none">5</td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none">5</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 5</td> <td class="xl71" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">4</td> <td class="xl63" style="border-top:none;border-left:none">Ben</td> <td class="xl63" style="border-top:none;border-left:none">3</td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none">9</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 6</td> <td class="xl71" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl63" style="border-top:none;border-left:none">Mike</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none">1</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 7</td> <td class="xl71" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">5</td> <td class="xl63" style="border-top:none;border-left:none">Scott</td> <td class="xl63" style="border-top:none;border-left:none">4</td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none">6</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 8</td> <td class="xl71" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl63" style="border-top:none;border-left:none">Mike</td> <td class="xl63" style="border-top:none;border-left:none">3</td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none">7</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 9</td> <td class="xl71" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">2</td> <td class="xl63" style="border-top:none;border-left:none">Tim</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none"> </td> <td class="xl63" style="border-top:none;border-left:none">8</td> </tr> </tbody></table>
AFTER MACRO:

<table style="width: 1110px; height: 315px;" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:2275;width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2787;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2616;width:55pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2645;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2588;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:2560;width:54pt" width="72"> <col style="mso-width-source:userset;mso-width-alt:3982;width:84pt" width="112"> <col style="mso-width-source:userset;mso-width-alt:2645;width:56pt" width="74"> <tbody><tr style="height:14.4pt" height="19"> <td class="xl70" style="height:14.4pt;width:48pt" width="64" height="19">
</td> <td style="width:59pt" width="78">COLUMN A</td> <td style="width:58pt" width="78">COLUMN B</td> <td style="width:55pt" width="74">COLUMN C</td> <td style="width:56pt" width="74">COLUMN D</td> <td style="width:55pt" width="73">COLUMN E</td> <td style="width:54pt" width="72">COLUMN F</td> <td style="width:84pt" width="112">COLUMN G</td> <td style="width:56pt" width="74">COLUMN H</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 1</td> <td class="xl71">Report Date</td> <td class="xl66" style="border-left:none">EmployeeID</td> <td class="xl72" style="border-left:none">Name</td> <td class="xl66" style="border-left:none">BW</td> <td class="xl66" style="border-left:none">Class</td> <td class="xl66" style="border-left:none">Make</td> <td class="xl66" style="border-left:none">Function</td> <td class="xl67" style="border-left:none">Hours</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 2</td> <td class="xl73" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">2</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 3</td> <td class="xl73" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">2</td> <td class="xl65" style="border-top:none;border-left:none">Tim</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> <td class="xl65" style="border-top:none;border-left:none">4</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 4</td> <td class="xl73" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">Jen</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">Accounting</td> <td class="xl65" style="border-top:none;border-left:none">5</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 5</td> <td class="xl73" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none">Ben</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">9</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 6</td> <td class="xl73" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> <td class="xl65" style="border-top:none;border-left:none">1</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 7</td> <td class="xl73" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none">Scott</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">Engineering</td> <td class="xl65" style="border-top:none;border-left:none">6</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 8</td> <td class="xl73" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">7</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 9</td> <td class="xl73" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">2</td> <td class="xl65" style="border-top:none;border-left:none">Tim</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> <td class="xl65" style="border-top:none;border-left:none">8</td> </tr> </tbody></table>
DataList:

<table style="width: 599px; height: 210px;" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2616;width:55pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2616; width:55pt" width="74" span="2"> <col style="mso-width-source:userset;mso-width-alt:4664;width:98pt" width="131"> <tbody><tr style="height:14.4pt" height="19"> <td class="xl70" style="height:14.4pt;width:48pt" width="64" height="19">
</td> <td class="xl72" style="width:55pt" width="74">COLUMN A</td> <td style="width:55pt" width="74">COLUMN B</td> <td style="width:55pt" width="74">COLUMN C</td> <td style="width:98pt" width="131">COLUMN D</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 1</td> <td class="xl67">BW</td> <td class="xl68" style="border-left:none">Class</td> <td class="xl68" style="border-left:none">Make</td> <td class="xl69" style="border-left:none">Function</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 2</td> <td class="xl66" style="border-top:none">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 3</td> <td class="xl66" style="border-top:none">2</td> <td class="xl65" style="border-top:none;border-left:none">NC 8</td> <td class="xl65" style="border-top:none;border-left:none">NC 8</td> <td class="xl65" style="border-top:none;border-left:none">Project Management</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 4</td> <td class="xl66" style="border-top:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 5</td> <td class="xl66" style="border-top:none">4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">Engineering</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">ROW 6</td> <td class="xl66" style="border-top:none">5</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">Accounting</td> </tr> </tbody></table>

 

[hiker95]

boldcode,


Sample worksheets before the macro:

Excel Workbook
	A	B	C	D
1	BW	Class	Make	Function
2	1	NC 1	NC 1	Finance
3	2	NC 8	NC 8	Project Management
4	3	NC 9	NC 9	Quality Assurance
5	4	NS 4	NS 4	Engineering
6	5	NS 7	NS 7	Accounting
7
DataList

Excel Workbook
	A	B	C	D	E	F	G	H
1	Report Date	EmployeeID	Name	BW	Class	Make	Function	Hours
2	6/9/2011	1	Mike	3				2
3	6/9/2011	2	Tim	1				4
4	6/9/2011	3	Jen	5				5
5	6/9/2011	4	Ben	3				9
6	6/9/2011	1	Mike	1				1
7	6/9/2011	5	Scott	4				6
8	6/9/2011	1	Mike	3				7
9	6/9/2011	2	Tim	1				8
10
Dataset

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Report Date	EmployeeID	Name	BW	Class	Make	Function	Hours
2	6/9/2011	1	Mike	3	NC 9	NC 9	Quality Assurance	2
3	6/9/2011	2	Tim	1	NC 1	NC 1	Finance	4
4	6/9/2011	3	Jen	5	NS 7	NS 7	Accounting	5
5	6/9/2011	4	Ben	3	NC 9	NC 9	Quality Assurance	9
6	6/9/2011	1	Mike	1	NC 1	NC 1	Finance	1
7	6/9/2011	5	Scott	4	NS 4	NS 4	Engineering	6
8	6/9/2011	1	Mike	3	NC 9	NC 9	Quality Assurance	7
9	6/9/2011	2	Tim	1	NC 1	NC 1	Finance	8
10
Dataset

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub UpdateDataset()
' hiker95, 06/09/2011
' http://www.mrexcel.com/forum/showthread.php?t=556133
Dim wS As Worksheet, wL As Worksheet
Dim c As Range, FR As Long
Application.ScreenUpdating = False
Set wS = Worksheets("Dataset")
Set wL = Worksheets("DataList")
For Each c In wS.Range("D2", wS.Range("D" & Rows.Count).End(xlUp))
  If c <> "" Then
    FR = 0
    On Error Resume Next
    FR = Application.Match(c, wL.Columns(1), 0)
    On Error GoTo 0
    If FR > 0 Then
      c.Offset(, 1).Resize(, 3).Value = wL.Range("B" & FR).Resize(, 3).Value
    End If
  End If
Next c
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the UpdateDataset macro.


The macro will adjust for more data in worksheet DataList.

 

[boldcode]

hiker95,

The code works like a champ, you are clearly a VBA MVP!!!! ray:ray:ray:

Thanks!!!!