Matching two columns in a table

Question

I deal with a two column table where I have to match data from two sources. The first column contains names of persons for whom, service is provided. The second, persons who are billed. My job is to match them at the end of day and find out descripancy, if any. Is there a way to automate this, so I just have a list of mismatched names with couple of clicks. Regards.

|column 1| column2|
|--------|--------|
| abc    | xyz    |
| xyz    | abc    |
| CSR    | CSR    |

Expected result:

|column 1| column2|
|--------|--------|
| abc    | xyz    |
| xyz    | abc    |

Answer 1

1st Version: Each row represents a separate service

Separate services can cost differently. So the first entry of each row must match the second entry of that row.

The org code below has a source code block DeleteDiagonal that deletes rows with equal entries in the first and second column.

#+NAME: ServiceBilling
| column 1 | column2 |
|----------+---------|
| abc      | xyz     |
| xyz      | abc     |
| CSR      | CSR     |

#+NAME: DeleteDiagonal
#+BEGIN_SRC emacs-lisp :var tab=ServiceBilling :colnames nil
(cl-loop for row in tab
     unless (string-equal (nth 0 row) (nth 1 row))
     collect row)
#+END_SRC

#+NAME: ServiceBillingMismatch
#+RESULTS: DeleteDiagonal
| column 1 | column2 |
|----------+---------|
| abc      | xyz     |
| xyz      | abc     |

You can also reuse that code block with ~#+CALL~:

#+NAME: ServiceBilling2
| service provided | billed         |
|------------------+----------------|
| foo              | bar            |
| fine gentleman   | fine gentleman |
| fox              | rabbit         |
| bar              | fox            |
| rabbit           | foo            |

#+CALL: DeleteDiagonal(tab=ServiceBilling2)

#+RESULTS:
| service provided | billed |
|------------------+--------|
| foo              | bar    |
| fox              | rabbit |
| bar              | fox    |
| rabbit           | foo    |

2nd Version: 1st and 2nd col present sets of served and billed customers, resp.

In this case the two columns are unrelated. They first column represents the set of served persons and the second one the set of billed persons.

#+NAME: ServiceBilling2
| service provided | billed         |
|------------------+----------------|
| foo              | bar            |
| fine gentleman   | fine gentleman |
| fox              | rabbit         |
| bar              | fox            |
| rabbit           | foo            |
| foobar           | nobody         |
| robber           | nil            |
| nil              | sponsor        |

#+BEGIN_SRC emacs-lisp :var tab=ServiceBilling2
(let ((served (mapcar #'first tab))
      (billed (mapcar #'second tab)))
  (list
   (cons "Served Not Billed" (list (cl-set-difference served billed :test #'string-equal)))
   (cons "Billed Not Served" (list (cl-set-difference billed served :test #'string-equal)))))
#+END_SRC

#+RESULTS:
| Served Not Billed | (foobar robber)  |
| Billed Not Served | (nobody sponsor) |

Answer 2

There is another posibility:

First: install orgtbl-join through MELPA.
Then name one of the tables, it will be the reference table (I also renamed columns to make it a better example).

 #+NAME: t1
|column a| column b|
|--------|---------|
| abc    | xyz     |
| xyz    | abc     |
| CSR    | CSR     |

Then put your Point into the second table at a reference column (I choosed column 1; '[]' is the Point)

| column 1 | column 2 |
|----------+---------|
| abc  []  | xyz     |
| xyz      | abc     |

Then call M-x orgtbl-join.
At the prompt for reference table, enter t1 (which is the reference table).
At the prompt for Reference column, enter column 1 (which is our choosen reference column from t1).

You will end up with the second table modified like this:

| column 1 | column 2 | column b |
|----------+---------+-----------|
| abc      | xyz     | xyz       |
| xyz      | abc     | abc       |

---

When you take the second table as reference, and join it to the first table you will get this result:

| column a | column b | column 2 |
|----------+----------+----------|
| abc      | xyz      | xyz      |
| xyz      | abc      | abc      |
| CSR      | CSR      |          |