Flag Backorder Shipments

[LVExcel]

Hi Everyone, I'm trying to get a cleaner formula to label any records shipped as backorder.

Here's my formula, but know there has to be something cleaner.

=IF(AND(B2=B1,C2<>C1),"Backorder",IF(AND(A1="Backorder",B2=B1,C2=C1),A1,"Regular"))

Formula: if Order# is the same as previous Order#, and ShipDate is not the same as previous ShipDate, then it's a Backorder. Also, if previous record was labeled "Backorder", and Order# is the same as previous Order#, and ShipDate is the same as previous ShipDate, then label it as previous record, "Backorder", if not label record as "Regular".

Of course, I first have to sort by Primary: Order#, Secondary: ShipDate

Backorder	Order #	Ship Date
Regular	128141841	9/21/2017
Regular	128141841	9/21/2017
Regular	128141841	9/21/2017
Regular	128142410	1/10/2017
Backorder	128142410	3/1/2017
Backorder	128142410	8/31/2017
Regular	128142831	1/10/2017
Backorder	128142831	8/31/2017
Regular	128143223	1/10/2017
Backorder	128143223	8/31/2017
Regular	128143286	1/10/2017
Backorder	128143286	8/31/2017
Regular	128143478	1/10/2017
Backorder	128143478	8/31/2017
Regular	128143674	1/17/2017
Regular	128143770	1/10/2017
Backorder	128143770	8/31/2017

Thanks, I would appreciate any help you can provide.

 

[sheetspread]

You can try something like this also:

Book1
	A	B	C
1	Backorder	Order #	Ship Date
2	Regular	128141841	9/21/17
3	Regular	128141841	9/21/17
4	Regular	128141841	9/21/17
5	Regular	128142410	1/10/17
6	Backorder	128142410	3/1/17
7	Backorder	128142410	8/31/17
8	Regular	128142831	1/10/17
9	Backorder	128142831	8/31/17
10	Regular	128143223	1/10/17
11	Backorder	128143223	8/31/17
12	Regular	128143286	1/10/17
13	Backorder	128143286	8/31/17
14	Regular	128143478	1/10/17
15	Backorder	128143478	8/31/17
16	Regular	128143674	1/17/17
17	Regular	128143770	1/10/17
18	Backorder	128143770	8/31/17
Sheet9

Cell Formulas
Range	Formula
A2:A18	A2	=IF(MIN(IF($B$2:$B$18=B2,$C$2:$C$18))=C2,"Regular","Backorder")
Press CTRL+SHIFT+ENTER to enter array formulas.

There's a minifs function on newer Excel versions (2016 - ?)

 

[LVExcel]

Thanks a lot Sheetspread, works great and much more professional. I appreciate it.

You can try something like this also:

Book1
	A	B	C
1	Backorder	Order #	Ship Date
2	Regular	128141841	9/21/17
3	Regular	128141841	9/21/17
4	Regular	128141841	9/21/17
5	Regular	128142410	1/10/17
6	Backorder	128142410	3/1/17
7	Backorder	128142410	8/31/17
8	Regular	128142831	1/10/17
9	Backorder	128142831	8/31/17
10	Regular	128143223	1/10/17
11	Backorder	128143223	8/31/17
12	Regular	128143286	1/10/17
13	Backorder	128143286	8/31/17
14	Regular	128143478	1/10/17
15	Backorder	128143478	8/31/17
16	Regular	128143674	1/17/17
17	Regular	128143770	1/10/17
18	Backorder	128143770	8/31/17
Sheet9

Cell Formulas
Range	Formula
A2:A18	A2	=IF(MIN(IF($B$2:$B$18=B2,$C$2:$C$18))=C2,"Regular","Backorder")
Press CTRL+SHIFT+ENTER to enter array formulas.

There's a minifs function on newer Excel versions (2016 - ?)

 

[LVExcel]

Hi Sheetspread, just to get this right, with your formula I wont need to Sort my data by ShipDate first, since it's looking for the Min Date to label as "Regular"?

 

[sheetspread]

The array formula still worked after randomly sorting your data:

Book1
	A	B	C	D	E	F
1	Backorder	Order #	Ship Date	sort
2	Regular	128142831	1/10/17	0.0425674	Regular	Regular
3	Backorder	128142831	8/31/17	0.057551	Backorder	Backorder
4	Backorder	128143770	8/31/17	0.1382673	Regular	Backorder
5	Regular	128141841	9/21/17	0.2513477	Regular	Regular
6	Backorder	128143223	8/31/17	0.2615502	Regular	Backorder
7	Backorder	128143286	8/31/17	0.2793757	Regular	Backorder
8	Regular	128143223	1/10/17	0.3141159	Regular	Regular
9	Backorder	128142410	8/31/17	0.5895387	Regular	Backorder
10	Regular	128143286	1/10/17	0.5990073	Regular	Regular
11	Regular	128143674	1/17/17	0.603248	Regular	Regular
12	Regular	128141841	9/21/17	0.648918	Regular	Regular
13	Backorder	128143478	8/31/17	0.8076012	Regular	Backorder
14	Regular	128141841	9/21/17	0.8228764	Regular	Regular
15	Regular	128143478	1/10/17	0.8554872	Regular	Regular
16	Regular	128142410	1/10/17	0.8933647	Regular	Regular
17	Regular	128143770	1/10/17	0.9037478	Regular	Regular
18	Backorder	128142410	3/1/17	0.9334438	Regular	Backorder
Sheet9 (2)

Cell Formulas
Range	Formula
E2:E18	E2	=IF(AND(B2=B1,C2<>C1),"Backorder",IF(AND(A1="Backorder",B2=B1,C2=C1),A1,"Regular"))
F2:F18	F2	=IF(MIN(IF($B$2:$B$18=B2,$C$2:$C$18))=C2,"Regular","Backorder")
Press CTRL+SHIFT+ENTER to enter array formulas.