Excel Macro question.

S

steveo275

New Member
Joined
Feb 28, 2012
Messages
6
Hello,

This one is a bit beyond my current Excel abilities so I thought I'd ask a forum. I have two sheets with data in it, sheet1 and sheet2.

sheet1:

A B

<table border="0" cellpadding="0" cellspacing="0" width="128"><colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl64" style="height:12.75pt;width:48pt" height="17" width="64">HDR</td> <td style="width:48pt" width="64" align="right">4212</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl64" style="height:12.75pt" height="17">HDR</td> <td align="right">4248</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl64" style="height:12.75pt" height="17">HDR</td> <td align="right">4352</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl64" style="height:12.75pt" height="17">HDR</td> <td align="right">4435</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl64" style="height:12.75pt" height="17">
</td> <td align="right">
</td> </tr> </tbody></table>sheet2:

A B C
<table border="0" cellpadding="0" cellspacing="0" height="483" width="204"><colgroup><col style="mso-width-source:userset;mso-width-alt:1792; width:37pt" span="2" width="49"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:1536;width:32pt" width="42"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:37pt" height="17" width="49" align="right">4212</td> <td style="width:37pt" width="49">
</td> <td class="xl65" style="width:48pt" width="64">ITM</td> <td style="width:32pt" width="42" align="right">30528</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4212</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">30175</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">49700</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">87908</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">19504</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">80218</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">25480</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">87955</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">39263</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">81654</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">76988</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">30175</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">25310</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">74354</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">25235</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">78754</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">30528</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">21980</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4352</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">14174</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4352</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">35728</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4435</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">61257</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4435</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">19504</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4435</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">61287</td> </tr> </tbody></table>
For each number in sheet1 I want to copy each row (with data) in sheet2 that has that number in column A (EXCEPT column A) directly under the number in sheet1. So, we'd end up with this:

A B C D Etc..
HDR 4212 Data Data ...
ITM 30528 Data Data ...
ITM 30175 Data Data ...
HDR 4248 Data Data ...
ITM 49700 Data Data ...
ITM 87908 Data Data ...
ITM 19504 Data Data ...
ITM 80218 Data Data ...
ITM 25480 Data Data ...
ITM 87955 Data Data ...
ITM 39263 Data Data ...
ITM 81654 Data Data ...
ITM 76988 Data Data ...
ITM 30175 Data Data ...
ITM 25310 Data Data ...
ITM 74354 Data Data ...
ITM 25235 Data Data ...
ITM 78754 Data Data ...
ITM 30528 Data Data ...
ITM 21980 Data Data ...

I've tried a number of macro's but haven't been able to get the result i'm looking for. Any help? Thanks.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
steveo275,

Welcome to the MrExcel forum.

What version of Excel are you using?

We can not tell what cells, rows, or columns your data is in.

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum:
sensitive data scrubbed/removed/changed
what you have and what you expect to achieve

To attach screenshots, see below in my Signature block:
Post a screen shot with one of these:
 
Upvote 0
Sheet1 columns A-E

<table border="0" cellpadding="0" cellspacing="0" width="448"><colgroup><col style="width:48pt" span="7" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;width:48pt" height="17" width="64">HDR</td> <td style="width:48pt" width="64" align="right">4212</td> <td style="width:48pt" width="64">
</td> <td class="xl66" style="width:48pt" width="64">Class</td> <td class="xl65" style="width:48pt" width="64" align="right">2/1/2012</td> <td class="xl65" style="width:48pt" width="64">
</td> <td class="xl66" style="width:48pt" width="64">PA</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">HDR</td> <td align="right">4248</td> <td>
</td> <td class="xl66">Class</td> <td class="xl65" align="right">2/1/2012</td> <td class="xl65">
</td> <td class="xl66">PA</td> </tr> </tbody></table>

Sheet2 columns A-G:

<table border="0" cellpadding="0" cellspacing="0" width="372"><colgroup><col style="mso-width-source:userset;mso-width-alt:1792; width:37pt" span="2" width="49"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:1536; width:32pt" span="5" width="42"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:37pt" height="17" width="49" align="right">4212</td> <td style="width:37pt" width="49">
</td> <td class="xl65" style="width:48pt" width="64">ITM</td> <td style="width:32pt" width="42" align="right">30528</td> <td style="width:32pt" width="42">
</td> <td class="xl65" style="width:32pt" width="42">QTY</td> <td class="xl65" style="width:32pt" width="42">
</td> <td class="xl65" style="width:32pt" width="42">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4212</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">30175</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">49700</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">87908</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">19504</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">80218</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">25480</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">87955</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">39263</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">81654</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">76988</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">30175</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">25310</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">74354</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">25235</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">78754</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">30528</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4248</td> <td>
</td> <td class="xl65">ITM</td> <td align="right">21980</td> <td>
</td> <td class="xl65">QTY</td> <td class="xl65">
</td> <td class="xl65">Unit</td> </tr> </tbody></table>
Result columns A-G:

<table border="0" cellpadding="0" cellspacing="0" width="448"><colgroup><col style="width:48pt" span="7" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;width:48pt" height="17" width="64">HDR</td> <td style="width:48pt" width="64" align="right">4212</td> <td style="width:48pt" width="64">
</td> <td class="xl66" style="width:48pt" width="64">Class</td> <td class="xl65" style="width:48pt" width="64" align="right">2/1/2012</td> <td class="xl65" style="width:48pt" width="64">
</td> <td class="xl66" style="width:48pt" width="64">PA</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">30528</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">30175</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">HDR</td> <td align="right">4248</td> <td>
</td> <td class="xl66">Class</td> <td class="xl65" align="right">2/1/2012</td> <td class="xl65">
</td> <td class="xl66">PA</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">49700</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">87908</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">19504</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">80218</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">25480</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">87955</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">39263</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">81654</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">76988</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">30175</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">25310</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">74354</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">25235</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">78754</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ITM</td> <td align="right">30528</td> <td>
</td> <td class="xl66">QTY</td> <td class="xl66">
</td> <td class="xl66">Unit</td> <td>
</td> </tr> </tbody></table>
I hope this is what you are looking for. Thanks
 
Upvote 0
steveo275,

That does not help.

Here is what I get for the first set of data in Sheet1:


Excel Workbook
ABCDEFG
1HDR4212Class2/1/2012PA
2HDR4248Class2/1/2012PA
3
Sheet1





We can not tell what cells, rows, or columns your data is actually in.

What version of Excel are you using?

If you are not able to give us screenshots, see below in my Signature block:
You can upload your workbook to Box Net
mark the workbook for sharing
and provide us with a link to your workbook.
 
Last edited:
Upvote 0
Sorry, I got it now. n00b here.

It's Excel 14.

Excel Workbook
ABCDE
1HDR4212Class2/1/2012PA
2HDR4248Class2/1/2012PA
Sheet1
Excel Workbook
ABCDE
14212ITM30528QTYUnit
24212ITM30175QTYUnit
34248ITM49700QTYUnit
44248ITM87908QTYUnit
54248ITM19504QTYUnit
64248ITM80218QTYUnit
74248ITM25480QTYUnit
84248ITM87955QTYUnit
94248ITM39263QTYUnit
104248ITM81654QTYUnit
114248ITM76988QTYUnit
124248ITM30175QTYUnit
134248ITM25310QTYUnit
144248ITM74354QTYUnit
154248ITM25235QTYUnit
164248ITM78754QTYUnit
174248ITM30528QTYUnit
184248ITM21980QTYUnit
Excel 2010 Sheet2
#VALUE!
Excel 2010
 
Upvote 0
steveo275,


Sample worksheets (with the raw data on Sheet2 grouped/sorted per your example):


Excel Workbook
ABCDE
1HDR4212Class2/1/2012PA
2HDR4248Class2/1/2012PA
3
Sheet1





Excel Workbook
ABCDE
14212ITM30528QTYUnit
24212ITM30175QTYUnit
34248ITM49700QTYUnit
44248ITM87908QTYUnit
54248ITM19504QTYUnit
64248ITM80218QTYUnit
74248ITM25480QTYUnit
84248ITM87955QTYUnit
94248ITM39263QTYUnit
104248ITM81654QTYUnit
114248ITM76988QTYUnit
124248ITM30175QTYUnit
134248ITM25310QTYUnit
144248ITM74354QTYUnit
154248ITM25235QTYUnit
164248ITM78754QTYUnit
174248ITM30528QTYUnit
184248ITM21980QTYUnit
19
Sheet2





After the macro in a new worksheet Results:


Excel Workbook
ABCDE
1HDR4212Class2/1/2012PA
2ITM30528QTYUnit
3ITM30175QTYUnit
4HDR4248Class2/1/2012PA
5ITM49700QTYUnit
6ITM87908QTYUnit
7ITM19504QTYUnit
8ITM80218QTYUnit
9ITM25480QTYUnit
10ITM87955QTYUnit
11ITM39263QTYUnit
12ITM81654QTYUnit
13ITM76988QTYUnit
14ITM30175QTYUnit
15ITM25310QTYUnit
16ITM74354QTYUnit
17ITM25235QTYUnit
18ITM78754QTYUnit
19ITM30528QTYUnit
20ITM21980QTYUnit
21
Results





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.


Code: 
Option Explicit
Sub ReorgData()
' hiker95, 02/28/2012
' http://www.mrexcel.com/forum/showthread.php?t=617316
Dim w1 As Worksheet, w2 As Worksheet, wR As Worksheet
Dim r As Long, lr As Long, n As Long
Dim nr As Long, sr As Long, er As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
lr = w1.Cells(Rows.Count, 1).End(xlUp).Row
Set w2 = Worksheets("Sheet2")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w2).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
nr = 1
For r = 1 To lr Step 1
  wR.Cells(nr, 1).Resize(, 5).Value = w1.Cells(r, 1).Resize(, 5).Value
  n = Application.CountIf(w2.Columns(1), w1.Cells(r, 2).Value)
  If n = 0 Then
    'do nothing
  ElseIf n >= 1 Then
    sr = Application.Match(w1.Cells(r, 2), w2.Columns(1), 0)
    wR.Cells(nr + 1, 1).Resize(n, 4).Value = w2.Cells(sr, 2).Resize(n, 4).Value
  End If
  nr = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Next r
wR.UsedRange.Columns.AutoFit
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 ReorgData macro.
 
Upvote 0
steveo275,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
172
dilshod_k
D
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
358
esben
E
M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
E
  • Question
Create mails with table - standard header specific row
Replies
1
Views
975
esben
E

Forum statistics

Threads
1,215,635
Messages
6,125,948
Members
449,275
Latest member
jacob_mcbride

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top