Compare cell values in two different columns for mismatches

**maacmaac** · 02-10-2012, 05:01 PM

In column A is a list of Invoice Numbers
In column B is a list of Client ID Numbers

Each unique invoice number in column A should correspond to the same Client ID Number in Column B.

I am trying to find a formula that would find all instances in which any invoice number is assigned to two or more different client IDs.

For example, in column A, I have five instances of invoice number 125000. For two of the 5, the client ID number is 700. For the other three, the client ID is 55005500. I want to be able to identify cases when this occurs.

I have attached a sample of data with expected results in column E. Thanks in advance for any comments.

**Pete_UK** · 02-10-2012, 08:53 PM

In the attached workbook I've used 3 helper columns in this solution - the first in column G joins together the Invoice number and the client number using this formula:

=A2&"_"&B2

The next column (H) reduces this to a unique list using this array formula:

=IF(ISNA(INDEX($G$2:$G$28, MATCH(0, COUNTIF($H$1:H1, $G$2:$G$28), 0))), "", INDEX($G$2:$G$28, MATCH(0, COUNTIF($H$1:H1, $G$2:$G$28), 0)))

The final helper column counts the occurrences of the invoice number within the unique list using this formula:

=IF(H2="","",COUNTIF(H:H,LEFT(H2,SEARCH("_",H2))&"*"))

All three formulae have been copied down to the bottom of your data (row 28), and I've coloured the cells pale yellow so you can see this.

Then this formula in E2 gives you what you wanted:

=IF(VLOOKUP(A2&"_"&B2,H:I,2,0)>1,"No match","match")

Again, this is copied down. If you have more data in reality, then the G$28 in the second formula would need to be changed (4 times).

Hope this helps.

Pete

**maacmaac** · 02-10-2012, 11:29 PM

It works fine except it takes a really long time to run. I should have mentioned I am running this using excel 2010 and have almost 100,000 lines of data I need to analyze. Would it be possible to actually do this using VBA to shorten the time to process? Thanks