Finding Duplicate Bin Locations

Question

I am trying to create a formula that will alert me when a part number has one (or more) duplicate bin locations assigned in an Excel spreadsheet.

Suppose that in box A1 I have part number 2839 1/4. (All the part numbers are listed in col. A)

In boxes B1 through E1 I have the following bin locations: B2-11F, 33-3, 49-1, 33-3.

As you can see, bin location 33-3 is mentioned twice by mistake. Such bin location should only be mentioned once.

Therefore I need a formula that will insert an "X" in column I1 alerting me that for that part number there is a duplicate bin location.

Depending on the part number, there is a minimum of one bin location assigned to it and a maximum of seven. So columns B-H are used for the bin locations, but not all columns are used for all the part numbers. For instance, the above example only uses four columns for the bin locations.

A part number could have no duplicate bin locations, or one or more duplicate bin locations.

I am hoping that somebody on this forum can help me.

Thanks.

Answer 1

Hi,

Try this formula in cell J2 and copy down

=IF(MAX(FILTER(COUNTIF(C2:I2,C2:I2),COUNTIF(C2:I2,C2:I2)<>0))>1,"Duplicate bin","")

Hope this helps.

Answer 2

You are welcome.

Answer 3

Thanks Ashish. Your solution seems to be working fine.

Answer 4

Hi Bernie.

Unfortunately the formula does not seem to work.

I am posting an excerpt of my Excel file to OneDrive.

The link is https://1drv.ms/x/s!AjsGdLUSZVE9gjLRsHr0MBjhqKFF?e=pHUV5q

You will see that I am getting "Duplicated Bin" even though there is no duplicate.

Thanks.

Answer 5

In I1, enter the formula

=IF(COUNTA(A1:H1)>COUNTA(FILTER(UNIQUE(A1:H1,TRUE),A1:H1<>"")),"Duplicated Bin","")

and copy down.