Finding Max and Min Dates in List for Each Job ID

Perryb

New Member
Joined
Jun 27, 2011
Messages
24
Hello,
I am trying to find the date range (i.e, min and max dates) in column B for each of the unique job numbers in column E. Can you help?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Thanks<o:p></o:p>
Perry<o:p></o:p>
Excel 2010<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Job#</TD><TD style="FONT-WEIGHT: bold">Date</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold">Job#</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>004654-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>074213-190</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>004655-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>074411-030</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>004660-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>015850-010</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>990000-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>074530-010</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>004660-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>074531-010</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>004674-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>015972-070</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>016118-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>016120-500</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>004654-000</TD><TD style="TEXT-ALIGN: right">12/25/2004</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>074213-200</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>016283-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>016122-700</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>990000-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>016126-060</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>015923-050</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>016125-500</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>016116-060</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>016071-700</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>004654-000</TD><TD style="TEXT-ALIGN: right">11/6/2010</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>016102-010</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>004684-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>016157-000</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD>990250-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>016125-060</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD>990000-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>002816-530</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>990070-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>074244-050</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD>990000-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>074282-090</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD>004000-990</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>073780-570</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD>004648-500</TD><TD style="TEXT-ALIGN: right">6/11/2005</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>016112-010</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD>004658-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>016167-000</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">23</TD><TD>016116-060</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>015985-700</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">24</TD><TD>990400-000</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>074431-060</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">25</TD><TD>016116-050</TD><TD style="TEXT-ALIGN: right">8/30/2008</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>074431-120</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">26</TD><TD>016116-000</TD><TD style="TEXT-ALIGN: right">10/7/2006</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>080334-000</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">27</TD><TD>016116-030</TD><TD style="TEXT-ALIGN: right">10/7/2006</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>080387-000</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">28</TD><TD>016125-010</TD><TD style="TEXT-ALIGN: right">10/7/2006</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>016176-000</TD></TR></TBODY></TABLE>
Sheet1


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #bbb 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A1</TH><TD style="TEXT-ALIGN: left">Job#</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B1</TH><TD style="TEXT-ALIGN: left">Date</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C1</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D1</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E1</TH><TD style="TEXT-ALIGN: left">Job#</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A2</TH><TD style="TEXT-ALIGN: left">004654-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B2</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D2</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E2</TH><TD style="TEXT-ALIGN: left">074213-190</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A3</TH><TD style="TEXT-ALIGN: left">004655-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B3</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C3</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D3</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E3</TH><TD style="TEXT-ALIGN: left">074411-030</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A4</TH><TD style="TEXT-ALIGN: left">004660-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B4</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C4</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D4</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E4</TH><TD style="TEXT-ALIGN: left">015850-010</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A5</TH><TD style="TEXT-ALIGN: left">990000-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B5</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C5</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D5</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E5</TH><TD style="TEXT-ALIGN: left">074530-010</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A6</TH><TD style="TEXT-ALIGN: left">004660-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B6</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C6</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D6</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E6</TH><TD style="TEXT-ALIGN: left">074531-010</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A7</TH><TD style="TEXT-ALIGN: left">004674-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B7</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C7</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D7</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E7</TH><TD style="TEXT-ALIGN: left">015972-070</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A8</TH><TD style="TEXT-ALIGN: left">016118-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B8</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C8</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D8</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E8</TH><TD style="TEXT-ALIGN: left">016120-500</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A9</TH><TD style="TEXT-ALIGN: left">004654-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B9</TH><TD style="TEXT-ALIGN: left">38346</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C9</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D9</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E9</TH><TD style="TEXT-ALIGN: left">074213-200</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A10</TH><TD style="TEXT-ALIGN: left">016283-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B10</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C10</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D10</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E10</TH><TD style="TEXT-ALIGN: left">016122-700</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A11</TH><TD style="TEXT-ALIGN: left">990000-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B11</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C11</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D11</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E11</TH><TD style="TEXT-ALIGN: left">016126-060</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A12</TH><TD style="TEXT-ALIGN: left">015923-050</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B12</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C12</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D12</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E12</TH><TD style="TEXT-ALIGN: left">016125-500</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A13</TH><TD style="TEXT-ALIGN: left">016116-060</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B13</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C13</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D13</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E13</TH><TD style="TEXT-ALIGN: left">016071-700</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A14</TH><TD style="TEXT-ALIGN: left">004654-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B14</TH><TD style="TEXT-ALIGN: left">40488</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C14</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D14</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E14</TH><TD style="TEXT-ALIGN: left">016102-010</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A15</TH><TD style="TEXT-ALIGN: left">004684-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B15</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C15</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D15</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E15</TH><TD style="TEXT-ALIGN: left">016157-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A16</TH><TD style="TEXT-ALIGN: left">990250-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B16</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C16</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D16</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E16</TH><TD style="TEXT-ALIGN: left">016125-060</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A17</TH><TD style="TEXT-ALIGN: left">990000-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B17</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C17</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D17</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E17</TH><TD style="TEXT-ALIGN: left">002816-530</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A18</TH><TD style="TEXT-ALIGN: left">990070-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B18</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C18</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D18</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E18</TH><TD style="TEXT-ALIGN: left">074244-050</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A19</TH><TD style="TEXT-ALIGN: left">990000-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B19</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C19</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D19</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E19</TH><TD style="TEXT-ALIGN: left">074282-090</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A20</TH><TD style="TEXT-ALIGN: left">004000-990</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B20</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C20</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D20</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E20</TH><TD style="TEXT-ALIGN: left">073780-570</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A21</TH><TD style="TEXT-ALIGN: left">004648-500</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B21</TH><TD style="TEXT-ALIGN: left">38514</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C21</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D21</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E21</TH><TD style="TEXT-ALIGN: left">016112-010</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A22</TH><TD style="TEXT-ALIGN: left">004658-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B22</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C22</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D22</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E22</TH><TD style="TEXT-ALIGN: left">016167-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A23</TH><TD style="TEXT-ALIGN: left">016116-060</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B23</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C23</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D23</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E23</TH><TD style="TEXT-ALIGN: left">015985-700</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A24</TH><TD style="TEXT-ALIGN: left">990400-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B24</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C24</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D24</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E24</TH><TD style="TEXT-ALIGN: left">074431-060</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A25</TH><TD style="TEXT-ALIGN: left">016116-050</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B25</TH><TD style="TEXT-ALIGN: left">39690</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C25</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D25</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E25</TH><TD style="TEXT-ALIGN: left">074431-120</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A26</TH><TD style="TEXT-ALIGN: left">016116-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B26</TH><TD style="TEXT-ALIGN: left">38997</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C26</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D26</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E26</TH><TD style="TEXT-ALIGN: left">080334-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A27</TH><TD style="TEXT-ALIGN: left">016116-030</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B27</TH><TD style="TEXT-ALIGN: left">38997</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C27</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D27</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E27</TH><TD style="TEXT-ALIGN: left">080387-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A28</TH><TD style="TEXT-ALIGN: left">016125-010</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B28</TH><TD style="TEXT-ALIGN: left">38997</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C28</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D28</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E28</TH><TD style="TEXT-ALIGN: left">016176-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A29</TH><TD style="TEXT-ALIGN: left">016134-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B29</TH><TD style="TEXT-ALIGN: left">38997</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C29</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D29</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E29</TH><TD style="TEXT-ALIGN: left">016165-000</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>A30</TH><TD style="TEXT-ALIGN: left">074213-210</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B30</TH><TD style="TEXT-ALIGN: left">38997</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C30</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D30</TH><TD style="TEXT-ALIGN: left"></TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E30</TH><TD style="TEXT-ALIGN: left">004667-000</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Perryb

