Excel Functions: Condition Based

J

jajatidev

Board Regular
Joined
Jul 29, 2016
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Need assistance on the following logic

Complete Delivery - N
Each line item within the order is treated independently. So if one line item in the order has a block then the remaining lines can be shipped if they don't have blocks.

Complete Delivery - Y
The order is treated as a whole. If a line item has a block then it needs to be removed for the order to ship.

The BlockCode column provides information on whether the line item is blocked or not.

The logic I'm looking for is;
If the order is Complete Delivery - N and the order line has a block then under the OrderIsBlocked column I need the comment "OrderLineIsBlocked"
Example: Order No# 0113987750 has 3 lines of which 1 is blocked. "OrderLineIsBlocked" should appear against the line while for the other two lines the field under OrderIsBlocked is blank.

If the order is Complete Delivery - Y and one of the order lines are blocked then under the OrderIsBlocked column I need the comment "OrderIsBlocked"
Example: Order No# 0110433907 has 2 lines with blocks. Since its a Delivery Complete - Y order all lines regardless will capture the comment "OrderIsBlocked"

Book1
BCDEFGH
1SalesOrderNoProductNoOrderQtyCompleteDeliveryUserStatusBlockCodeOrderIsBlocked
201129353801NR73A-K1H1NC2AP INC-VBAP-CEPOK INCO MDID NoMP RELINCO
30113430548A1A30A-KGJ1NBLCA C2AP EOL2 NoMP REL SCALEOL2 SCAL
401134937101NR73A-K1H1NC2AP INC-VBAP-CEPOK INCO MDID NoMP RELINCO
50113512547A1A30A-KGJ1NC2AP EOL2 NoMP REL SCALEOL2 SCAL
601135438135RA32F-KGJ1NC2AP MDID NoMP REL RRDCRRDC
70113561139A1A30A-KGJ1NEOL2 MDID NoMP REL SCALEOL2 SCAL
80113705782A1A77A-KGJ10NC2AP EOL2 MDID NoMP REL SCALEOL2 SCAL
90113725103N7L37A1NC2AP INC-VBAP-CEPOK INCO MDID NoMP RELINCO
1001139009462Z600F-KGJ1NC2AP NoMP REL SCALSCAL
1101139338933JA05AN-1501NC2AP EOL2 NoMP RELEOL2
120113969730A1A56A-KGJ1NC2AP EOL2 MDID NoMP REL SCALEOL2 SCAL
1301139877505RA32F-KGJ1NCOM-GLA NoMP REL
1401140021502Z613F-KGJ2NC2AP NoMP REL SCALSCAL
1501140021505RA32F-KGJ1NCOM-GLA NoMP REL
1601140034522Z600F-KGJ5NC2AP NoMP REL SCALSCAL
1701140051802Z600F-KGJ1NC2AP NoMP REL SCALSCAL
180114016353A1A77A-KGJ1NC2AP EOL2 MDID NoMP REL SCALEOL2 SCAL
1901101134333GZ25A-KGJ1YBLKD C2AP COM-GLA NoMP REL SCALSCAL
200110113433K5L35A1YBLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDCRRDC
2101101134333GZ25A-KGJ1YBLKD C2AP COM-GLA NoMP REL SCALSCAL
220110113433K5L35A1YBLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDCRRDC
230110113433Y3D03A1YBLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
240110113433Z7C07A1YBLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
2501101134333PZ35A-KGJ1YC2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOPXBOP
260110113433D7P27A1YC2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL
2701101134333PZ35A-KGJ1YC2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOPXBOP
280110113433D7P27A1YC2AP CFSL COM-FDL COM-GLA COM-PDD NoMP REL
2901104339073GZ25A-KGJ1YBLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOPSCAL XBOP
300110433907K5L35A1YBLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
310110433907Y3D03A1YBLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
320110433907Z7C07A1YBLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL
3301104339073GZ25A-KGJ1YBLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOPSCAL XBOP
Sheet1
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
jajatidev said:
Order No# 0113987750 has 3 lines of which 1 is blocked. "OrderLineIsBlocked" should appear against the line while for the other two lines the field under OrderIsBlocked is blank.
Click to expand...
Actually, that order# only appears once in the sample data you provided ;)

See if this comes close to what you're looking for. I note that almost every line in your sample data attracts the comment "OrderLineIsBlocked"

Book1
BCDEFGH
1SalesOrderNoProductNoOrderQtyCompleteDeliveryUserStatusBlockCodeOrderIsBlocked
21101134333GZ25A-KGJ1YBLKD C2AP COM-GLA NoMP REL SCALSCALOrderLineIsBlocked
3110113433K5L35A1YBLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDCRRDCOrderLineIsBlocked
41101134333GZ25A-KGJ1YBLKD C2AP COM-GLA NoMP REL SCALSCALOrderLineIsBlocked
5110113433K5L35A1YBLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP REL RRDCRRDCOrderLineIsBlocked
6110113433Y3D03A1YBLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP RELOrderLineIsBlocked
7110113433Z7C07A1YBLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP RELOrderLineIsBlocked
81101134333PZ35A-KGJ1YC2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOPXBOPOrderLineIsBlocked
9110113433D7P27A1YC2AP CFSL COM-FDL COM-GLA COM-PDD NoMP RELOrderLineIsBlocked
101101134333PZ35A-KGJ1YC2AP CFSL COM-FDL COM-GLA COM-PDD NoMP PREL REL XBOPXBOPOrderLineIsBlocked
11110113433D7P27A1YC2AP CFSL COM-FDL COM-GLA COM-PDD NoMP RELOrderLineIsBlocked
121104339073GZ25A-KGJ1YBLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOPSCAL XBOPOrderLineIsBlocked
13110433907K5L35A1YBLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP RELOrderLineIsBlocked
14110433907Y3D03A1YBLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP RELOrderLineIsBlocked
15110433907Z7C07A1YBLCA BLKD C2AP CFSL COM-ATP COM-FDL COM-GLA NoMP RELOrderLineIsBlocked
161104339073GZ25A-KGJ1YBLCA BLKD C2AP COM-GLA NoMP REL SCAL XBOPSCAL XBOPOrderLineIsBlocked
171129353801NR73A-K1H1NC2AP INC-VBAP-CEPOK INCO MDID NoMP RELINCOOrderLineIsBlocked
18113430548A1A30A-KGJ1NBLCA C2AP EOL2 NoMP REL SCALEOL2 SCALOrderLineIsBlocked
191134937101NR73A-K1H1NC2AP INC-VBAP-CEPOK INCO MDID NoMP RELINCOOrderLineIsBlocked
20113512547A1A30A-KGJ1NC2AP EOL2 NoMP REL SCALEOL2 SCALOrderLineIsBlocked
211135438135RA32F-KGJ1NC2AP MDID NoMP REL RRDCRRDCOrderLineIsBlocked
22113561139A1A30A-KGJ1NEOL2 MDID NoMP REL SCALEOL2 SCALOrderLineIsBlocked
23113705782A1A77A-KGJ10NC2AP EOL2 MDID NoMP REL SCALEOL2 SCALOrderLineIsBlocked
24113725103N7L37A1NC2AP INC-VBAP-CEPOK INCO MDID NoMP RELINCOOrderLineIsBlocked
251139009462Z600F-KGJ1NC2AP NoMP REL SCALSCALOrderLineIsBlocked
261139338933JA05AN-1501NC2AP EOL2 NoMP RELEOL2OrderLineIsBlocked
27113969730A1A56A-KGJ1NC2AP EOL2 MDID NoMP REL SCALEOL2 SCALOrderLineIsBlocked
281139877505RA32F-KGJ1NCOM-GLA NoMP REL 
291140021502Z613F-KGJ2NC2AP NoMP REL SCALSCALOrderLineIsBlocked
301140021505RA32F-KGJ1NCOM-GLA NoMP REL 
311140034522Z600F-KGJ5NC2AP NoMP REL SCALSCALOrderLineIsBlocked
321140051802Z600F-KGJ1NC2AP NoMP REL SCALSCALOrderLineIsBlocked
33114016353A1A77A-KGJ1NC2AP EOL2 MDID NoMP REL SCALEOL2 SCALOrderLineIsBlocked
Sheet1
Cell Formulas
RangeFormula
H2:H33H2=IF(AND(E2="N",G2<>""),"OrderLineIsBlocked",IF(AND(E2="Y",COUNTIFS($B$2:$B$33,B2,$G$2:$G$33,"<>")>0),"OrderLineIsBlocked",""))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

J
  • Solved
Index function to search a string and retain multiple words with reference to another table
2
Replies
11
Views
630
Fluff
Fluff
J
  • Solved
Need assistance with Power Query
2
Replies
11
Views
660
smozgur
smozgur

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top