Another Excel macro question

steveo275

New Member
Joined
Feb 28, 2012
Messages
6
Hello,

I've asked about something similar before here:

Excel Macro question.

I have a similar request. The last one worked great. However, I tried to figure out the macro and how to expand it but it's a bit beyond me.

I need my "Header" sheet:

Header

*ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARAS
HIN**Invoice*********************CAP*****Yes
HIN**Invoice*********************CAP*****Yes

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Ref Key[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Customer[/TD]
[TD="align: center"]Ref Type[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]PO[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]Currency[/TD]
[TD="align: center"]Sales Type[/TD]
[TD="align: center"]Ref Date[/TD]
[TD="align: center"]Due Date[/TD]
[TD="align: center"]Terms[/TD]
[TD="align: center"]Ship To Name[/TD]
[TD="align: center"]Add1[/TD]
[TD="align: center"]Add2[/TD]
[TD="align: center"]Add3[/TD]
[TD="align: center"]City[/TD]
[TD="align: center"]State[/TD]
[TD="align: center"]Zip[/TD]
[TD="align: center"]Country[/TD]
[TD="align: center"]Phone[/TD]
[TD="align: center"]Fax[/TD]
[TD="align: center"]Billto[/TD]
[TD="align: center"]BTAdd1[/TD]
[TD="align: center"]BTAdd2[/TD]
[TD="align: center"]BTAdd3[/TD]
[TD="align: center"]BTCity[/TD]
[TD="align: center"]BTState[/TD]
[TD="align: center"]BTZip[/TD]
[TD="align: center"]BTCountry[/TD]
[TD="align: center"]BTPhone[/TD]
[TD="align: center"]BTFax[/TD]
[TD="align: center"]Discount[/TD]
[TD="align: center"]Tax[/TD]
[TD="align: center"]Sales Loc[/TD]
[TD="align: center"]Notes[/TD]
[TD="align: center"]Station[/TD]
[TD="align: center"]Related[/TD]
[TD="align: center"]Rep1[/TD]
[TD="align: center"]Rep2[/TD]
[TD="align: center"]Period[/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]Exempt[/TD]
[TD="align: center"]Exempt ID[/TD]
[TD="align: center"]Exchg Rate[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]888888[/TD]

[TD="align: right"]1000[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]2/1/2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]488080[/TD]

[TD="align: right"]2000[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]2/1/2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

To find and match each "ref key" on the "Trailer" sheet:

Trailer

*ABCDEFGHIJKLMNOPQRSTU
IINCaseNo****CAPCAP
IINCaseNo****CAPCAP
IINCaseNo****CAPCAP
IINCaseNo****CAPCAP

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Ref Key[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Customer[/TD]
[TD="align: center"]Ref Type[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Seq[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]UM[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Tax[/TD]
[TD="align: center"]Product Name[/TD]
[TD="align: center"]Product Description[/TD]
[TD="align: center"]Detail[/TD]
[TD="align: center"]Adjst[/TD]
[TD="align: center"]Ext Price[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]Return Code[/TD]
[TD="align: center"]Ship Loc[/TD]
[TD="align: center"]Sales Loc[/TD]
[TD="align: center"]Agent[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]888888[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10001[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]888888[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10002[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]488080[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10001[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]488080[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10002[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

And create a new sheet called "Results" that looks like this. So, its saying for each header record "ref key" (ex: 1000) find each trailer record "ref key" with the same number (1000) and then copy the header row exactly and put the each matching trailer row under it.

Results

*ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARAS
HIN**Invoice*****************************Yes
IINCaseNo****CAPCAP************************
IINCaseNo****CAPCAP************************
HIN**Invoice*****************************Yes
IINCaseNo****CAPCAP************************
IINCaseNo****CAPCAP************************

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]888888[/TD]

[TD="align: right"]1000[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]2/1/2012[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]888888[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10001[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]888888[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10002[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]488080[/TD]

[TD="align: right"]2000[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]2/1/2012[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]488080[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10001[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]488080[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10002[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

The columns have to remain as is. The program I'm importing this data with is looking for a certain amount of columns. Thanks again.
 
from FAQ

  • Make sure you are in the correct forum for the question you are asking.
  • Word your questions clearly.
  • ALWAYS state what version of Excel and Windows you are using (i.e., Excel 97, Windows XP, etc.)
  • Don't use a subject line like HELP ME! or EXCEL question. Instead, say something like Trouble With Pivot Tables In XL97. And remember, saying URGENT doesn't make it so to anyone but you.
  • Provide background information for your question when needed. Include example data and formulas in your post. The more you tell us up front, the easier it is for us to answer your questions. If your formulas aren't giving the results you want, include the results you want to get, as well as the results you're getting. Your post should include at least:
 
Upvote 0
My sincerest apologies to the board for not reading the FAQ. I'm sure this has devastated the MrExcel community. I'm not sure how I'm going to sleep at night knowing the pain my subject has caused all of you. We must put this travesty behind us and look forward. It's the only way the healing process can begin.
 
Upvote 0

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