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:
<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.
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:
Date | Order # | Sequence |
3/15/2017 | 301330920000 | 20 |
12/13/2018 | 301358930000 | 10 |
12/13/2018 | 301358950000 | 10 |
1/9/2017 | 301371180000 | 10 |
1/31/2017 | 301371180000 | 20 |
1/31/2017 | 301371180000 | 60 |
1/6/2017 | 301371790000 | 40 |
1/3/2017 | 301371980000 | 40 |
1/3/2017 | 301371980000 | 50 |
1/5/2017 | 301371980000 | 60 |
1/5/2017 | 301371980000 | 70 |
1/5/2017 | 301371980000 | 80 |
1/6/2017 | 301372660000 | 10 |
1/6/2017 | 301372660000 | 40 |
1/6/2017 | 301372660000 | 50 |
1/9/2017 | 301372660000 | 60 |
1/16/2017 | 301372660000 | 70 |
1/3/2017 | 301373180000 | 10 |
1/11/2017 | 301373180000 | 40 |
1/11/2017 | 301373180000 | 50 |
1/16/2017 | 301373180000 | 60 |
1/17/2017 | 301373180000 | 70 |
1/19/2017 | 301373180000 | 80 |
<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.