Necesito un codigo VBA para validar información

M

mpatino

Board Regular
Joined
Jul 8, 2009
Messages
82
Buenos días,

Actualmente tengo un documento con mas de un millon de códigos los cuales tengo que comparar con otra fila de unos trescientos cincuenta mil códigos, para poder identificar ciertos valores, haciendo que los valores se desplieguen las columnas subsiguientes: Por ejemplo

<TABLE class=MsoNormalTable style="WIDTH: 350pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 0in 0in 0in" cellSpacing=0 cellPadding=0 width=467 border=0><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; WIDTH: 44pt; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=59>A<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

</TD><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; WIDTH: 44pt; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=59>B<o:p></o:p>

</TD><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; WIDTH: 44pt; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=59>C<o:p></o:p>

</TD><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; WIDTH: 44pt; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=59>D<o:p></o:p>

</TD><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; WIDTH: 44pt; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=59>E<o:p></o:p>

</TD><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; WIDTH: 21pt; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=28>F<o:p></o:p>

</TD><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; WIDTH: 44pt; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=59>G<o:p></o:p>

</TD><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; WIDTH: 0.75in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=72>H<o:p></o:p>
​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8">
1<o:p></o:p>​



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt">MM #<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">Plan 1<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">Plant 2<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">Plant 3<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">Plant 4<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">MM #<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">Plant code<o:p></o:p>
​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8">
2<o:p></o:p>​



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt">800499<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">AZ05<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">JPA1<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">MYA4<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">OR02<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">800499<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">AZ05<o:p></o:p>
​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8">
3<o:p></o:p>​



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt">800056<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">CR01<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">WMYA<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">800499<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">JPA1<o:p></o:p>
​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4"><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8">
4<o:p></o:p>​



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt">800125<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">AZ05<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">800499<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">MYA4<o:p></o:p>
​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 5"><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8">
5<o:p></o:p>​



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt">800132<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">NLA3<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">800499<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">OR02<o:p></o:p>
​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 6"><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8">
6<o:p></o:p>​



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">800125<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">AZ05<o:p></o:p>
​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 7"><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8">
7<o:p></o:p>​



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">800056<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">CR01<o:p></o:p>

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 8"><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">800056<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">WMYA<o:p></o:p>

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 9; mso-yfti-lastrow: yes"><TD style="PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">800132<o:p></o:p>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; PADDING-LEFT: 0in; BORDER-LEFT-COLOR: #ece9d8; PADDING-BOTTOM: 0in; BORDER-TOP-COLOR: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">NLA3<o:p></o:p>

</TD></TR></TBODY></TABLE>

Entonces, tengo la columna A con ciertos códigos de materiales y columnas G y H con códigos de materiales y códigos de bodegas, en las columnas G y H tengo mas de un millon de códigos ya que el código del material puede existir en diferentes bodegas y en la columna A tengo cerca de 350 mil códigos, entonces lo que necesito es poder comparar la columna A con la columna G y acomodar los códigos de las bodegas según código de material en las columnas B, C, D, etc..

Yo he estado haciendolo con la siguiente fórmula, sin embargo, como son tantos datos, el calculo/validación es muy lento y algúnas veces mi computadora no lo puede validar, entonces tengo que hacerlo de 1000 en 1000 para poder tener una validación mas rapida:

=IF(COLUMNS($B$2:B2)<=COUNTIF($H$2:$H$9,$A2),INDEX($I$2:$I$9,SMALL(IF($H$2:$H$9=$A2,ROW($A$2:$A$9)-ROW($A$2)+1),COLUMNS($A$2:A2))),"")

Me preguntaba si alguien me podía ayudar con un código en VBA que me pudiera ayudar a hacer las validaciónes.

Muchas gracias,

Marco Patiño.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Marco,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

Disculpe pero no estoy entiendo qué es lo que desea hacer.<o:p></o:p>
  1. ¿Tiene lo que está a la izquierda y quiere generar lo que está a la derecha?<o:p></o:p>
  2. ¿Tiene lo que está a la derecha y quiere generar lo que está a la izquierda?<o:p></o:p>
  3. ¿Tiene ambos y quiere algo que le va a decir si hay casos donde las parejas no coinciden?<o:p></o:p>
Y si no me equivoco, la fórmula que usted está empleando (no sé dónde) será: <o:p></o:p>
  • =IF(COLUMNS($B$2:B2)<=COUNTIF($G$2:$G$9,$A2),INDEX($H$2:$H$9,SMALL(IF($G$2:$G$9=$A2,ROW($A$2:$A$9)-ROW($A$2)+1),COLUMNS($A$2:A2))),"")<o:p></o:p>
 
Upvote 0
Hola Marco, Greg,

También quedo un poco confundido.

Por favor, revisa los rangos que has puesto como ejemplo, en la fórmula pareciera que los datos van hasta la columna I y en el ejemplo has puesto datos hasta la columna H:

