* equivalent of VLOOKUP (in ods) to org-table
@ 2023-10-08 12:58 Uwe Brauer
2023-10-08 16:42 ` Bruno Barbier
0 siblings, 1 reply; 11+ messages in thread
From: Uwe Brauer @ 2023-10-08 12:58 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 3422 bytes --]
Hi
I have regularly to deal with large tables, that I split in smaller ones and then try to connect them.
I have to export the resulting big table to ods, and in the ods case, I have several sheets and use the
VLOOKUP command, like this
#+begin_src
#+Name: table1
| Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
|--------+-----+-----+-----+-----+-----+--------|
| Smith | 2 | 3 | 4 | 6 | 7 | 22 |
| Miller | 2 | 10 | 1 | 1 | 5 | 19 |
| Wick | 1 | 2 | 3 | 10 | 2 | 18 |
#+TBLFM: $7=vsum($2..$6);f2
#+Name: table2
| Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh2 |
|--------+-----+-----+-----+-----+-----+--------|
| Smith | 8 | 3 | 5 | 8 | 9 | 33 |
| Miller | 2 | 1 | 6 | 9 | 3 | 21 |
| Wick | 1 | 5 | 9 | 1 | 2 | 18 |
#+TBLFM: $7=vsum($2..$6);f2
| Name | | ResSh1 | ResSh2 | Total |
|--------+---+-------------------------------------------------+-------------------------------------------------+-------------|
| Smith | 3 | /=VLOOKUP(final.A2,table1.$A$1:$G$3,7,FALSE())/ | /=VLOOKUP(final.A2,table2.$A$1:$G$3,7,FALSE())/ | =SUM(C2:D2) |
| Miller | 3 | /=VLOOKUP(final.A3,table1.$A$1:$G$3,7,FALSE())/ | /=VLOOKUP(final.A3,table2.$A$1:$G$3,5,FALSE())/ | =SUM(C3:D3) |
| Wick | 4 | /=VLOOKUP(final.A4,table1.$A$1:$G$4,7,FALSE())/ | /=VLOOKUP(final.A4,table2.$A$1:$G$4,7,FALSE())/ | =SUM(C4:D4) |
#+TBLFM: $5=SUM(@4$3:@4$4)
#+end_src
Maybe there are other solution in ods I don't know, so the question is what is equivalent of VLOOKUP in org.
I came up with the remote command that results in a similar result, (I did not want to use third party packages like
orgtbl-aggregate.
#+begin_src
#+Name: table1
| Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
|--------+-----+-----+-----+-----+-----+--------|
| Smith | 2 | 3 | 4 | 6 | 7 | 22 |
| Miller | 2 | 10 | 1 | 1 | 5 | 19 |
| Wick | 1 | 2 | 3 | 10 | 2 | 18 |
#+TBLFM: $7=vsum($2..$6);f2
#+Name: table2
| Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh2 |
|--------+-----+-----+-----+-----+-----+--------|
| Smith | 8 | 3 | 5 | 8 | 9 | 33 |
| Miller | 9 | 4 | 6 | 9 | 3 | 31 |
| Wick | 1 | 5 | 9 | 1 | 2 | 18 |
|--------+-----+-----+-----+-----+-----+--------|
#+TBLFM: $7=vsum($2..$6);f2
#+Name: final
| Name | Some | ResSh1 | ResSh2 | Final |
|--------+------+--------+--------+-------|
| Smith | 4 | 22 | 33 | 59 |
| Miller | 4 | 19 | 31 | 54 |
| Wick | 4 | 18 | 18 | 40 |
|--------+------+--------+--------+-------|
#+TBLFM: $3=remote(table1,@@#$7)::$4=remote(table2,@@#$7)::$5=vsum($2..$4);f2
#+end_src
The syntax with @@#$7 look cryptic to me, but I found it somewhere and it works
if somebody can suggest a simpler command that is closer to VLOOKUP I would be grateful
Thanks and regards
Uwe Brauer
--
Warning: Content may be disturbing to some audiences
I strongly condemn Putin's war of aggression against Ukraine.
I support to deliver weapons to Ukraine's military.
I support the NATO membership of Ukraine.
I support the EU membership of Ukraine.
https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/
[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]
^ permalink raw reply [flat|nested] 11+ messages in thread
* Re: equivalent of VLOOKUP (in ods) to org-table
2023-10-08 12:58 equivalent of VLOOKUP (in ods) to org-table Uwe Brauer
@ 2023-10-08 16:42 ` Bruno Barbier
2023-10-08 16:49 ` Uwe Brauer
0 siblings, 1 reply; 11+ messages in thread
From: Bruno Barbier @ 2023-10-08 16:42 UTC (permalink / raw)
To: Uwe Brauer, emacs-orgmode
Hi Uwe,
Uwe Brauer <oub@mat.ucm.es> writes:
> so the question is what is equivalent of VLOOKUP in org.
Did you check these lookup functions in the Org manual?
(info "(org) Lookup functions")
Bruno.
>
> I came up with the remote command that results in a similar result, (I did not want to use third party packages like
> orgtbl-aggregate.
>
> #+begin_src
> #+Name: table1
> | Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
> |--------+-----+-----+-----+-----+-----+--------|
> | Smith | 2 | 3 | 4 | 6 | 7 | 22 |
> | Miller | 2 | 10 | 1 | 1 | 5 | 19 |
> | Wick | 1 | 2 | 3 | 10 | 2 | 18 |
> #+TBLFM: $7=vsum($2..$6);f2
>
>
> #+Name: table2
> | Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh2 |
> |--------+-----+-----+-----+-----+-----+--------|
> | Smith | 8 | 3 | 5 | 8 | 9 | 33 |
> | Miller | 9 | 4 | 6 | 9 | 3 | 31 |
> | Wick | 1 | 5 | 9 | 1 | 2 | 18 |
> |--------+-----+-----+-----+-----+-----+--------|
> #+TBLFM: $7=vsum($2..$6);f2
>
> #+Name: final
> | Name | Some | ResSh1 | ResSh2 | Final |
> |--------+------+--------+--------+-------|
> | Smith | 4 | 22 | 33 | 59 |
> | Miller | 4 | 19 | 31 | 54 |
> | Wick | 4 | 18 | 18 | 40 |
> |--------+------+--------+--------+-------|
> #+TBLFM: $3=remote(table1,@@#$7)::$4=remote(table2,@@#$7)::$5=vsum($2..$4);f2
> #+end_src
>
>
> The syntax with @@#$7 look cryptic to me, but I found it somewhere and it works
> if somebody can suggest a simpler command that is closer to VLOOKUP I would be grateful
>
> Thanks and regards
>
> Uwe Brauer
>
> --
> Warning: Content may be disturbing to some audiences
> I strongly condemn Putin's war of aggression against Ukraine.
> I support to deliver weapons to Ukraine's military.
> I support the NATO membership of Ukraine.
> I support the EU membership of Ukraine.
> https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/
^ permalink raw reply [flat|nested] 11+ messages in thread
* Re: equivalent of VLOOKUP (in ods) to org-table
2023-10-08 16:42 ` Bruno Barbier
@ 2023-10-08 16:49 ` Uwe Brauer
2023-10-08 16:59 ` [the cryptic @@#$7] (was: equivalent of VLOOKUP (in ods) to org-table) Uwe Brauer
2023-10-08 17:11 ` equivalent of VLOOKUP (in ods) to org-table Bruno Barbier
0 siblings, 2 replies; 11+ messages in thread
From: Uwe Brauer @ 2023-10-08 16:49 UTC (permalink / raw)
To: Bruno Barbier; +Cc: Uwe Brauer, emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 717 bytes --]
>>> "BB" == Bruno Barbier <brubar.cs@gmail.com> writes:
> Hi Uwe,
> Uwe Brauer <oub@mat.ucm.es> writes:
>> so the question is what is equivalent of VLOOKUP in org.
> Did you check these lookup functions in the Org manual?
> (info "(org) Lookup functions")
Yes of course, but I am unable to obtain the same result as I do using
the remote call.
Uwe
--
Warning: Content may be disturbing to some audiences
I strongly condemn Putin's war of aggression against Ukraine.
I support to deliver weapons to Ukraine's military.
I support the NATO membership of Ukraine.
I support the EU membership of Ukraine.
https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/
[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]
^ permalink raw reply [flat|nested] 11+ messages in thread
* [the cryptic @@#$7] (was: equivalent of VLOOKUP (in ods) to org-table)
2023-10-08 16:49 ` Uwe Brauer
@ 2023-10-08 16:59 ` Uwe Brauer
2023-10-08 17:30 ` Bruno Barbier
2023-10-08 17:11 ` equivalent of VLOOKUP (in ods) to org-table Bruno Barbier
1 sibling, 1 reply; 11+ messages in thread
From: Uwe Brauer @ 2023-10-08 16:59 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 1589 bytes --]
>>> "UB" == Uwe Brauer <oub@mat.ucm.es> writes:
>>> "BB" == Bruno Barbier <brubar.cs@gmail.com> writes:
>> Hi Uwe,
>> Uwe Brauer <oub@mat.ucm.es> writes:
>>> so the question is what is equivalent of VLOOKUP in org.
>> Did you check these lookup functions in the Org manual?
>> (info "(org) Lookup functions")
I ask differently why does the following solution not work
#+Name: table1
| Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
|--------+-----+-----+-----+-----+-----+--------|
| Smith | 2 | 3 | 4 | 6 | 7 | 22 |
| Miller | 2 | 10 | 1 | 1 | 5 | 19 |
| Wick | 1 | 2 | 3 | 10 | 2 | 18 |
#+TBLFM: $7=vsum($2..$6);f2
#+Name: final
| Name | Some | ResSh1 |
|--------+------+--------------|
| Smith | 4 | [22, 19, 18] |
| Miller | 4 | [22, 19, 18] |
| Wick | 4 | [22, 19, 18] |
|--------+------+--------------|
#+TBLFM: @2$3..@>$3=remote(table1,@2$7..@>$7)
But this solution does, what does @@#$7 mean?
#+Name: final2
| Name | Some | ResSh1 |
|--------+------+--------|
| Smith | 4 | 22 |
| Miller | 4 | 19 |
| Wick | 4 | 18 |
|--------+------+--------|
#+TBLFM: @2$3..@>$3=remote(table1,@@#$7)
--
Warning: Content may be disturbing to some audiences
I strongly condemn Putin's war of aggression against Ukraine.
I support to deliver weapons to Ukraine's military.
I support the NATO membership of Ukraine.
I support the EU membership of Ukraine.
https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/
[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]
^ permalink raw reply [flat|nested] 11+ messages in thread
* Re: equivalent of VLOOKUP (in ods) to org-table
2023-10-08 16:49 ` Uwe Brauer
2023-10-08 16:59 ` [the cryptic @@#$7] (was: equivalent of VLOOKUP (in ods) to org-table) Uwe Brauer
@ 2023-10-08 17:11 ` Bruno Barbier
2023-10-08 17:45 ` Uwe Brauer
1 sibling, 1 reply; 11+ messages in thread
From: Bruno Barbier @ 2023-10-08 17:11 UTC (permalink / raw)
To: Uwe Brauer; +Cc: Uwe Brauer, emacs-orgmode
Uwe Brauer <oub@mat.ucm.es> writes:
>>>> "BB" == Bruno Barbier <brubar.cs@gmail.com> writes:
>> Did you check these lookup functions in the Org manual?
>> (info "(org) Lookup functions")
>
>
> Yes of course, but I am unable to obtain the same result as I do using
> the remote call.
Did you try something like this ?
#+NAME: table1
| Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
|--------+-----+-----+-----+-----+-----+--------|
| Smith | 2 | 3 | 4 | 6 | 7 | 22 |
| Miller | 2 | 10 | 1 | 1 | 5 | 19 |
| Wick | 1 | 2 | 3 | 10 | 2 | 18 |
#+TBLFM: $7=vsum($2..$6);f2
#+NAME: table2
| Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh2 |
|--------+-----+-----+-----+-----+-----+--------|
| Smith | 8 | 3 | 5 | 8 | 9 | 33 |
| Miller | 2 | 1 | 6 | 9 | 3 | 21 |
| Wick | 1 | 5 | 9 | 1 | 2 | 18 |
#+TBLFM: $7=vsum($2..$6);f2
| Name | | ResSh1 | ResSh2 | Total |
|--------+---+--------+--------+-------|
| Smith | 4 | 22 | 33 | 59 |
| Miller | 4 | 19 | 21 | 44 |
| Wick | 4 | 18 | 18 | 40 |
#+TBLFM: $3='(org-lookup-first $1 '(remote(table1, @I$1..@II$1)) '(remote(table1, @I$7..@II$7)))
#+TBLFM: $5=vsum($2..$4)
IIUC, it seems to be the result that you're expecting.
Bruno
>
> Uwe
> --
> Warning: Content may be disturbing to some audiences
> I strongly condemn Putin's war of aggression against Ukraine.
> I support to deliver weapons to Ukraine's military.
> I support the NATO membership of Ukraine.
> I support the EU membership of Ukraine.
> https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/
^ permalink raw reply [flat|nested] 11+ messages in thread
* Re: [the cryptic @@#$7] (was: equivalent of VLOOKUP (in ods) to org-table)
2023-10-08 16:59 ` [the cryptic @@#$7] (was: equivalent of VLOOKUP (in ods) to org-table) Uwe Brauer
@ 2023-10-08 17:30 ` Bruno Barbier
2023-10-08 17:46 ` [the cryptic @@#$7] Uwe Brauer
2023-10-11 15:01 ` Uwe Brauer
0 siblings, 2 replies; 11+ messages in thread
From: Bruno Barbier @ 2023-10-08 17:30 UTC (permalink / raw)
To: Uwe Brauer, emacs-orgmode
Uwe Brauer <oub@mat.ucm.es> writes:
>>>> "UB" == Uwe Brauer <oub@mat.ucm.es> writes:
>
>>>> "BB" == Bruno Barbier <brubar.cs@gmail.com> writes:
>>> Hi Uwe,
>
>>> Uwe Brauer <oub@mat.ucm.es> writes:
>>>> so the question is what is equivalent of VLOOKUP in org.
>
>>> Did you check these lookup functions in the Org manual?
>
>>> (info "(org) Lookup functions")
>
>
> I ask differently why does the following solution not work
>
> #+Name: table1
> | Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
> |--------+-----+-----+-----+-----+-----+--------|
> | Smith | 2 | 3 | 4 | 6 | 7 | 22 |
> | Miller | 2 | 10 | 1 | 1 | 5 | 19 |
> | Wick | 1 | 2 | 3 | 10 | 2 | 18 |
> #+TBLFM: $7=vsum($2..$6);f2
>
>
>
> #+Name: final
> | Name | Some | ResSh1 |
> |--------+------+--------------|
> | Smith | 4 | [22, 19, 18] |
> | Miller | 4 | [22, 19, 18] |
> | Wick | 4 | [22, 19, 18] |
> |--------+------+--------------|
> #+TBLFM: @2$3..@>$3=remote(table1,@2$7..@>$7)
The ref "remote(table1,@2$7..@>$7)" targets a range, containing all fields of
colum 7. So, for each field, you get the same value: an array
containing all the field values: [22, 19, 19].
>
> But this solution does, what does @@#$7 mean?
See (info "(org) References"), in the section:
"Field coordinates in formulas"
"‘@#’ is substituted with the row number of the field where the
current result will go to".
> #+Name: final2
> | Name | Some | ResSh1 |
> |--------+------+--------|
> | Smith | 4 | 22 |
> | Miller | 4 | 19 |
> | Wick | 4 | 18 |
> |--------+------+--------|
> #+TBLFM: @2$3..@>$3=remote(table1,@@#$7)
With this formula, your remote reference targets the field, that is in
the same row number as the computed field in final.
Here is the same table, with some extra columns that show the
intermediate steps:
#+Name: final2
| Name | Some | ResSh1 | What is '@#'? | Which ref? |
|--------+------+--------+---------------+------------|
| Smith | 4 | 22 | 2 | @2$7 |
| Miller | 4 | 19 | 3 | @3$7 |
| Wick | 4 | 18 | 4 | @4$7 |
|--------+------+--------+---------------+------------|
#+TBLFM: @2$3..@>$3=remote(table1,@@#$7)
#+TBLFM: @2$4..@>$4=@#
#+TBLFM: @2$5..@>$5='(concat "@" (format "%d" @#) "$" "7")
Bruno
>
>
>
> --
> Warning: Content may be disturbing to some audiences
> I strongly condemn Putin's war of aggression against Ukraine.
> I support to deliver weapons to Ukraine's military.
> I support the NATO membership of Ukraine.
> I support the EU membership of Ukraine.
> https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/
^ permalink raw reply [flat|nested] 11+ messages in thread
* Re: equivalent of VLOOKUP (in ods) to org-table
2023-10-08 17:11 ` equivalent of VLOOKUP (in ods) to org-table Bruno Barbier
@ 2023-10-08 17:45 ` Uwe Brauer
0 siblings, 0 replies; 11+ messages in thread
From: Uwe Brauer @ 2023-10-08 17:45 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 722 bytes --]
> Uwe Brauer <oub@mat.ucm.es> writes:
> #+TBLFM: $3='(org-lookup-first $1 '(remote(table1, @I$1..@II$1)) '(remote(table1, @I$7..@II$7)))
Oops no I tried
$3='(org-lookup-first '(remote(table1, @1$7..@4$7)))
And that gave error
So thanks a lot
> #+TBLFM: $5=vsum($2..$4)
> IIUC, it seems to be the result that you're expecting.
> Bruno
--
Warning: Content may be disturbing to some audiences
I strongly condemn Putin's war of aggression against Ukraine.
I support to deliver weapons to Ukraine's military.
I support the NATO membership of Ukraine.
I support the EU membership of Ukraine.
https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/
[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]
^ permalink raw reply [flat|nested] 11+ messages in thread
* Re: [the cryptic @@#$7]
2023-10-08 17:30 ` Bruno Barbier
@ 2023-10-08 17:46 ` Uwe Brauer
2023-10-11 15:01 ` Uwe Brauer
1 sibling, 0 replies; 11+ messages in thread
From: Uwe Brauer @ 2023-10-08 17:46 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 920 bytes --]
> Uwe Brauer <oub@mat.ucm.es> writes:
> The ref "remote(table1,@2$7..@>$7)" targets a range, containing all fields of
> colum 7. So, for each field, you get the same value: an array
> containing all the field values: [22, 19, 19].
> See (info "(org) References"), in the section:
> "Field coordinates in formulas"
> "‘@#’ is substituted with the row number of the field where the
> current result will go to".
Aha, and has the benefit that the lookup call is not needed, but is more cryptic thanks for clarifying this.
--
Warning: Content may be disturbing to some audiences
I strongly condemn Putin's war of aggression against Ukraine.
I support to deliver weapons to Ukraine's military.
I support the NATO membership of Ukraine.
I support the EU membership of Ukraine.
https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/
[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]
^ permalink raw reply [flat|nested] 11+ messages in thread
* Re: [the cryptic @@#$7]
2023-10-08 17:30 ` Bruno Barbier
2023-10-08 17:46 ` [the cryptic @@#$7] Uwe Brauer
@ 2023-10-11 15:01 ` Uwe Brauer
2023-10-12 13:21 ` Bruno Barbier
1 sibling, 1 reply; 11+ messages in thread
From: Uwe Brauer @ 2023-10-11 15:01 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 4428 bytes --]
> Uwe Brauer <oub@mat.ucm.es> writes:
> The ref "remote(table1,@2$7..@>$7)" targets a range, containing all fields of
> colum 7. So, for each field, you get the same value: an array
> containing all the field values: [22, 19, 19].
> See (info "(org) References"), in the section:
> "Field coordinates in formulas"
> "‘@#’ is substituted with the row number of the field where the
> current result will go to".
> With this formula, your remote reference targets the field, that is in
> the same row number as the computed field in final.
> Here is the same table, with some extra columns that show the
> intermediate steps:
> #+Name: final2
> | Name | Some | ResSh1 | What is '@#'? | Which ref? |
> |--------+------+--------+---------------+------------|
> | Smith | 4 | 22 | 2 | @2$7 |
> | Miller | 4 | 19 | 3 | @3$7 |
> | Wick | 4 | 18 | 4 | @4$7 |
> |--------+------+--------+---------------+------------|
> #+TBLFM: @2$3..@>$3=remote(table1,@@#$7)
> #+TBLFM: @2$4..@>$4=@#
> #+TBLFM: @2$5..@>$5='(concat "@" (format "%d" @#) "$" "7")
Here is an example where the org-lookup-first method seems to fail
#+begin_src
#+Name: Table1A
| / | <> | <> | <> | <> | <> | <> | <> |
| | Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | E1Tot |
|---+----------+-----+-----+-----+-----+-----+-------|
| | 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: $8=vsum($3..$7);f2
#+Name: Table2A
| / | <> | <> | <> | <> | <> | <> | <> |
| | Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | E2Tot |
|---+----------+-----+-----+-----+-----+-----+-------|
| | 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: $8=vsum($3..$7);f2
#+Name: Final1A
| / | <> | <> | <> | <> |
| | Name | E1Tot | E2Tot | Total |
|---+----------+-------+-------+-------|
| | Smith | nil | nil | 2 nil |
| | Brown | nil | nil | 2 nil |
| | Graham | nil | nil | 2 nil |
| | Jones | nil | nil | 2 nil |
| | Thompson | nil | nil | 2 nil |
| | Walker | nil | nil | 2 nil |
#+TBLFM: $3='(org-lookup-first $2 '(remote(Table1A, @I$1..@II$1)) '(remote(Table1A, @I$7..@II$7)))::$4='(org-lookup-first $2 '(remote(Table2A, @I$1..@II$1)) '(remote(Table2A, @I$7..@II$7)))::$5=vsum($3..$4);f2
#+Name: Final2A
| / | <> | <> | <> | <> |
| | Name | E1Tot | E2Tot | 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: $5=vsum($3..$4);f2::@3$3..@>$3=remote(Table1A,@@#$8)::@3$4..@>$4=remote(Table2A,@@#$8)
#+Name: Final3
| / | <> | <> | <> | <> |
| | Name | E1Tot | E2Tot | 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: $3=remote(Table1A,@@#$8)::$4=remote(Table2A,@@#$8)::$5=vsum($3..$4);f2
#+end_src
> Bruno
--
Warning: Content may be disturbing to some audiences
I strongly condemn Hamas terroristic attack on Israel.
I strongly condemn Putin's war of aggression against Ukraine.
I support to deliver weapons to Ukraine's military.
I support the NATO membership of Ukraine.
I support the EU membership of Ukraine.
https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/
[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]
^ permalink raw reply [flat|nested] 11+ messages in thread
* Re: [the cryptic @@#$7]
2023-10-11 15:01 ` Uwe Brauer
@ 2023-10-12 13:21 ` Bruno Barbier
2023-10-12 14:40 ` Uwe Brauer
0 siblings, 1 reply; 11+ messages in thread
From: Bruno Barbier @ 2023-10-12 13:21 UTC (permalink / raw)
To: Uwe Brauer, emacs-orgmode
Hi Uwe,
Uwe Brauer <oub@mat.ucm.es> writes:
> Here is an example where the org-lookup-first method seems to fail
> #+TBLFM: $3='(org-lookup-first $2 '(remote(Table1A, @I$1..@II$1)) '(remote(Table1A, @I$7..@II$7)))::$4='(org-lookup-first $2 '(remote(Table2A, @I$1..@II$1)) '(remote(Table2A, @I$7..@II$7)))::$5=vsum($3..$4);f2
You forgot to update the column numbers: the names to look for are now in
the column '2', and, the totals are now in column '8'. This should work:
#+TBLFM: $3='(org-lookup-first $2 '(remote(Table1A, @I$2..@II$2)) '(remote(Table1A, @I$8..@II$8)))
#+TBLFM: $4='(org-lookup-first $2 '(remote(Table2A, @I$2..@II$2)) '(remote(Table2A, @I$8..@II$8)))
#+TBLFM: $5=vsum($3..$4);f2
Bruno
^ permalink raw reply [flat|nested] 11+ messages in thread
* Re: [the cryptic @@#$7]
2023-10-12 13:21 ` Bruno Barbier
@ 2023-10-12 14:40 ` Uwe Brauer
0 siblings, 0 replies; 11+ messages in thread
From: Uwe Brauer @ 2023-10-12 14:40 UTC (permalink / raw)
To: Bruno Barbier; +Cc: Uwe Brauer, emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 1571 bytes --]
>>> "BB" == Bruno Barbier <brubar.cs@gmail.com> writes:
> Hi Uwe,
> Uwe Brauer <oub@mat.ucm.es> writes:
>> Here is an example where the org-lookup-first method seems to fail
>> #+TBLFM: $3='(org-lookup-first $2 '(remote(Table1A, @I$1..@II$1))
>> '(remote(Table1A, @I$7..@II$7)))::$4='(org-lookup-first $2
>> '(remote(Table2A, @I$1..@II$1)) '(remote(Table2A,
>> @I$7..@II$7)))::$5=vsum($3..$4);f2
> You forgot to update the column numbers: the names to look for are now in
> the column '2', and, the totals are now in column '8'. This should work:
> #+TBLFM: $3='(org-lookup-first $2 '(remote(Table1A, @I$2..@II$2)) '(remote(Table1A, @I$8..@II$8)))
> #+TBLFM: $4='(org-lookup-first $2 '(remote(Table2A, @I$2..@II$2)) '(remote(Table2A, @I$8..@II$8)))
> #+TBLFM: $5=vsum($3..$4);f2
Arg 😨
I even tried
#+TBLFM: $3='(org-lookup-first $2 '(remote(Table1A, @I$1..@II$1))'(remote(Table1A, @I$8..@II$8)))
#+TBLFM:$4='(org-lookup-first $2 '(remote(Table2A, @I$1..@II$1)) '(remote(Table2A, @I$8..@II$8)))
#+TBLFM:$5=vsum($3..$4);f2
So I changed
1. $7-->$8
2. but *not* $1-->$2
Thanks very much
Uwe
--
Warning: Content may be disturbing to some audiences
I strongly condemn Hamas terroristic attack on Israel.
I strongly condemn Putin's war of aggression against Ukraine.
I support to deliver weapons to Ukraine's military.
I support the NATO membership of Ukraine.
I support the EU membership of Ukraine.
https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/
[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]
^ permalink raw reply [flat|nested] 11+ messages in thread
end of thread, other threads:[~2023-10-12 14:41 UTC | newest]
Thread overview: 11+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2023-10-08 12:58 equivalent of VLOOKUP (in ods) to org-table Uwe Brauer
2023-10-08 16:42 ` Bruno Barbier
2023-10-08 16:49 ` Uwe Brauer
2023-10-08 16:59 ` [the cryptic @@#$7] (was: equivalent of VLOOKUP (in ods) to org-table) Uwe Brauer
2023-10-08 17:30 ` Bruno Barbier
2023-10-08 17:46 ` [the cryptic @@#$7] Uwe Brauer
2023-10-11 15:01 ` Uwe Brauer
2023-10-12 13:21 ` Bruno Barbier
2023-10-12 14:40 ` Uwe Brauer
2023-10-08 17:11 ` equivalent of VLOOKUP (in ods) to org-table Bruno Barbier
2023-10-08 17:45 ` Uwe Brauer
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).