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!
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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?
 

zb134

New Member
Joined
Jun 23, 2014
Messages
38
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.
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,913
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top