=IF(COLUMNS($B$2:B2)<=COUNTIF($H$2:$H$9,$A2),INDEX($I$2:$I$9,SMALL(IF($H$2:$H$9=$A2,ROW($A$2:$A$9)-ROW($A$2)+1),COLUMNS($A$2:A2))),"")

Viendo tu datos de ejemplo y tu fórmula, me surgen dos dudas:

1-. ¿Exactamente cómo están tus datos?
2-. ¿Cuántas plantas existen? Digo, ¿pueden ser más de las 4 que has puesto en tu ejemplo? ¿si fueran 10, en dónde se ingresaría el resultado y en dónde habría que buscar los códigos?

En todo caso (aunque no creo que vaya a ser una gran solución :D) es posible que si escribes la fórmula así mejore un poco su rendimiento:

=SI(COLUMNA(A2)<=CONTAR.SI($G$2:$G$9,$A2),INDICE($H$2:$H$9,K.ESIMO.MENOR(SI($G$2:$G$9=$A2,FILA($A$2:$A$9)-1),COLUMNA(A2))),"")

Para reescribir tu fórmula me he basado en los datos de tu ejemplo (hasta la columna H, no en los rangos que parecen indicar tu fórmula original).
 
Upvote 0
Marco,

No se si esta formula matricial sea mas rapida que la que ya tienes, pero hay te va:

En la celda B2 iria:

=INDEX($G$2:$H$9,SMALL(IF($G$2:$H$9=A2,ROW($G$2:$H$9)-ROW($G$2)+1,ROW($H$9)+1),1),2))


en la celda C2 iria:
=INDEX($G$2:$H$9,SMALL(IF($G$2:$H$9=A2,ROW($G$2:$H$9)-ROW($G$2)+1,ROW($H$9)+1),2),2)

en la cedla D2 pones lo mismo que la C2 pero le cambias el penultimo numero a 3 y asi lo haces para las columnas restantes.

Recuerda las formulas matriciales se hacen al oprimir control-shift-enter no nada mas enter al momento the ingresarlas.

