Referencing a cell adjacent to the first and last instance of a reference value: please help

tommy_b

New Member
Joined
Feb 10, 2016
Messages
4
Hi,

I'm trying to formula that returns a cell value from a column that's adjacent to the first and last instance of my reference cell. Here's a sample of the data:

DateOrder #Sequence
3/15/201730133092000020
12/13/201830135893000010
12/13/201830135895000010
1/9/201730137118000010
1/31/201730137118000020
1/31/201730137118000060
1/6/201730137179000040
1/3/201730137198000040
1/3/201730137198000050
1/5/201730137198000060
1/5/201730137198000070
1/5/201730137198000080
1/6/201730137266000010
1/6/201730137266000040
1/6/201730137266000050
1/9/201730137266000060
1/16/201730137266000070
1/3/201730137318000010
1/11/201730137318000040
1/11/201730137318000050
1/16/201730137318000060
1/17/201730137318000070
1/19/201730137318000080

<tbody>
</tbody>


Ultimately what I'm trying to do is get the delta between the first sequence that appears for an order # and the last sequence. I have no idea how to make this happen.

For instance, in this data set, I'd want to find the date of first instance of 301373180000 (Sequence 10 on 1/3/2017) and the last instance it appears (sequence 80 on 1/19/2017).

I've thought about using a sequential SUMIF statement that counts the instance of the WO and then take the MAX and MIN values using a separate table where I copy out all the first instances of an order # using VLOOKUP or INDEX/MATCH but I'm not Excel savvy enough to make it work. Also, my data set is roughly 20,000 rows and there are a bunch more columns so Excel keeps crashing when I try to apply the formulas to the entire table. It's frustrating. Any help would be appreciated.

Thank you.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,297
Office Version
  1. 365
Platform
  1. Windows
I think this might help. It calculates the absolute difference between the first Sequence figure and the last, based on Order# and Date. Cell I2 does it all in a single cell.

<strike>
</strike>
​<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="background-color: #FFF2CC;;">Date</td><td style="background-color: #FFF2CC;;">Order#</td><td style="background-color: #FFF2CC;;">Sequence</td><td style="text-align: right;;"></td><td style="background-color: #FCE4D6;;">Order#</td><td style="background-color: #FCE4D6;;">First</td><td style="background-color: #FCE4D6;;">Last</td><td style="background-color: #FCE4D6;;">Delta</td><td style="background-color: #FCE4D6;;">Delta (single cell)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">3/15/17</td><td style=";">301330920000</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">301373180000</td><td style="text-align: right;background-color: #E2EFDA;;">2017-01-03</td><td style="text-align: right;background-color: #E2EFDA;;">2017-01-19</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #C6E0B4;;">70</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">12/13/18</td><td style=";">301358930000</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="background-color: #FCE4D6;;">Sequence</td><td style="text-align: right;background-color: #E2EFDA;;">10</td><td style="text-align: right;background-color: #E2EFDA;;">80</td><td style="text-align: right;background-color: #E2EFDA;;">70</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">12/13/18</td><td style=";">301358950000</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">1/09/17</td><td style=";">301371180000</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">1/31/17</td><td style=";">301371180000</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">1/31/17</td><td style=";">301371180000</td><td style="text-align: right;;">60</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">1/06/17</td><td style=";">301371790000</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">1/03/17</td><td style=";">301371980000</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">1/03/17</td><td style=";">301371980000</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">1/05/17</td><td style=";">301371980000</td><td style="text-align: right;;">60</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">1/05/17</td><td style=";">301371980000</td><td style="text-align: right;;">70</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">1/05/17</td><td style=";">301371980000</td><td style="text-align: right;;">80</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">1/06/17</td><td style=";">301372660000</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">1/06/17</td><td style=";">301372660000</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">1/06/17</td><td style=";">301372660000</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">1/09/17</td><td style=";">301372660000</td><td style="text-align: right;;">60</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">1/16/17</td><td style=";">301372660000</td><td style="text-align: right;;">70</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">1/03/17</td><td style=";">301373180000</td><td style="text-align: right;background-color: #FFFF00;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">1/11/17</td><td style=";">301373180000</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">1/11/17</td><td style=";">301373180000</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">1/16/17</td><td style=";">301373180000</td><td style="text-align: right;;">60</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;">1/17/17</td><td style=";">301373180000</td><td style="text-align: right;;">70</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;">1/19/17</td><td style=";">301373180000</td><td style="text-align: right;background-color: #FFFF00;;">80</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet60</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=ABS(<font color="Blue">SUM(<font color="Red">(<font color="Green">AGGREGATE(<font color="Purple">15,6,A2:A24/(<font color="Teal">$E$2=B2:B24</font>),1</font>)=A2:A24</font>)*(<font color="Green">$E$2=B2:B24</font>)*C2:C24</font>)-SUM(<font color="Red">(<font color="Green">AGGREGATE(<font color="Purple">14,6,A2:A24/(<font color="Teal">$E$2=B2:B24</font>),1</font>)=A2:A24</font>)*(<font color="Green">$E$2=B2:B24</font>)*C2:C24</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=AGGREGATE(<font color="Blue">15,6,A2:A24/(<font color="Red">$E$2=B2:B24</font>),1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=AGGREGATE(<font color="Blue">14,6,A2:A24/(<font color="Red">$E$2=B2:B24</font>),1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F3</th><td style="text-align:left">=SUM(<font color="Blue">(<font color="Red">AGGREGATE(<font color="Green">15,6,A2:A24/(<font color="Purple">$E$2=B2:B24</font>),1</font>)=A2:A24</font>)*(<font color="Red">$E$2=B2:B24</font>)*C2:C24</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G3</th><td style="text-align:left">=SUM(<font color="Blue">(<font color="Red">AGGREGATE(<font color="Green">14,6,A2:A24/(<font color="Purple">$E$2=B2:B24</font>),1</font>)=A2:A24</font>)*(<font color="Red">$E$2=B2:B24</font>)*C2:C24</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H3</th><td style="text-align:left">=ABS(<font color="Blue">F3-G3</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

