org-mode-ox-odt icon indicating copy to clipboard operation
org-mode-ox-odt copied to clipboard

Published 20 hours ago • verified publisher icon kjambunathan

Support for VLOOKUP

Open kjambunathan opened this issue 1 year ago • 4 comments

@ouboub, this space is intentionally left blank so that you could fill it in.

Avoid stuff like identity (the `Org' info doesn't mention it) which are concoctions known only to the original author or those who were conversing with him when he was active.

We shouldn't focus on what we do NOT understand or need.

Feel free to fill in the details. I will handle this bug, once the remote() is handled.

kjambunathan avatar Oct 07 '23 10:10 kjambunathan

I made some progress in understading both the ODS and the org side.

ODS Side

I start with the ods side. There are the following possibilities to link tables that are located on different sheets

  1. A very general solution provided to me by Remy Gauthier. Currently I found this too complicated to implement, I also don't think I will use it in the foreseeable future. Nevertheless you find this solution attached.
  2. vslookup solution (you find the relevant solution attached)
  3. an even simpler solution by static linking the cells (included in the same attached ods file)

To cite Remy

  1. My solution used =SUMIF()- which allows for multiple entries of, for example, Smith in the same table. (That is the solution I send you privately)

  2. VLOOKUP() works perfectly for your current setup. It will, however, only return the first occurrence of Smith if there are many. This is therefore something to consider if your table expands in the future (perhaps by adding a new column for date references).

  3. Your solution is indeed the most simple, but it will become difficult to maintain if the names in the various tables are not in the same order or if the names are different from table to table. I usually prefer using search functions (like MATCH(), VLOOKUP(), or SUMIF()) because they remove the need of maintaining the formula references if there are insertions or deletions in the source data. They will also allow you to present your summary table in a different order by only changing the content of the cells in column "A".

Org side

There are at least solutions, thanks to Bruno Barbier

the org-lookup-first solution

#+Name: Table1
| Name     | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResS |
|----------+-----+-----+-----+-----+-----+------|
| Smith    |   2 |   3 |   4 |   6 |   7 |   22 |
| Brown    |   7 |  10 |   3 |   4 |  12 |   36 |
| Graham   |   4 |   5 |   1 |  10 |   7 |   27 |
| Jones    |  14 |   9 |   1 |   8 |  13 |   45 |
| Thompson |  14 |   4 |  11 |   9 |  11 |   49 |
| Walker   |   3 |  11 |   5 |   3 |   6 |   28 |
#+TBLFM: $7=vsum($2..$6);f2

#+Name: Table2
| Name     | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResS |
|----------+-----+-----+-----+-----+-----+------|
| Smith    |   1 |   3 |   5 |   8 |   9 |   26 |
| Brown    |  14 |   9 |  10 |   3 |   8 |   44 |
| Graham   |   5 |  14 |   1 |   5 |   6 |   31 |
| Jones    |  11 |  11 |   3 |   1 |   9 |   35 |
| Thompson |   7 |  10 |   3 |   9 |   5 |   34 |
| Walker   |   2 |   3 |   3 |  10 |   2 |   20 |
#+TBLFM: $7=vsum($2..$6);f2


#+Name: Final
| Name     | Table1 | Table2 | Total |
|----------+--------+--------+-------|
| Smith    |     22 |     26 |    48 |
| Brown    |     36 |     44 |    80 |
| Graham   |     27 |     31 |    58 |
| Jones    |     45 |     35 |    80 |
| Thompson |     49 |     34 |    83 |
| Walker   |     28 |     20 |    48 |
#+TBLFM: $2='(org-lookup-first $1 '(remote(Table1, @I$1..@II$1)) '(remote(Table1, @I$7..@II$7)))::$3='(org-lookup-first $1 '(remote(Table2, @I$1..@II$1)) '(remote(Table2, @I$7..@II$7)))::$4=vsum($2..$3);f2

The pure remote solution

#+Name: Final2
| Name     | Table1 | Table2 | Total |
|----------+--------+--------+-------|
| Smith    |     22 |     26 |    48 |
| Brown    |     36 |     44 |    80 |
| Graham   |     27 |     31 |    58 |
| Jones    |     45 |     35 |    80 |
| Thompson |     49 |     34 |    83 |
| Walker   |     28 |     20 |    48 |
#+TBLFM: $2=remote(Table1,@@#$7)::$3=remote(Table2,@@#$7)::$4=vsum($2..$3);f2

Or maybe safer


#+Name: Final3
| Name     | Table1 | Table2 | Total |
|----------+--------+--------+-------|
| Smith    |     22 |     26 |    48 |
| Brown    |     36 |     44 |    80 |
| Graham   |     27 |     31 |    58 |
| Jones    |     45 |     35 |    80 |
| Thompson |     49 |     34 |    83 |
| Walker   |     28 |     20 |    48 |
#+TBLFM: $4=vsum($2..$3);f2::@2$2..@>$2=remote(table1,@@#$7)::@2$3..@>$3=remote(table2,@@#$7)

To cite Bruno

"‘@#’ is substituted with the row number of the field where the current result will go to".

| Name     | Table1 | Table2 | Total |
|----------+--------+--------+-------|
| Smith    |     22 |     26 |    48 |
| Brown    |     36 |     44 |    80 |
| Graham   |     27 |     31 |    58 |
| Jones    |     45 |     35 |    80 |
| Thompson |     49 |     34 |    83 |
| Walker   |     28 |     20 |    48 |
#+TBLFM: $4=vsum($2..$3);f2::@2$2..@>$2=remote(table1,@@#$7)::@2$3..@>$3=remote(table2,@@#$7)

With this formula, your remote reference targets the field, that is in the same row number as the computed field in final.

org-lookup.txt SolutionRemyGauthier.ods

ouboub avatar Oct 09 '23 15:10 ouboub

SolutionRemyGauthier.ods

There is no one to one correspondence between the org file and the ods file.

I will just copy paste what I have gathered from the files, and I leave it to you to fill in the gaps ... gaps in my own response and the examples you have circulated.

Org file has these tables

Table1
Table2
Final
Final2
Final3

ODS has these tables and formula

(("Final" .
  ;; #+TBLFM: $2='(org-lookup-first $1 '(remote(Table1, @I$1..@II$1)) '(remote(Table1, @I$7..@II$7)))
  ;; =SUMIF($''.$A$4:$''.$A$10,CONCATENATE("=",$B4),$''.$G$4:$''.$G$10)
  "of:=SUMIF([$Table1.$A$4:$Table1.$A$10];CONCATENATE(\"=\";[.$B4]);[$Table1.$G$4:$Table1.$G$10])")
 ("Final (alternate)" .
  ;; #+TBLFM: $2=remote(Table1,@@#$7)
  ;; =SUMIF(OFFSET(INDEX(Table1,1,1),0,0,ROWS(Table1),1),CONCATENATE("=",$B4),OFFSET(INDEX(Table1,1,7),0,0,ROWS(Table1),1))
  "of:=SUMIF(OFFSET(INDEX(Table1;1;1);0;0;ROWS(Table1);1);CONCATENATE(\"=\";[.$B4]);OFFSET(INDEX(Table1;1;7);0;0;ROWS(Table1);1))")
 ("with-vslookup" .
  ;; This table doesn't exist in org snippet
  ;; =VLOOKUP(B4,Table1.$A$4:$G$10,7,FALSE())
  "of:=VLOOKUP([.B4];[Table1.$A$4:.$G$10];7;FALSE())")
 ("static" .
  ;; =$Table1.G5
  "of:=[$Table1.G5]"))

kjambunathan avatar Oct 11 '23 04:10 kjambunathan

There cannot be a one-to-one correspondence. The ods file contained 4 solutions

  1. 2 provided by remy that I just added because that might be of interest, if it confuses, I delete them
  2. 2 provided by me, using vslookup or static solutions

The org file contained three solutions

  1. one uses org-lookup-first and remote
  2. one only uses remote and the target syntax @@#$7 but I have split it.
    1. one using directly the column via $2=remote
    2. the other indicating range in the column via or @2$2..@>$2=remote

So I decided to reduce the ods example to two solutions, using vslookup and the static link and the org example I reduce also to two

  1. the org-lookup-first as a substitute for vslookup and
  2. remote alone to substitute the static link
  3. and just use the column without specifying its range.

So here is the org example

#+Name: Table1
| Name     | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResS |
|----------+-----+-----+-----+-----+-----+------|
| Smith    |   2 |   3 |   4 |   6 |   7 |   22 |
| Brown    |   7 |  10 |   3 |   4 |  12 |   36 |
| Graham   |   4 |   5 |   1 |  10 |   7 |   27 |
| Jones    |  14 |   9 |   1 |   8 |  13 |   45 |
| Thompson |  14 |   4 |  11 |   9 |  11 |   49 |
| Walker   |   3 |  11 |   5 |   3 |   6 |   28 |
#+TBLFM: $7=vsum($2..$6);f2

#+Name: Table2
| Name     | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResS |
|----------+-----+-----+-----+-----+-----+------|
| Smith    |   1 |   3 |   5 |   8 |   9 |   26 |
| Brown    |  14 |   9 |  10 |   3 |   8 |   44 |
| Graham   |   5 |  14 |   1 |   5 |   6 |   31 |
| Jones    |  11 |  11 |   3 |   1 |   9 |   35 |
| Thompson |   7 |  10 |   3 |   9 |   5 |   34 |
| Walker   |   2 |   3 |   3 |  10 |   2 |   20 |
#+TBLFM: $7=vsum($2..$6);f2


#+Name: Final1
| Name     | Table1 | Table2 | Total |
|----------+--------+--------+-------|
| Smith    |     22 |     26 |    48 |
| Brown    |     36 |     44 |    80 |
| Graham   |     27 |     31 |    58 |
| Jones    |     45 |     35 |    80 |
| Thompson |     49 |     34 |    83 |
| Walker   |     28 |     20 |    48 |
#+TBLFM: $2='(org-lookup-first $1 '(remote(Table1, @I$1..@II$1)) '(remote(Table1, @I$7..@II$7)))::$3='(org-lookup-first $1 '(remote(Table2, @I$1..@II$1)) '(remote(Table2, @I$7..@II$7)))::$4=vsum($2..$3);f2


#+Name: Final2
| Name     | Table1 | Table2 | Total |
|----------+--------+--------+-------|
| Smith    |     22 |     26 |    48 |
| Brown    |     36 |     44 |    80 |
| Graham   |     27 |     31 |    58 |
| Jones    |     45 |     35 |    80 |
| Thompson |     49 |     34 |    83 |
| Walker   |     28 |     20 |    48 |
#+TBLFM: $2=remote(Table1,@@#$7)::$3=remote(Table2,@@#$7)::$4=vsum($2..$3);f2

org-vslookup.txt

ods-vslookup.ods

ouboub avatar Oct 11 '23 12:10 ouboub

org-vslookup.txt

ods-vslookup.ods

Thanks. Looks good.

Notes to myself ...


  (("Final1" . "of:=VLOOKUP([.A2];[Table1.$A$1:.$G$7];7;FALSE())")
   ("Final2" . "of:=[$Table1.G2]"))

#+name: Final1
| Name     | Table1                                  | Table2                                  | Total       |
| Smith    | =VLOOKUP(A2,Table1.$A$1:$G$7,7,FALSE()) | =VLOOKUP(A2,Table2.$A$1:$G$7,7,FALSE()) | =SUM(B2:C2) |


#+name: Final2
| Name     | Table1      | Table2      | Result      |
| Smith    | =$Table1.G2 | =$Table2.G2 | =SUM(B2:C2) |


#+name: Table1
| Name     | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResS        |
| Smith    |   2 |   3 |   4 |   6 |   7 | =SUM(B2:F2) |


#+name: Table2
| Name     | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResS        |
| Smith    |   1 |   3 |   5 |   8 |   9 | =SUM(B2:F2) |

kjambunathan avatar Oct 13 '23 09:10 kjambunathan