0

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    |
  • Could you include a simple example of the table, and perhaps the expected output as well? – jagrg Mar 21 at 13:37
  • Are the original table and the expected result right? (I corrected some formatting problems in the original table and added an expected results table.) – Tobias Mar 21 at 13:52
2

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) |
  • Here both the sides contain foo and bar. That means we have provided them service and billed. I have to find person whom we have provided service but have forgotten to bill. – Vaibhav Mar 21 at 14:31
  • @Vaibhav Both versions are valid answers. The question leaves that detail open. – Tobias Mar 21 at 14:52
  • Thanks. I am greatful to you – Vaibhav Mar 21 at 14:53
0

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      |          |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.