Some help needed with comparieson (Formula NOT VBA)

zb134

New Member
Joined
Jun 23, 2014
Messages
38
Consider a table like shown
Shipment No.CompanyMatch
s1aexception
s2b
s3aexception
s2b

<tbody>
</tbody>

What I want to do is in the 'Match' column, check if the same company has the same shipment number or not. The actual record has approx 300 values.

In the above table, since company 'a' has two shipment numbers s1 and s3, I want it to be shown as an exception in the match column while company 'b' has the same shipment number so it passes.

Thanks a lot!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Will there only be two instances of a company? Or can there be thirty instances? If there are thirty, do all thirty have to be S1 (or similar) to not throw an exception?
 
Upvote 0
Will there only be two instances of a company? Or can there be thirty instances? If there are thirty, do all thirty have to be S1 (or similar) to not throw an exception?

There could be multiple instance of a company, what I have omitted is other columns like product code etc. But yes company A could be repeated 30 times and if even on of them is not the same shipment number, I want an exception for each corresponding col with the company. Hope that is clear.
 
Upvote 0
Code:
=IF(COUNTIF($B$2:$B$5,$B2)=COUNTIFS($B$2:$B$5,$B2,$A$2:$A$5,$A2),"","exception")

Simple enough. Column A is your Shipment No. and column B is your Company.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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