Find incident

[Tara_Simphson]

Hi,
I have a table with 3 different sets of values. First is lot number of a product(A:A), second is order number(B:) and the third one is the container id(C:C). Orders are shipped in 10-15 containers with multiple products(lot numbers). I need to find out how many times the same lot number in the same order ended up in a different container. What formula should I use to show the frequency of incident happening? Thank you in advance!!

[#2]

@Tara_Simphson so you can create another column and use something like:

=COUNTIFS(A:A,A1,B:B,B1)

and fill down.  Then any row with a value >1 has a duplicate.  This is useful so can also easily find them and know how many duplicates of that value exist.

That said, I recommend not using A:A if possible.  Ideally your data should be formatted as a table and then use table notation like:

=COUNTIFS([order number],[@[order number]],[id],[@id])

if you don't want that extra column and just the number and have excel365 then you could use something like

=ROWS(UNIQUE(B1:C100,FALSE,TRUE))

to count how many unique rows there are and divide by total number of rows or subtract from total number of rows or subtract and then divide depending on exactly what you want/need. 

[#3]

Hi,
Thank you for your help however this didn’t show needed results….

[#4]

@Tara_Simphson 

Where does the information in your worksheet come from, the packing slip? or shipping information?, and how can you tell from the packing slip or shipping information that the same lot number in the same order ended up in a different container. or do you get that information from the receiving people? then compare what they accounted for against what's on the paper work?