VBA Loop/Vlookup Multiple Values - STUMPED!

[alfread187]

Good Morning,

Since yesterday I have been trying to work through a vba problem that I just can't seem to figure out. I have worked through the logic (I think), so please see below as I try to explain:

I am trying to write a macro that will step through a list of department numbers (each department number is in a different cell in a different row ie: A2, A3, A4....An). The macro needs to look at the department number, then find each instance of the department number on worksheet 2 and return all associated values from column 3 on worksheet 3. Then it needs to step down to the next department number and repeat the process for that number (placing the value in column 3 where the first set of values left off on worksheet 3).

It is like a vlookup return multiple values but for many source values. Ideas?

Thank you for any help you can provide in advance!!

alfread187

 

[GaryWMn]

What if you just created a PivotTable from your second data set and used that to generate the first set you described?

1. Create a PivotTable based on your data set.
2. Put the Department Number as the first row field
3. Put the "Associated Value" as the second row field
4. Hide the totals (as needed)

The result looks like this:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;background-color: #FFFF99;;">Data</td><td style="font-weight: bold;text-align: right;background-color: #FFFF99;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;background-color: #CCFFCC;;">PivotTable</td><td style="font-weight: bold;text-align: right;background-color: #CCFFCC;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #CCFFCC;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #CCFFCC;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;background-color: #FFFF99;;">Department Number</td><td style="font-weight: bold;background-color: #FFFF99;;">Associated value</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">Department Number</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">Associated value</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;background-color: #FFFF99;;">10001</td><td style="text-align: right;background-color: #FFFF99;;">144</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">10001</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">123</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;background-color: #FFFF99;;">10001</td><td style="text-align: right;background-color: #FFFF99;;">159</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">144</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;background-color: #FFFF99;;">10001</td><td style="text-align: right;background-color: #FFFF99;;">123</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">159</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #FFFF99;;">10002</td><td style="text-align: right;background-color: #FFFF99;;">175</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">10002</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">175</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;background-color: #FFFF99;;">10002</td><td style="text-align: right;background-color: #FFFF99;;">179</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">177</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;background-color: #FFFF99;;">10002</td><td style="text-align: right;background-color: #FFFF99;;">177</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">179</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;background-color: #FFFF99;;">10003</td><td style="text-align: right;background-color: #FFFF99;;">193</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">10003</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;background-color: #FFFF99;;">10003</td><td style="text-align: right;background-color: #FFFF99;;">195</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">193</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;background-color: #FFFF99;;">10003</td><td style="text-align: right;background-color: #FFFF99;;">100</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">195</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

You could automate this in VBA, but the end result is pretty quick as it is.

Let us know if this would suit your needs.

Gary