Comparing excel cell pairs in a column for matches or differences in another column

Question

My data consists of two columns of interest, A & B.

Each unique cell should have the same corresponding serial number i.e. each entry for KR0146U09A3 should have the same serial number which it does B150501388, I am happy to drop the A08 and A09 part of the string. Similarly KR0146U21A1 entries both have 0000a000K2008438a as it's serial number.

I am comfortable constructing if statements in excel for string matching but I can't understand how to construct the for loop to loop down through the list for each cell pair.

I guess the pseudocode for the task is: for each unique cell in column A check if the corresponding cells in B are the same.

Can someone provide me with a start as to how I could write a function to do this in excel?

Below is an example of my data:

Cell        Serial Number
KR0146U09A3 B150501388-A08
KR0146U09A3 B150501388-A09
KR0146U09A3 B150501388-A08
KR0146U09A3 B150501388-A09
KR0146U09B3 B150501395-A08
KR0146U09B3 B150501395-A08
KR0146U09B3 B150501395-A09
KR0146U09B3 B150501395-A09
KR0146U09C3 B150501391-A08
KR0146U09C3 B150501391-A08
KR0146U09C3 B150501391-A09
KR0146U09C3 B150501391-A09
KR0146U21A1 0000a000K2008438a
KR0146U21A1 0000a000K2008438a
KR0146U21A2 0000a000K2008438a
KR0146U21A2 0000a000K2008438a
KR0146U21A3 0000a000K2008438a
KR0146U21A3 0000a000K2008438a
KR0146U21B1 0000a000K2008447a
KR0146U21B1 0000a000K2008447a
KR0146U21B2 0000a000K2008447a
KR0146U21B2 0000a000K2008447a
KR0146U21B3 0000a000K2008447a
KR0146U21B3 0000a000K2008447a
KR0146U21C1 0000a000K2008404a
KR0146U21C1 0000a000K2008404a
KR0146U21C2 0000a000K2008404a
KR0146U21C2 0000a000K2008404a
KR0146U21C3 0000a000K2008404a
KR0146U21C3 0000a000K2008404a

Add a helper column with your truncated serial number, then use `COUNTIFS` to get the count of where the cells and truncated serial numbers match, and compare that to a `COUNTIF` of the number of Cells of the type. If the Counts are the same, they all match, if not, they don't. — Ron Rosenfeld, Sep 03 '16 at 11:32

score 2 · Answer 1 · answered Sep 03 '16 at 13:08

Copy your Serial Number column and parse that copied column with Text to Columns and - as the delimiter. Then pivot all the data within a Data Model with Cell for ROWS and Distinct Count of Serial Number2 for Values. Anything other than 1 would indicate a Cell with more than one Serial Number.

If you do not have Distinct Count, insert a PivotTable anyway with Cell above Serial Number2 for ROWS and Count of Serial Number2 for VALUES. Choose Show in Tabular Form for Report Layout and do not subtotal. In Field Settings... for Cell do not check Repeat item labels. Blanks in the Cells column should indicate where associated Serial Numbers are not unique for the Cell value.

Here's a link to a thread with screenshots showing how to enable `Distinct Count` [Simple Pivot Table to Count Unique Values](http://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-values) — Ron Rosenfeld, Sep 03 '16 at 13:58
Guys thank you both for responding to my post, I'm very grateful. — TheGoat, Sep 03 '16 at 18:57

Ron Rosenfeld · Accepted Answer · 2016-09-03T19:01:27.093

Expanding on my comment: Add a helper column with your truncated serial number, then use COUNTIFS to get the count of where the cells and truncated serial numbers match, and compare that to a COUNTIF of the number of Cells of the type. If the Counts are the same, they all match, if not, they don't.

Formula:

=COUNTIF(Cell,A2)=COUNTIFS(Cell,A2,TruncatedSN,D2)

EDIT: you can use this formula for the truncation

=IFERROR(LEFT(B2,FIND("-",B2)),B2)

Where Cell and TruncatedSN refer to the so-labelled columns below.

You can see where I changed the entry in B8 so as to cause a mismatch:

Comparing excel cell pairs in a column for matches or differences in another column

2 Answers2

Formula: