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

[tommy_b]

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:

[TABLE="width: 334"]
<tbody>[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"]Order #[/TD]
[TD="align: center"]Sequence[/TD]
[/TR]
[TR]
[TD="align: right"]3/15/2017[/TD]
[TD="align: right"]301330920000[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2018[/TD]
[TD="align: right"]301358930000[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2018[/TD]
[TD="align: right"]301358950000[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1/9/2017[/TD]
[TD="align: right"]301371180000[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2017[/TD]
[TD="align: right"]301371180000[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2017[/TD]
[TD="align: right"]301371180000[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]301371790000[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD="align: right"]301371980000[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD="align: right"]301371980000[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]1/5/2017[/TD]
[TD="align: right"]301371980000[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]1/5/2017[/TD]
[TD="align: right"]301371980000[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD="align: right"]1/5/2017[/TD]
[TD="align: right"]301371980000[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]301372660000[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]301372660000[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]301372660000[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]1/9/2017[/TD]
[TD="align: right"]301372660000[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]1/16/2017[/TD]
[TD="align: right"]301372660000[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD="align: right"]301373180000[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2017[/TD]
[TD="align: right"]301373180000[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2017[/TD]
[TD="align: right"]301373180000[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]1/16/2017[/TD]
[TD="align: right"]301373180000[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]1/17/2017[/TD]
[TD="align: right"]301373180000[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD="align: right"]1/19/2017[/TD]
[TD="align: right"]301373180000[/TD]
[TD="align: right"]80[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[DRSteele]

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>}}​

Book1
	A	B	C	D	E	F	G	H	I
1	Date	Order#	Sequence		Order#	First	Last	Delta	Delta (single cell)
2	3/15/17	301330920000	20		301373180000	2017-01-03	2017-01-19		70
3	12/13/18	301358930000	10		Sequence	10	80	70
4	12/13/18	301358950000	10
5	1/09/17	301371180000	10
6	1/31/17	301371180000	20
7	1/31/17	301371180000	60
8	1/06/17	301371790000	40
9	1/03/17	301371980000	40
10	1/03/17	301371980000	50
11	1/05/17	301371980000	60
12	1/05/17	301371980000	70
13	1/05/17	301371980000	80
14	1/06/17	301372660000	10
15	1/06/17	301372660000	40
16	1/06/17	301372660000	50
17	1/09/17	301372660000	60
18	1/16/17	301372660000	70
19	1/03/17	301373180000	10
20	1/11/17	301373180000	40
21	1/11/17	301373180000	50
22	1/16/17	301373180000	60
23	1/17/17	301373180000	70
24	1/19/17	301373180000	80
Sheet60

Cell Formulas
Range	Formula
I2	=ABS(SUM((AGGREGATE(15,6,A2:A24/($E$2=B2:B24),1)=A2:A24)*($E$2=B2:B24)*C2:C24)-SUM((AGGREGATE(14,6,A2:A24/($E$2=B2:B24),1)=A2:A24)*($E$2=B2:B24)*C2:C24))
F2	=AGGREGATE(15,6,A2:A24/($E$2=B2:B24),1)
F3	=SUM((AGGREGATE(15,6,A2:A24/($E$2=B2:B24),1)=A2:A24)*($E$2=B2:B24)*C2:C24)
G2	=AGGREGATE(14,6,A2:A24/($E$2=B2:B24),1)
G3	=SUM((AGGREGATE(14,6,A2:A24/($E$2=B2:B24),1)=A2:A24)*($E$2=B2:B24)*C2:C24)
H3	=ABS(F3-G3)

 

[tommy_b]

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]

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]

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,

[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

 

[tommy_b]

Thank you. I really appreciate the help as well as the links.

 

[DRSteele]

You're welcome...glad to have helped.