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
<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
<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
<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.
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
* | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS |
H | IN | * | * | Invoice | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | CAP | * | * | * | * | * | Yes | |||||||||||||
H | IN | * | * | 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
* | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U |
I | IN | Case | No | * | * | * | * | CAP | CAP | ||||||||||||
I | IN | Case | No | * | * | * | * | CAP | CAP | ||||||||||||
I | IN | Case | No | * | * | * | * | CAP | CAP | ||||||||||||
I | IN | Case | No | * | * | * | * | CAP | CAP |
<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
* | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS |
H | IN | * | * | Invoice | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | Yes | |||||||||||
I | IN | Case | No | * | * | * | * | CAP | CAP | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | ||||||||||||
I | IN | Case | No | * | * | * | * | CAP | CAP | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | ||||||||||||
H | IN | * | * | Invoice | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | Yes | |||||||||||
I | IN | Case | No | * | * | * | * | CAP | CAP | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | ||||||||||||
I | IN | Case | No | * | * | * | * | CAP | CAP | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * | * |
<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.