ademas, si uno de las instancias no esta presente te regresaria un error. Lo podrias eliminar con un =IF(ISERROR(la formula) (i.e.=IF(ISERROR(INDEX($G$2:$H$9,SMALL(IF($G$2:$H$9=A2,ROW($G$2:$H$9)-ROW($G$2)+1,ROW($H$9)+1),1),2)),"",INDEX($G$2:$H$9,SMALL(IF($G$2:$H$9=A2,ROW($G$2:$H$9)-ROW($G$2)+1,ROW($H$9)+1),1),2))

La facilidad de las matriciales es que se pueden copiar, de modo que una vez que tengas la primera fila arreglada copias las formulas hacia abajo y listo.

Saludos.
 
Upvote 0
Ok, muchas gracias a todos. Lo que necesito es lo siguiente: Tengo números de materiales en la columna A, tambien tengo números de materiales no ordenados en la columna G y estos materiales de la columna G esta repetidos por que en la columna H hay códigos de plantas diferentes, entonces lo que necesito es que la columna A sea comparada con la columna G y me devuelva los valores de la columna H en las columnas B, C, D y F si hay mas de un código de planta por cada material. Por ejemplo, si en la comuna A tengo el material 1234 y en la columna G esta el mismo material 3 veces, esto quiere decir que hay 3 codigos de planta en la columna H para ese mismo material, entonces quisiera que esas 3 plantas se desplegaran en las columnas B, C y D.
 
Upvote 0
¿Pero cómo sabe si un material pertenece solamente a planta nº 2 o si hay un caso de dos que están almacenados en planta nº2 y planta nº 4 por ejemplo?
 
Upvote 0
Las columnas G y H son de un reporte que yo corro desde una aplicacion en la cual solicito la lista de materiales y su locación, por lo tanto todo lo que esta en estas dos columnas es lo que tenemos hubicado en las diferentes plantas. Por ejemplo, yo se que el material 800499 existe en 4 plantas diferentes (AZ05, JPA1, MYA4 y OR02), no importa en que secuencia se hubiquen en las columnas B,C,D, etc, lo importante es que se puedan ordenar de manera horizontal, para que de esa forma, en lugar de tener 4 filas diferentes solamente tenga una fila con 4 columnas. Por cierto, los materiales de la columna G, no estan ordenados secuencialmente, estan todos desordenados.

Muchas gracias.
 
Upvote 0
Bueno, la programación de VBA anexo revise y hace el tipo de tabla que quiere en menos de diez segundos procesando 315 mil registros de entrada y 99 mil registros a la salida. El Output sale en una hoja nueva. Si, en realidad sus datos no empiezan en la G1, solamente edite la contante mc_AddressData para corresponder a la dirección actual.

Vea los comentarios al inicio de Sub ReworkPivot y baje el valor de c_lngGroupSize si está muy lento. Edit -- Cambié el valor de la constante a 200 y me corre bastante más rápido.

Para ver cómo funciona use una muestra de como 100 filas y camina el programa con la tecla F8.

Code: 
Option Explicit
 
Private Const mc_strAddressData As String = "G1"
Private m_wsNew As Excel.Worksheet
Private m_wsData As Excel.Worksheet
Private m_pvtNew As Excel.PivotTable
 
Sub Main()
    Application.ScreenUpdating = False
    Set m_wsData = ActiveSheet
    CreatePivot
    ReworkPivot
    Application.ScreenUpdating = True
End Sub
 
Private Sub CreatePivot()
 
    Dim rngSourceData As Excel.Range, _
        pcNew As Excel.PivotCache, _
        pvtNew As Excel.PivotTable, _
        strSrceAddr As String, strDestAddr As String
 
    Set rngSourceData = m_wsData.Range(mc_strAddressData).CurrentRegion
    Set m_wsNew = Sheets.Add
 
    Let strSrceAddr = m_wsData.Name & "!" & rngSourceData.Address(True, True, xlR1C1)
    Let strDestAddr = m_wsNew.Name & "!R3C1"
 
    Set pcNew = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                  SourceData:=strSrceAddr, _
                                                  Version:=XlPivotTableVersionList.xlPivotTableVersion12)
 
    Set m_pvtNew = pcNew.CreatePivotTable(TableDestination:=strDestAddr, _
                                       TableName:="pvtNew", _
                                       DefaultVersion:=XlPivotTableVersionList.xlPivotTableVersion12)
 
    With m_pvtNew
        With .PivotFields(rngSourceData.Range("A1").Text)
            .Orientation = xlRowField
            .Position = 1
        End With
        .AddDataField .PivotFields(rngSourceData.Range("B1").Text), "Count of Plant Code", xlCount
        With .PivotFields(rngSourceData.Range("B1").Text)
            .Orientation = xlColumnField
            .Position = 1
        End With
        .CompactLayoutRowHeader = rngSourceData.Range("A1").Text
        .ColumnGrand = False
        .RowGrand = False
    End With
End Sub
 
Private Sub ReworkPivot()
 
    '// habrá que experimentar para ver qué es una cifra eficiente,
    '// para mí, valores entre 500 y 2500 dan mejores resultados
    Const c_lngGroupSize As Long = 2500
 
    Dim rngPivot As Excel.Range, rngCell As Excel.Range, _
        wf As Excel.WorksheetFunction, _
        lngTotalRows As Long, intGroupCount As Integer, _
        lngRowsRemain As Long, i%, _
        rngChunks() As Excel.Range
 
    Set rngPivot = m_pvtNew.TableRange1
    Set wf = Application.WorksheetFunction
 
    Let lngTotalRows = rngPivot.Rows.Count
    Let intGroupCount = lngTotalRows \ c_lngGroupSize
    Let lngRowsRemain = lngTotalRows - (c_lngGroupSize * intGroupCount)
    ReDim rngChunks(intGroupCount)
 
    With rngPivot
        .Copy
        .PasteSpecial xlPasteValues
        .Offset(-2).Resize(3).EntireRow.Delete
        With .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
            For i = 0 To intGroupCount - 1
                Set rngChunks(i) = .Offset(i * c_lngGroupSize).Resize(c_lngGroupSize)
            Next i
            Set rngChunks(i) = .Offset(i * c_lngGroupSize).Resize(lngRowsRemain)
        End With
    End With
 
    For i = 0 To intGroupCount
        Application.StatusBar = fnStatusBarProgIndic(i / intGroupCount) _
                              & Space(5) & i & " / " & intGroupCount
        With rngChunks(i)
            .SpecialCells(xlCellTypeConstants, 1).FormulaR1C1 = "=R1C"
            .Formula = .Value
            .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftToLeft
        End With
    Next i
    Application.StatusBar = False
    With rngPivot
        With .Range("B1")
            .Formula = "Plant 1"
            .AutoFill Destination:=.Resize(, .CurrentRegion.Columns.Count - 1)
        End With
        Set rngCell = .Cells(1, .Columns.Count)
        .Columns.AutoFit
    End With
 
    While wf.CountA(rngCell.Offset(1).Resize(1000)) = 0 _
    And rngCell.Column <> 1
        rngCell.Clear
        Set rngCell = rngCell.Offset(, -1)
    Wend
End Sub
 
Private Function fnStatusBarProgIndic(ByVal dblPct As Double) As String
    Dim i%, j%
 
    Let i = 25 * dblPct
    Let j = 25 - i
    Let fnStatusBarProgIndic = String(i, ChrW(9608)) & String(j, ChrW(8803))
 
End Function
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
574
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
495
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
912
epactheactor
E

Forum statistics

Threads
1,223,948
Messages
6,175,565
Members
452,652
Latest member
eduedu

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