Hi Excel experts
I have limited knowledge of Macros but would need one to help save on work load for something. I believe it should be straightforward enough, but then again I'm a novice!
Basically I have a weekly report (in excel format) that collects stats on staff. Each week the file is unique because the assignments change very often. It is a simple table of names, stats and a couple of other columns of info.
Currently we are manually using Vlookup to collect this info and to paste it in another central workbook file. Thing is, every week we need to create a new Vlookup formula in a new row in the central file to point to the newly (weekly) created file and I'm sure a Macro would be easier.
Ideally what the macro would do is run on the central document, ask which document to 'vlookup' the data to mine, then paste the info from certain (set) columns into the central file in a new column.
The Vlookup matches the names and then extracts the data from Stat2, Stat3, Category1, Client, Stat3, Date w/e columns and pastes them in matching columns in the central file.
The info looks like the below.
Weekly file
<table width="1308" border="0" cellpadding="0" cellspacing="0"><col style="width: 107pt;" width="143"> <col style="width: 48pt;" width="64"> <col style="width: 57pt;" width="76"> <col style="width: 48pt;" width="64"> <col style="width: 61pt;" width="81"> <col style="width: 62pt;" width="82"> <col style="width: 107pt;" width="143"> <col style="width: 50pt;" width="66"> <col style="width: 48pt;" width="64"> <col style="width: 238pt;" width="317"> <col style="width: 46pt;" width="61" span="2"> <col style="width: 65pt;" width="86"> <tbody><tr style="height: 32.25pt;" height="43"> <td class="xl65" style="height: 32.25pt; width: 107pt;" width="143" height="43">Employee</td> <td class="xl66" style="width: 48pt;" width="64">Lang</td> <td class="xl66" style="width: 57pt;" width="76">Category1</td> <td class="xl68" style="width: 50pt;" width="66"> Stat1
</td> <td class="xl66" style="width: 48pt;" width="64">Stat2</td> <td class="xl66" style="width: 238pt;" width="317">Random col to ignore
</td> <td class="xl66" style="width: 46pt;" width="61">Client</td> <td class="xl66" style="width: 46pt;" width="61">Stat3</td> <td class="xl69" style="width: 65pt;" width="86">Date w/e</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Name1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">DA</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Title1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">3</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">6.4</td> <td class="xl63" style="border-top: medium none; border-left: medium none;"> Text to ignore
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Client1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">5
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right"> 01/01/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Name2</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">DA</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Title2</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">8</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">6.2</td> <td class="xl63" style="border-top: medium none; border-left: medium none;"> Text to ignore</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Cilent2</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">01/01/2012</td> </tr> </tbody></table>
Central file
<table width="470" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="3"> <col style="width: 56pt;" width="75"> <col style="width: 48pt;" width="64" span="2"> <col style="width: 56pt;" width="75"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Name</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Stat2</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Stat3</td> <td class="xl65" style="border-left: medium none; width: 56pt;" width="75">Category1</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Client</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Stat3</td> <td class="xl65" style="border-left: medium none; width: 56pt;" width="75">date w/e</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Name1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Name2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> </tr> </tbody></table>
The sucessive mined data is put after the existing info, so the name is in the first Col and long row of stats afterward, separated each week with the date w/e col. Ideally the marcro will search for the next open cell and paste the info or it could ask into which column to start pasting.
it looks like this:
<table width="929" border="0" cellpadding="0" cellspacing="0"><col width="64"><col width="64" span="2"><col width="75"><col width="64" span="5"><col width="86"><col width="64" span="4"><tr height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Name</td> <td class="xl65" style="width: 48pt;" width="64">Stat2</td> <td class="xl65" style="width: 48pt;" width="64">Stat3</td> <td class="xl65" style="width: 56pt;" width="75">Category1</td> <td class="xl65" style="width: 48pt;" width="64">Client</td> <td class="xl65" style="width: 48pt;" width="64">Stat3</td> <td class="xl65" style="width: 48pt;" width="64">date w/e</td> <td class="xl65" style="width: 48pt;" width="64">Stat2</td> <td class="xl65" style="width: 48pt;" width="64">Stat3</td> <td class="xl65" style="width: 65pt;" width="86">Category1</td> <td class="xl65" style="width: 48pt;" width="64">Client</td> <td class="xl65" style="width: 48pt;" width="64">Stat3</td> <td class="xl65" style="width: 48pt;" width="64">date w/e</td> <td class="xl65" style="width: 48pt;" width="64">etc...</td> </tr></table>
Let me know if you think it's not possible or anything. Thanks in advance for any help!
gj
I have limited knowledge of Macros but would need one to help save on work load for something. I believe it should be straightforward enough, but then again I'm a novice!
Basically I have a weekly report (in excel format) that collects stats on staff. Each week the file is unique because the assignments change very often. It is a simple table of names, stats and a couple of other columns of info.
Currently we are manually using Vlookup to collect this info and to paste it in another central workbook file. Thing is, every week we need to create a new Vlookup formula in a new row in the central file to point to the newly (weekly) created file and I'm sure a Macro would be easier.
Ideally what the macro would do is run on the central document, ask which document to 'vlookup' the data to mine, then paste the info from certain (set) columns into the central file in a new column.
The Vlookup matches the names and then extracts the data from Stat2, Stat3, Category1, Client, Stat3, Date w/e columns and pastes them in matching columns in the central file.
The info looks like the below.
Weekly file
<table width="1308" border="0" cellpadding="0" cellspacing="0"><col style="width: 107pt;" width="143"> <col style="width: 48pt;" width="64"> <col style="width: 57pt;" width="76"> <col style="width: 48pt;" width="64"> <col style="width: 61pt;" width="81"> <col style="width: 62pt;" width="82"> <col style="width: 107pt;" width="143"> <col style="width: 50pt;" width="66"> <col style="width: 48pt;" width="64"> <col style="width: 238pt;" width="317"> <col style="width: 46pt;" width="61" span="2"> <col style="width: 65pt;" width="86"> <tbody><tr style="height: 32.25pt;" height="43"> <td class="xl65" style="height: 32.25pt; width: 107pt;" width="143" height="43">Employee</td> <td class="xl66" style="width: 48pt;" width="64">Lang</td> <td class="xl66" style="width: 57pt;" width="76">Category1</td> <td class="xl68" style="width: 50pt;" width="66"> Stat1
</td> <td class="xl66" style="width: 48pt;" width="64">Stat2</td> <td class="xl66" style="width: 238pt;" width="317">Random col to ignore
</td> <td class="xl66" style="width: 46pt;" width="61">Client</td> <td class="xl66" style="width: 46pt;" width="61">Stat3</td> <td class="xl69" style="width: 65pt;" width="86">Date w/e</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Name1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">DA</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Title1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">3</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">6.4</td> <td class="xl63" style="border-top: medium none; border-left: medium none;"> Text to ignore
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Client1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">5
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right"> 01/01/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Name2</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">DA</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Title2</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">8</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">6.2</td> <td class="xl63" style="border-top: medium none; border-left: medium none;"> Text to ignore</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Cilent2</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">01/01/2012</td> </tr> </tbody></table>
Central file
<table width="470" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="3"> <col style="width: 56pt;" width="75"> <col style="width: 48pt;" width="64" span="2"> <col style="width: 56pt;" width="75"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Name</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Stat2</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Stat3</td> <td class="xl65" style="border-left: medium none; width: 56pt;" width="75">Category1</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Client</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Stat3</td> <td class="xl65" style="border-left: medium none; width: 56pt;" width="75">date w/e</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Name1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Name2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">VLOOKUP</td> </tr> </tbody></table>
The sucessive mined data is put after the existing info, so the name is in the first Col and long row of stats afterward, separated each week with the date w/e col. Ideally the marcro will search for the next open cell and paste the info or it could ask into which column to start pasting.
it looks like this:
<table width="929" border="0" cellpadding="0" cellspacing="0"><col width="64"><col width="64" span="2"><col width="75"><col width="64" span="5"><col width="86"><col width="64" span="4"><tr height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Name</td> <td class="xl65" style="width: 48pt;" width="64">Stat2</td> <td class="xl65" style="width: 48pt;" width="64">Stat3</td> <td class="xl65" style="width: 56pt;" width="75">Category1</td> <td class="xl65" style="width: 48pt;" width="64">Client</td> <td class="xl65" style="width: 48pt;" width="64">Stat3</td> <td class="xl65" style="width: 48pt;" width="64">date w/e</td> <td class="xl65" style="width: 48pt;" width="64">Stat2</td> <td class="xl65" style="width: 48pt;" width="64">Stat3</td> <td class="xl65" style="width: 65pt;" width="86">Category1</td> <td class="xl65" style="width: 48pt;" width="64">Client</td> <td class="xl65" style="width: 48pt;" width="64">Stat3</td> <td class="xl65" style="width: 48pt;" width="64">date w/e</td> <td class="xl65" style="width: 48pt;" width="64">etc...</td> </tr></table>
Let me know if you think it's not possible or anything. Thanks in advance for any help!
gj
