Find the earliest order that was open each week

[TwinJohnson]

I need to get a snapshot to find the earliest order date that was still open for each week.

First I assigned the week ending that coorelates with the order date then I located the earliest order date by manually reviewing each week if the order was still open or not and if not then I pulled the earliest order date open that week. (column BI-TB)

For example on Week Ending Date 01/05/2019 the earliest order # that was not closed prior to 01/05/2019 is either order 72787152, 72774457, 72774465, 772776825 or 77276833. Each of these orders had an order date of 01/02/2019 therefore the earliest order open during that time was 01/02/2019. These orders didn't close until March.

The next week is week ending 01/12/2019, the earliest order # that was not closed prior to 01/12/2019 is the same orders from above AND 72793176, 72793192,72793168 etc. (see column BE where week ending date = 01/12/2019). The earliest order still open during this period and prior is still 01/02/2019.

The earliest order still open doesn't change until week ending 01/26 in which order 72827594 is still open when recorded.

What is the best way to figure this out in Excel? I tried =IF(ISNUMBER(CLOSED DATE),MAX(IF(ORDER # COLUMN =ORDER # CELL A2,ORDER DATE WEEK ENDING)), 99/99/9999") but that returns the order date week ending and not the actual order date.

Thank you,
Tawnee

 

[Herakles]

I need to get a snapshot to find the earliest order date that was still open for each week.

First I assigned the week ending that coorelates with the order date then I located the earliest order date by manually reviewing each week if the order was still open or not and if not then I pulled the earliest order date open that week. (column BI-TB)

For example on Week Ending Date 01/05/2019 the earliest order # that was not closed prior to 01/05/2019 is either order 72787152, 72774457, 72774465, 772776825 or 77276833. Each of these orders had an order date of 01/02/2019 therefore the earliest order open during that time was 01/02/2019. These orders didn't close until March.

The next week is week ending 01/12/2019, the earliest order # that was not closed prior to 01/12/2019 is the same orders from above AND 72793176, 72793192,72793168 etc. (see column BE where week ending date = 01/12/2019). The earliest order still open during this period and prior is still 01/02/2019.

The earliest order still open doesn't change until week ending 01/26 in which order 72827594 is still open when recorded.

What is the best way to figure this out in Excel? I tried =IF(ISNUMBER(CLOSED DATE),MAX(IF(ORDER # COLUMN =ORDER # CELL A2,ORDER DATE WEEK ENDING)), 99/99/9999") but that returns the order date week ending and not the actual order date.

Thank you,
Tawnee

Can you please post a decent amount of data using XL2BB that members can use when working out a formula?

 

[TwinJohnson]

RAW Table

Results

On week ending:

01/05 order was open?

On 01/12 order was open?

On 01/19 order was open?

On 01/26 order was open?

On 02/02 order was open?

On 02/09 order was open?

On 02/16 order was open?

On 02/23 order was open?

On 03/02 order was open?

On 03/09 order was open?

On 03/16 order was open?

On 03/23 order was open?

Order #

Order Date

Closed Date

Date Table Week Ending Date

Results = Earliest Order Date

72787152

01/02/19

03/06/19

1/5/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

N

72774457

01/02/19

03/21/19

1/5/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

72774465

01/02/19

03/21/19

1/5/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

72776825

01/02/19

03/21/19

1/5/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

72776833

01/02/19

03/21/19

1/5/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

72793176

01/06/19

03/10/19

1/12/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

72793192

01/06/19

03/21/19

1/12/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

72793168

01/07/19

03/21/19

1/12/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

72796963

01/08/19

03/08/19

1/12/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

N

72805491

01/14/19

03/12/19

1/19/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

72800708

01/14/19

03/13/19

1/19/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

72804544

01/14/19

03/13/19

1/19/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

72805269

01/14/19

03/13/19

1/19/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

72802555

01/15/19

03/13/19

1/19/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

72809055

01/15/19

03/13/19

1/19/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

72808610

01/16/19

03/13/19

1/19/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

72809014

01/16/19

03/14/19

1/19/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

72810343

01/16/19

03/14/19

1/19/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

72816894

01/20/19

03/18/19

1/26/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

72819633

01/20/19

03/18/19

1/26/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

72819625

01/21/19

03/20/19

1/26/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

72819468

01/23/19

03/22/19

1/26/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

72819484

01/23/19

03/22/19

1/26/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

72822231

01/23/19

03/22/19

1/26/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

72822256

01/23/19

03/22/19

1/26/2019

1/2/2019

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

72827594

01/24/19

03/28/19

1/26/2019

1/24/2023

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

72822082

01/25/19

03/22/19

1/26/2019

1/24/2023

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

 

[Fluff]

@TwinJohnson please do not include multiple quotes of the same thing in your posts. I have removed them this time. Thanks

 

[TwinJohnson]

Can you please post a decent amount of data using XL2BB that members can use when working out a formula?

Thank you Herakles, I just learned about XL2BB for this forum and hope that I uploaded this correctly.
Regards,
Tawnee