• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • Last Modified:

comparing 2 columns

I have got a set of data returned from a query – exported to excel. I have 2 rows of data I need to “compare”, they are called invoice_num and invoice_ID. If a unique invoice_num appears in 20 consecutive rows – there should also be a unique invoice_ID which should also appear in the same 20 rows.

invoice_num             invoice_id
12345                  34567
12345                  34567
12345                  34567
55555                  99999
55555                  99999
12348                  50006
12348                  50006

As it stands invoice_num is in column A and invoice_ID is in column B. I presume an additioanl column i.e. Z that has some sort of summary would be fine. An exception would be if you have unique invoice_num but different invoice_ID on different rows, and vice versa.
3 Solutions
Randy PooleCommented:
Not quite sure what you are asking for here. Can you give an example

pls try

=IF(SUM(IF(FREQUENCY(($A$2:$A$8=A2)*($B$2:$B$8),($A$2:$A$8=A2)*($B$2:$B$8))>0,1))-2+SUM(IF(FREQUENCY(($B$2:$B$8=B2)*($A$2:$A$8),($B$2:$B$8=B2)*($A$2:$A$8))>0,1))-2,"Not OK","OK")

Rob HensonFinance AnalystCommented:
Assuming data in columns A & B, the following formula in column C will give blank for first occurence of Invoice or TRUE or FALSE depending whether the Invoice ID matches the first occurence:


Copy into C2 and then copy down. The lookup range will extend as you copy down. You can then filter for FALSE to get those that don't match.

Rob H
If this is an ongoing requirement you could make a table out of the two columns and create two linked pivot tables, one with column A left-most row and one with column B left-most row ( in traditional pivot table format) and the abnormal combinations will visually stand out.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now