tommy_b

New Member
Joined
Feb 10, 2016
Messages
4
DRSTeele, thank you for the response. I've never used AGGREGATE before and I'm looking forward to learning more about it.

I should have stated in my OP that I'm looking to take the delta between the date of the first and last sequence that shows up for each WO. I'm not sure how to parse out every discrete Order # into a separate table. My data set is currently at 32,000 rows (I'm using power query to bring a table in from our ERP system). Is there a way to easily grab each discrete order # and transcribe it to a new table? Or would it make sense to house the delta in a new column but only for the largest sequence value?

Ultimately, my goal will be to evaluate the average time for the orders to move through our processes.
 

tommy_b

New Member
Joined
Feb 10, 2016
Messages
4
Also, could you explain how the '/' is working in the reference parameter of the AGGREGATE function? It looks like you're dividing the entire column by a boolean expression. Is that correct or does the '=' do something else in an excel function?

Thank you.
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,297
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You can extract a list of unique orders numbers many ways, including PQ, PivotTables, native filter operations, or the easiest way: by using the new spiller functions in Excel365. In our case,

Code:
[FONT=Verdana]=UNIQUE(B2:B24)[/FONT]

See here and related videos in the MrExcel channel for explanations of Excels new functions. https://www.youtube.com/watch?v=vy8y4CN-IHY

The "/" in function AGGREGATE is used to intentionally create errors in the array housed inside it. That way, determining the minimum value is not hindered by the zeros that would be created by a "*" multiplication operation. The key here is that AGGREGATE will ignore array errors when the argument 6 is used.

See this youtube video and others in that ExcelIsFun channel for insight into AGGREGATE. https://www.youtube.com/watch?v=u7B__903dUQ&t=1s&list=LL0pw634NtPT66QP2LcA6YRA&index=79
 
Last edited:

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,297
Office Version
  1. 365
Platform
  1. Windows
You're welcome...glad to have helped.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,834
Members
409,839
Latest member
akashsadhu
Top