OK, I’ve got a spreadsheet which includes a list of serial numbers in one column, and which “group number” they fall into listed in a second column. Each group number will have several serial numbers listed as part of it, and serial numbers can occur more than once in a group.
Now, although serial numbers can be listed multiple times, they are only supposed to be part of one group. Although they’d appear more than once on the spreadsheet, the group should be the same each time they appear. There are however, mistakes on the list, where serial numbers are listed in two (or more) groups, and I’m trying to search for or separate out these numbers when they occur.
Any suggestions on how to accomplish this? I had tried using countif on the serial number column, but since they can sometimes appear multiple times and not necessarily be mistakes (as long as group is always the same), this doesn’t list sure mistakes, only possible mistakes. Anyone have an idea about a different way about going at this?
If I’m understanding you correctly, here’s how I’d do it:
Sort by serial number (call it col A) then by group number (call it col B). Add a new col C and in cell c2 write the following formula:
=if(a2<>a1,"",if(b2=b1,"","!!!"))
Copy that formula down the table to the last line. Any rows where the serial number does match the one in the row above it but the group number doesn’t match (same serial, different group) will be indicated by “!!!” in col C.
This works but only only if the data is sorted by serial number. If the OP can indeed sort the data this would work very nicely.
If the data cannot be sorted, then here is a generalized solution for detecting if a value in column 1 is matched with more than one value in column 2, regardless of sorting. Add a third column; in row 1 it would read
=CONCATENATE(A1,B1)
This creates a merged serial number + group number.
This compares the number of times that the serial number appears against the number of times it appears with this particular group number. They should be the same, otherwise it means the same serial number is matched to at least one other group on at least one other line. (There is a small bug here, which is the case where two unique serial numbers paired with two unique group numbers are the same when merged, like 123+45 and 12+345. But usually serial numbers have the same number of digits.)
Copy the formulas from C1 and D1 into all data rows.
If the serial number is contained in more than one group, you will see the message MULTIPLE GROUPS, otherwise nothing.