Search Range & Create List

[Alex O]

Is there a formula or macro that will search BI and if the value >0 copy the corresponding value in AW, then do the same for BJ and BK? My data set is 52000+ rows and only 20-30 will have a count >0. I'd like to have a list off to the side just to make identifying them easier....

Thanks!

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 148px"><COL style="WIDTH: 155px"><COL style="WIDTH: 148px"><COL style="WIDTH: 54px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>AW</TD><TD>BA</TD><TD>BE</TD><TD>BI</TD><TD>BJ</TD><TD>BK</TD></TR><TR style="HEIGHT: 67px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left; FONT-WEIGHT: bold">DM Numbers Column1</TD><TD style="TEXT-ALIGN: left; FONT-WEIGHT: bold">DM Numbers Column2</TD><TD style="TEXT-ALIGN: left; FONT-WEIGHT: bold">DM Numbers Column3</TD><TD style="TEXT-ALIGN: left; FONT-WEIGHT: bold">Count1</TD><TD style="TEXT-ALIGN: left; FONT-WEIGHT: bold">Count2</TD><TD style="TEXT-ALIGN: left; FONT-WEIGHT: bold">Count3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BACKGROUND-COLOR: #ff99cc; FONT-WEIGHT: bold"> </TD><TD style="BACKGROUND-COLOR: #ffcc99; FONT-WEIGHT: bold"> </TD><TD style="BACKGROUND-COLOR: #ffff99; FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc; COLOR: #ff99cc">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99; COLOR: #ffcc99">1</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99; COLOR: #ffff99">1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc">125322</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99">232848</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99">306535</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Calibri">1</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Calibri">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc">119643</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99">180911</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99">244822</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Calibri">1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc">20026</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99">162851</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99">308372</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Calibri">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc">20029</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99">162892</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99">288925</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Calibri">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc">20064</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99">162895</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99">288927</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Calibri">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc">20192</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99">162938</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99">288928</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Calibri">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc">20202</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99">163025</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99">288930</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Calibri">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc">20231</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99">163059</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99">288931</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Calibri">0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Calibri">0</TD></TR></TBODY></TABLE>

 

[Alex O]

A point of clarification regarding this query; the values returned when >0 should be pasted in BL:BL.

Thanks<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>