/ExcelLambdaTools

Excel VBA macros to import and export user-defined lambda functions to/from a text file + a repository of potentially useful lambda functions

Primary LanguageVBAMIT LicenseMIT

Examples for some lambdas included in my_excel_lambda_functions.txt

The lambda functions can be ceasily imported from that that text file with a VBA macro ImportLambdasFromTextFile from lambda_import_export.vba.

■  Reshape (melt) from a wide data to a long data form

Similar to R's melt or Stata's reshape long

Example:

If you download the data from https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/data/NAMA_10_PC/.CP_EUR_HAB+CP_NAC_HAB.B1GQ+P31_S14.BE+BG+CZ+DE?format=TSV&startPeriod=2020&endPeriod=2022 you will get this:

A B C D E F G
1 freq unit na_item geo 2020 2021 2022
2 A CP_EUR_HAB B1GQ BE 39830 p 43350 p 46990 p
3 A CP_EUR_HAB B1GQ BG 8890 10330 12400 p
4 A CP_EUR_HAB B1GQ CZ 20170 22270 25850
5 A CP_EUR_HAB B1GQ DE 40930 p 43480 p 46260 p
6 A CP_EUR_HAB P31_S14 BE 19250 p 20740 p 23240 p
7 A CP_EUR_HAB P31_S14 BG 5150 5980 7370 p
8 A CP_EUR_HAB P31_S14 CZ 8960 9900 11820
9 A CP_EUR_HAB P31_S14 DE 19890 p 20800 p 22930 p
10 A CP_NAC_HAB B1GQ BE 39830 p 43350 p 46990 p
11 A CP_NAC_HAB B1GQ BG 17390 20210 24250 p
12 A CP_NAC_HAB B1GQ CZ 533560 571050 634910
13 A CP_NAC_HAB B1GQ DE 40930 p 43480 p 46260 p
14 A CP_NAC_HAB P31_S14 BE 19250 p 20740 p 23240 p
15 A CP_NAC_HAB P31_S14 BG 10060 11700 14410 p
16 A CP_NAC_HAB P31_S14 CZ 237040 253890 290420
17 A CP_NAC_HAB P31_S14 DE 19890 p 20800 p 22930 p

If you use the function =reshapeToLong(A1:G17,{"unit","na_item","geo"},{2020,2021,2022},"val","year") in any Excel cell that has sufficiently many empty cells below and to the right (to avoid the #SPILL! error) you will get this:

val year unit na_item geo
39830 p 2020 CP_EUR_HAB B1GQ BE
8890 2020 CP_EUR_HAB B1GQ BG
20170 2020 CP_EUR_HAB B1GQ CZ
40930 p 2020 CP_EUR_HAB B1GQ DE
19250 p 2020 CP_EUR_HAB P31_S14 BE
5150 2020 CP_EUR_HAB P31_S14 BG
8960 2020 CP_EUR_HAB P31_S14 CZ
19890 p 2020 CP_EUR_HAB P31_S14 DE
39830 p 2020 CP_NAC_HAB B1GQ BE
17390 2020 CP_NAC_HAB B1GQ BG
533560 2020 CP_NAC_HAB B1GQ CZ
40930 p 2020 CP_NAC_HAB B1GQ DE
19250 p 2020 CP_NAC_HAB P31_S14 BE
10060 2020 CP_NAC_HAB P31_S14 BG
237040 2020 CP_NAC_HAB P31_S14 CZ
19890 p 2020 CP_NAC_HAB P31_S14 DE
43350 p 2021 CP_EUR_HAB B1GQ BE
10330 2021 CP_EUR_HAB B1GQ BG
22270 2021 CP_EUR_HAB B1GQ CZ
43480 p 2021 CP_EUR_HAB B1GQ DE
20740 p 2021 CP_EUR_HAB P31_S14 BE
5980 2021 CP_EUR_HAB P31_S14 BG
9900 2021 CP_EUR_HAB P31_S14 CZ
20800 p 2021 CP_EUR_HAB P31_S14 DE
43350 p 2021 CP_NAC_HAB B1GQ BE
20210 2021 CP_NAC_HAB B1GQ BG
571050 2021 CP_NAC_HAB B1GQ CZ
43480 p 2021 CP_NAC_HAB B1GQ DE
20740 p 2021 CP_NAC_HAB P31_S14 BE
11700 2021 CP_NAC_HAB P31_S14 BG
253890 2021 CP_NAC_HAB P31_S14 CZ
20800 p 2021 CP_NAC_HAB P31_S14 DE
46990 p 2022 CP_EUR_HAB B1GQ BE
12400 p 2022 CP_EUR_HAB B1GQ BG
25850 2022 CP_EUR_HAB B1GQ CZ
46260 p 2022 CP_EUR_HAB B1GQ DE
23240 p 2022 CP_EUR_HAB P31_S14 BE
7370 p 2022 CP_EUR_HAB P31_S14 BG
11820 2022 CP_EUR_HAB P31_S14 CZ
22930 p 2022 CP_EUR_HAB P31_S14 DE
46990 p 2022 CP_NAC_HAB B1GQ BE
24250 p 2022 CP_NAC_HAB B1GQ BG
634910 2022 CP_NAC_HAB B1GQ CZ
46260 p 2022 CP_NAC_HAB B1GQ DE
23240 p 2022 CP_NAC_HAB P31_S14 BE
14410 p 2022 CP_NAC_HAB P31_S14 BG
290420 2022 CP_NAC_HAB P31_S14 CZ
22930 p 2022 CP_NAC_HAB P31_S14 DE

■  Reshape (cast) from a wide data to a long data form

Similar to R's cast or dcast or Stata's reshape wide

Now, if you want to reverse the above transformation and go from the long table to the wide one, use the function =reshapeToWide(J1#,{"unit","na_item","geo"},"val","year") (where J1 is the top left corner of the long table produced by reshapeToLong, but use the standard Excel top-left-cell : bottom-right-cell notation if you have a static set of data cells, otherwise you'll get the #REF! error). Again, make sure that the Excel cell where you call reshapeToWide has sufficiently many empty cells below and to the right (to avoid the #SPILL! error).

Note that if some combinations/rows of the id variables ('unit', 'na_item', 'geo', and 'year' in the example) where missing, you would get the missing data cells (#N/As) in the wide version produced by reshapeToWide. E.g., calling =reshapeToWide(A1:D4,{"id1","id2"},"v","yr") with the source data:

A B C D
1 id1 id2 v yr
2 a b 101 2020
3 b a 102 2020
4 a b 103 2021

generates the following output:

id1 id2 2020 2021
a b 101 103
b a 102 #N/A