Show result from other columns (without knowing which of the 4 columns has value in it)

feni1388

Board Regular
Joined
Feb 19, 2018
Messages
78
Office Version
  1. 2021
Platform
  1. Windows
Hello,

I have a table in which the result from the other column is shown here (column I).
Actually there is supposed to be only one of the 4 columns (M to P) that show the result. But I'm just worried that there'll be 2 of the columns show a value in it.
If that's the case, the formula that I'm using won't be able to show the result from both columns.
Have anyone any idea what kind of function I can use in the situation like this?

Thank you in advance.

check.xlsx
ABCDEFGHIJKLMNOP
1Invoice NoCustomer codeCustomer nameCustomer addressQtyTotal weightwarehouse+code (manual)warehouse+code (system)CheckPICCond. 1Cond. 2Cond. 3Cond. 4
2866771 totalSBD03031AAAA276.54043checkJohncheckdelivery code check  
3866772 totalSBD01T78BBBB594.44040 John    
4866773 totalASG02007CCCC232.34343 John    
5866774 totalTNS07030DDDD118.34343 John    
6866775 totalTNS07038EEEE466.74040 John    
7866776 totalTNS07035FFFF236.64343 John    
8866777 totalNPK01072GGGG116.24343 John    
9866778 totalSBD01ABMHHHH687.34040 John    
10866779 totalSBD01Q49IIII12145.04040 John    
11866780 totalOZX01305JJJJ459.34040 John    
12866781 totalOZX01034KKKK114.84343 John    
13866782 totalOZX01042LLLL574.94040 John    
14866783 totalOZX01120MMMM229.84343 John    
15866784 totalOZX01254NNNN459.34040 John    
16866785 totalOZX01210OOOO229.94343 John    
17866950 totalFPC01005PPPP585.94343PPPJohn  PPP 
Sheet1
Cell Formulas
RangeFormula
M2:M17M2=IF(J2="","",IF(G2<>H2,"check",""))
N2:N17N2=IF(J2="","",IF(AND(E2<=2,VALUE(RIGHT(G2,1))=0),"delivery code check",""))
O2:O17O2=IF(J2="","",IF(AND(E2>=4,VALUE(RIGHT(G2,1))<>0),LEFT(D2,3),""))
P2:P17P2=IF(J2="","",IF(AND(G2=90,E2<=2),"warehouse check",""))
I2:I17I2=IF(M2<>"",M2,IF(N2<>"",N2,IF(O2<>"",O2,IF(P2<>"",P2,""))))
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
will that =TEXTJOIN(", ",,M2,N2,O2,P2) in I2 works?
 
Upvote 0
Solution
A slightly shorter option
Excel Formula:
=TEXTJOIN(", ",,M2:P2)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

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