New Member
Joined
Jun 27, 2011
Messages
24
I took a subset of the 368000 lines of date, the sample of 30 rows was not enough to get a match from column E. Assume 004654-000 is the 1st entry in column E, and there are three hits in column A and B
 

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
Perhaps try the following array formulas (need to be entered with CRTL-SHIFT-ENTER not just ENTER):
Excel Workbook
ABCDEFG
1Job#DateJob#Min DateMax Date
2004654-00030/08/2008004654-00025/12/200406/11/2010
3004655-00030/08/2008
4004660-00030/08/2008
5990000-00030/08/2008
6004660-00030/08/2008
7004674-00030/08/2008
8016118-00030/08/2008
9004654-00025/12/2004
10016283-00030/08/2008
11990000-00030/08/2008
12015923-05030/08/2008
13016116-06030/08/2008
14004654-00006/11/2010
15004684-00030/08/2008
16990250-00030/08/2008
17990000-00030/08/2008
18990070-00030/08/2008
19990000-00030/08/2008
20004000-99030/08/2008
21004648-50011/06/2005
22004658-00030/08/2008
23016116-06030/08/2008
24990400-00030/08/2008
25016116-05030/08/2008
26016116-00007/10/2006
27016116-03007/10/2006
28016125-01007/10/2006
Sheet1
Excel 2010
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,258
Messages
5,546,818
Members
410,759
Latest member
Bufnercash
Top