# Compass Directions

#### Duncan Garmonsway

#### 2018-01-20

This vignette for the unpivotr package demonstrates unpivoting pivot tables of various kinds imported from a spreadsheet via the tidyxl package. It is best read with the spreadsheet open in a spreadsheet program, e.g. Excel, LibreOffice Calc or Gnumeric.

The spreadsheet is in the package directory at `system.file("extdata", "purpose.xlsx", package = "unpivotr")`.

    library(dplyr)

    ##
    ## Attaching package: 'dplyr'

    ## The following objects are masked from 'package:stats':
    ##
    ##     filter, lag

    ## The following objects are masked from 'package:base':
    ##
    ##     intersect, setdiff, setequal, union

    library(tidyxl)
    library(unpivotr)

    ##
    ## Attaching package: 'unpivotr'

    ## The following object is masked from 'package:stats':
    ##
    ##     offset

## NNW WNW example

    (original <- purpose$`NNW WNW`)

    ##                            X2      X3     X4     X5    X6     X7
    ## 1                        <NA>    <NA> Female   <NA>  Male   <NA>
    ## 2                        <NA>    <NA>  0 - 6 7 - 10 0 - 6 7 - 10
    ## 3           Bachelor's degree 15 - 24   7000  27000  <NA>  13000
    ## 4                        <NA> 25 - 44  12000 137000  9000  81000
    ## 5                        <NA> 45 - 64  10000  64000  7000  66000
    ## 6                        <NA>     65+   <NA>  18000  7000  17000
    ## 7                 Certificate 15 - 24  29000 161000 30000 190000
    ## 8                        <NA> 25 - 44  34000 179000 31000 219000
    ## 9                        <NA> 45 - 64  30000 210000 23000 199000
    ## 10                       <NA>     65+  12000  77000  8000 107000
    ## 11                    Diploma 15 - 24   <NA>  14000  9000  11000
    ## 12                       <NA> 25 - 44  10000  66000  8000  47000
    ## 13                       <NA> 45 - 64   6000  68000  5000  58000
    ## 14                       <NA>     65+   5000  41000  1000  34000
    ## 15           No Qualification 15 - 24  10000  43000 12000  37000
    ## 16                       <NA> 25 - 44  11000  36000 21000  50000
    ## 17                       <NA> 45 - 64  19000  91000 17000  75000
    ## 18                       <NA>     65+  16000 118000  9000  66000
    ## 19 Postgraduate qualification 15 - 24   <NA>   6000  <NA>   <NA>
    ## 20                       <NA> 25 - 44   5000  86000  7000  60000
    ## 21                       <NA> 45 - 64   6000  55000  6000  68000
    ## 22                       <NA>     65+   <NA>  13000  <NA>  18000

    tail(cells <- tidy_table(original))

    ## # A tibble: 6 x 3
    ##     row   col chr
    ##   <int> <int> <chr>
    ## 1    17     6 75000
    ## 2    18     6 66000
    ## 3    19     6 <NA>
    ## 4    20     6 60000
    ## 5    21     6 68000
    ## 6    22     6 18000

### Headers

    row_headers <-
      cells %>%
      filter(col <= 2, !is.na(chr)) %>% # Select all rows of headers at once
      select(row, col, header = chr) %>%
      split(.$col) # Return each row of headers in its own element of a list
    row_headers

    ## $`1`
    ## # A tibble: 5 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     3     1 Bachelor's degree
    ## 2     7     1 Certificate
    ## 3    11     1 Diploma
    ## 4    15     1 No Qualification
    ## 5    19     1 Postgraduate qualification
    ##
    ## $`2`
    ## # A tibble: 20 x 3
    ##      row   col header
    ##    <int> <int> <chr>
    ##  1     3     2 15 - 24
    ##  2     4     2 25 - 44
    ##  3     5     2 45 - 64
    ##  4     6     2 65+
    ##  5     7     2 15 - 24
    ##  6     8     2 25 - 44
    ##  7     9     2 45 - 64
    ##  8    10     2 65+
    ##  9    11     2 15 - 24
    ## 10    12     2 25 - 44
    ## 11    13     2 45 - 64
    ## 12    14     2 65+
    ## 13    15     2 15 - 24
    ## 14    16     2 25 - 44
    ## 15    17     2 45 - 64
    ## 16    18     2 65+
    ## 17    19     2 15 - 24
    ## 18    20     2 25 - 44
    ## 19    21     2 45 - 64
    ## 20    22     2 65+

    col_headers <-
      cells %>%
      filter(row <= 2, !is.na(chr)) %>%
      select(row, col, header = chr) %>%
      split(.$row)
    col_headers

    ## $`1`
    ## # A tibble: 2 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     1     3 Female
    ## 2     1     5 Male
    ##
    ## $`2`
    ## # A tibble: 4 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     2     3 0 - 6
    ## 2     2     4 7 - 10
    ## 3     2     5 0 - 6
    ## 4     2     6 7 - 10

### Data

    datacells <-
      cells %>%
      filter(row >= 3, col >= 3, !is.na(chr)) %>%
      mutate(value = as.integer(chr)) %>%
      select(row, col, value)
    head(datacells)

    ## # A tibble: 6 x 3
    ##     row   col value
    ##   <int> <int> <int>
    ## 1     3     3  7000
    ## 2     4     3 12000
    ## 3     5     3 10000
    ## 4     7     3 29000
    ## 5     8     3 34000
    ## 6     9     3 30000

### Join headers to data

    datacells <-
      datacells %>%
      NNW(col_headers[[1]]) %>%
      N(col_headers[[2]]) %>%
      WNW(row_headers[[1]]) %>%
      W(row_headers[[2]])

### Result

    datacells %>% as.data.frame

    ##    row col  value header.data header.header           header.data.data
    ## 1    3   3   7000      Female         0 - 6          Bachelor's degree
    ## 2    4   3  12000      Female         0 - 6          Bachelor's degree
    ## 3    5   3  10000      Female         0 - 6          Bachelor's degree
    ## 4    7   3  29000      Female         0 - 6                Certificate
    ## 5    8   3  34000      Female         0 - 6                Certificate
    ## 6    9   3  30000      Female         0 - 6                Certificate
    ## 7   10   3  12000      Female         0 - 6                Certificate
    ## 8   12   3  10000      Female         0 - 6                    Diploma
    ## 9   13   3   6000      Female         0 - 6                    Diploma
    ## 10  14   3   5000      Female         0 - 6                    Diploma
    ## 11  15   3  10000      Female         0 - 6           No Qualification
    ## 12  16   3  11000      Female         0 - 6           No Qualification
    ## 13  17   3  19000      Female         0 - 6           No Qualification
    ## 14  18   3  16000      Female         0 - 6           No Qualification
    ## 15  20   3   5000      Female         0 - 6 Postgraduate qualification
    ## 16  21   3   6000      Female         0 - 6 Postgraduate qualification
    ## 17   3   4  27000      Female        7 - 10          Bachelor's degree
    ## 18   4   4 137000      Female        7 - 10          Bachelor's degree
    ## 19   5   4  64000      Female        7 - 10          Bachelor's degree
    ## 20   6   4  18000      Female        7 - 10          Bachelor's degree
    ## 21   7   4 161000      Female        7 - 10                Certificate
    ## 22   8   4 179000      Female        7 - 10                Certificate
    ## 23   9   4 210000      Female        7 - 10                Certificate
    ## 24  10   4  77000      Female        7 - 10                Certificate
    ## 25  11   4  14000      Female        7 - 10                    Diploma
    ## 26  12   4  66000      Female        7 - 10                    Diploma
    ## 27  13   4  68000      Female        7 - 10                    Diploma
    ## 28  14   4  41000      Female        7 - 10                    Diploma
    ## 29  15   4  43000      Female        7 - 10           No Qualification
    ## 30  16   4  36000      Female        7 - 10           No Qualification
    ## 31  17   4  91000      Female        7 - 10           No Qualification
    ## 32  18   4 118000      Female        7 - 10           No Qualification
    ## 33  19   4   6000      Female        7 - 10 Postgraduate qualification
    ## 34  20   4  86000      Female        7 - 10 Postgraduate qualification
    ## 35  21   4  55000      Female        7 - 10 Postgraduate qualification
    ## 36  22   4  13000      Female        7 - 10 Postgraduate qualification
    ## 37   4   5   9000        Male         0 - 6          Bachelor's degree
    ## 38   5   5   7000        Male         0 - 6          Bachelor's degree
    ## 39   6   5   7000        Male         0 - 6          Bachelor's degree
    ## 40   7   5  30000        Male         0 - 6                Certificate
    ## 41   8   5  31000        Male         0 - 6                Certificate
    ## 42   9   5  23000        Male         0 - 6                Certificate
    ## 43  10   5   8000        Male         0 - 6                Certificate
    ## 44  11   5   9000        Male         0 - 6                    Diploma
    ## 45  12   5   8000        Male         0 - 6                    Diploma
    ## 46  13   5   5000        Male         0 - 6                    Diploma
    ## 47  14   5   1000        Male         0 - 6                    Diploma
    ## 48  15   5  12000        Male         0 - 6           No Qualification
    ## 49  16   5  21000        Male         0 - 6           No Qualification
    ## 50  17   5  17000        Male         0 - 6           No Qualification
    ## 51  18   5   9000        Male         0 - 6           No Qualification
    ## 52  20   5   7000        Male         0 - 6 Postgraduate qualification
    ## 53  21   5   6000        Male         0 - 6 Postgraduate qualification
    ## 54   3   6  13000        Male        7 - 10          Bachelor's degree
    ## 55   4   6  81000        Male        7 - 10          Bachelor's degree
    ## 56   5   6  66000        Male        7 - 10          Bachelor's degree
    ## 57   6   6  17000        Male        7 - 10          Bachelor's degree
    ## 58   7   6 190000        Male        7 - 10                Certificate
    ## 59   8   6 219000        Male        7 - 10                Certificate
    ## 60   9   6 199000        Male        7 - 10                Certificate
    ## 61  10   6 107000        Male        7 - 10                Certificate
    ## 62  11   6  11000        Male        7 - 10                    Diploma
    ## 63  12   6  47000        Male        7 - 10                    Diploma
    ## 64  13   6  58000        Male        7 - 10                    Diploma
    ## 65  14   6  34000        Male        7 - 10                    Diploma
    ## 66  15   6  37000        Male        7 - 10           No Qualification
    ## 67  16   6  50000        Male        7 - 10           No Qualification
    ## 68  17   6  75000        Male        7 - 10           No Qualification
    ## 69  18   6  66000        Male        7 - 10           No Qualification
    ## 70  20   6  60000        Male        7 - 10 Postgraduate qualification
    ## 71  21   6  68000        Male        7 - 10 Postgraduate qualification
    ## 72  22   6  18000        Male        7 - 10 Postgraduate qualification
    ##    header.header.header
    ## 1               15 - 24
    ## 2               25 - 44
    ## 3               45 - 64
    ## 4               15 - 24
    ## 5               25 - 44
    ## 6               45 - 64
    ## 7                   65+
    ## 8               25 - 44
    ## 9               45 - 64
    ## 10                  65+
    ## 11              15 - 24
    ## 12              25 - 44
    ## 13              45 - 64
    ## 14                  65+
    ## 15              25 - 44
    ## 16              45 - 64
    ## 17              15 - 24
    ## 18              25 - 44
    ## 19              45 - 64
    ## 20                  65+
    ## 21              15 - 24
    ## 22              25 - 44
    ## 23              45 - 64
    ## 24                  65+
    ## 25              15 - 24
    ## 26              25 - 44
    ## 27              45 - 64
    ## 28                  65+
    ## 29              15 - 24
    ## 30              25 - 44
    ## 31              45 - 64
    ## 32                  65+
    ## 33              15 - 24
    ## 34              25 - 44
    ## 35              45 - 64
    ## 36                  65+
    ## 37              25 - 44
    ## 38              45 - 64
    ## 39                  65+
    ## 40              15 - 24
    ## 41              25 - 44
    ## 42              45 - 64
    ## 43                  65+
    ## 44              15 - 24
    ## 45              25 - 44
    ## 46              45 - 64
    ## 47                  65+
    ## 48              15 - 24
    ## 49              25 - 44
    ## 50              45 - 64
    ## 51                  65+
    ## 52              25 - 44
    ## 53              45 - 64
    ## 54              15 - 24
    ## 55              25 - 44
    ## 56              45 - 64
    ## 57                  65+
    ## 58              15 - 24
    ## 59              25 - 44
    ## 60              45 - 64
    ## 61                  65+
    ## 62              15 - 24
    ## 63              25 - 44
    ## 64              45 - 64
    ## 65                  65+
    ## 66              15 - 24
    ## 67              25 - 44
    ## 68              45 - 64
    ## 69                  65+
    ## 70              25 - 44
    ## 71              45 - 64
    ## 72                  65+

    NNW_WNW <- datacells %>% arrange(row, col)

## NNE WSW example

    cells <- tidy_table(purpose$`NNE WSW`)

### Headers (same as NNW WNW)

    row_headers <-
      cells %>%
      filter(col <= 2, !is.na(chr)) %>%
      select(row, col, header = chr) %>%
      split(.$col)
    row_headers

    ## $`1`
    ## # A tibble: 5 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     6     1 Bachelor's degree
    ## 2    10     1 Certificate
    ## 3    14     1 Diploma
    ## 4    18     1 No Qualification
    ## 5    22     1 Postgraduate qualification
    ##
    ## $`2`
    ## # A tibble: 20 x 3
    ##      row   col header
    ##    <int> <int> <chr>
    ##  1     3     2 15 - 24
    ##  2     4     2 25 - 44
    ##  3     5     2 45 - 64
    ##  4     6     2 65+
    ##  5     7     2 15 - 24
    ##  6     8     2 25 - 44
    ##  7     9     2 45 - 64
    ##  8    10     2 65+
    ##  9    11     2 15 - 24
    ## 10    12     2 25 - 44
    ## 11    13     2 45 - 64
    ## 12    14     2 65+
    ## 13    15     2 15 - 24
    ## 14    16     2 25 - 44
    ## 15    17     2 45 - 64
    ## 16    18     2 65+
    ## 17    19     2 15 - 24
    ## 18    20     2 25 - 44
    ## 19    21     2 45 - 64
    ## 20    22     2 65+

    col_headers <-
      cells %>%
      filter(row <= 2, !is.na(chr)) %>%
      select(row, col, header = chr) %>%
      split(.$row)
    col_headers

    ## $`1`
    ## # A tibble: 2 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     1     4 Female
    ## 2     1     6 Male
    ##
    ## $`2`
    ## # A tibble: 4 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     2     3 0 - 6
    ## 2     2     4 7 - 10
    ## 3     2     5 0 - 6
    ## 4     2     6 7 - 10

### Data (same as NNW WNW)

    datacells <-
      cells %>%
      filter(row >= 3, col >= 3, !is.na(chr)) %>%
      mutate(value = as.integer(chr)) %>%
      select(row, col, value)
    datacells

    ## # A tibble: 72 x 3
    ##      row   col value
    ##    <int> <int> <int>
    ##  1     3     3  7000
    ##  2     4     3 12000
    ##  3     5     3 10000
    ##  4     7     3 29000
    ##  5     8     3 34000
    ##  6     9     3 30000
    ##  7    10     3 12000
    ##  8    12     3 10000
    ##  9    13     3  6000
    ## 10    14     3  5000
    ## # ... with 62 more rows

### Join headers to data (different from NNW WNW)

    datacells <-
      datacells %>%
      NNE(col_headers[[1]]) %>% # Different from NNW WNW
      N(col_headers[[2]]) %>% # Same as NNW WNW
      WSW(row_headers[[1]]) %>% # Different from NNW WNW
      W(row_headers[[2]]) # Same as NNW WNW

### Result

    datacells %>% as.data.frame

    ##    row col  value header.data header.header           header.data.data
    ## 1    3   3   7000      Female         0 - 6          Bachelor's degree
    ## 2    4   3  12000      Female         0 - 6          Bachelor's degree
    ## 3    5   3  10000      Female         0 - 6          Bachelor's degree
    ## 4    7   3  29000      Female         0 - 6                Certificate
    ## 5    8   3  34000      Female         0 - 6                Certificate
    ## 6    9   3  30000      Female         0 - 6                Certificate
    ## 7   10   3  12000      Female         0 - 6                Certificate
    ## 8   12   3  10000      Female         0 - 6                    Diploma
    ## 9   13   3   6000      Female         0 - 6                    Diploma
    ## 10  14   3   5000      Female         0 - 6                    Diploma
    ## 11  15   3  10000      Female         0 - 6           No Qualification
    ## 12  16   3  11000      Female         0 - 6           No Qualification
    ## 13  17   3  19000      Female         0 - 6           No Qualification
    ## 14  18   3  16000      Female         0 - 6           No Qualification
    ## 15  20   3   5000      Female         0 - 6 Postgraduate qualification
    ## 16  21   3   6000      Female         0 - 6 Postgraduate qualification
    ## 17   3   4  27000      Female        7 - 10          Bachelor's degree
    ## 18   4   4 137000      Female        7 - 10          Bachelor's degree
    ## 19   5   4  64000      Female        7 - 10          Bachelor's degree
    ## 20   6   4  18000      Female        7 - 10          Bachelor's degree
    ## 21   7   4 161000      Female        7 - 10                Certificate
    ## 22   8   4 179000      Female        7 - 10                Certificate
    ## 23   9   4 210000      Female        7 - 10                Certificate
    ## 24  10   4  77000      Female        7 - 10                Certificate
    ## 25  11   4  14000      Female        7 - 10                    Diploma
    ## 26  12   4  66000      Female        7 - 10                    Diploma
    ## 27  13   4  68000      Female        7 - 10                    Diploma
    ## 28  14   4  41000      Female        7 - 10                    Diploma
    ## 29  15   4  43000      Female        7 - 10           No Qualification
    ## 30  16   4  36000      Female        7 - 10           No Qualification
    ## 31  17   4  91000      Female        7 - 10           No Qualification
    ## 32  18   4 118000      Female        7 - 10           No Qualification
    ## 33  19   4   6000      Female        7 - 10 Postgraduate qualification
    ## 34  20   4  86000      Female        7 - 10 Postgraduate qualification
    ## 35  21   4  55000      Female        7 - 10 Postgraduate qualification
    ## 36  22   4  13000      Female        7 - 10 Postgraduate qualification
    ## 37   4   5   9000        Male         0 - 6          Bachelor's degree
    ## 38   5   5   7000        Male         0 - 6          Bachelor's degree
    ## 39   6   5   7000        Male         0 - 6          Bachelor's degree
    ## 40   7   5  30000        Male         0 - 6                Certificate
    ## 41   8   5  31000        Male         0 - 6                Certificate
    ## 42   9   5  23000        Male         0 - 6                Certificate
    ## 43  10   5   8000        Male         0 - 6                Certificate
    ## 44  11   5   9000        Male         0 - 6                    Diploma
    ## 45  12   5   8000        Male         0 - 6                    Diploma
    ## 46  13   5   5000        Male         0 - 6                    Diploma
    ## 47  14   5   1000        Male         0 - 6                    Diploma
    ## 48  15   5  12000        Male         0 - 6           No Qualification
    ## 49  16   5  21000        Male         0 - 6           No Qualification
    ## 50  17   5  17000        Male         0 - 6           No Qualification
    ## 51  18   5   9000        Male         0 - 6           No Qualification
    ## 52  20   5   7000        Male         0 - 6 Postgraduate qualification
    ## 53  21   5   6000        Male         0 - 6 Postgraduate qualification
    ## 54   3   6  13000        Male        7 - 10          Bachelor's degree
    ## 55   4   6  81000        Male        7 - 10          Bachelor's degree
    ## 56   5   6  66000        Male        7 - 10          Bachelor's degree
    ## 57   6   6  17000        Male        7 - 10          Bachelor's degree
    ## 58   7   6 190000        Male        7 - 10                Certificate
    ## 59   8   6 219000        Male        7 - 10                Certificate
    ## 60   9   6 199000        Male        7 - 10                Certificate
    ## 61  10   6 107000        Male        7 - 10                Certificate
    ## 62  11   6  11000        Male        7 - 10                    Diploma
    ## 63  12   6  47000        Male        7 - 10                    Diploma
    ## 64  13   6  58000        Male        7 - 10                    Diploma
    ## 65  14   6  34000        Male        7 - 10                    Diploma
    ## 66  15   6  37000        Male        7 - 10           No Qualification
    ## 67  16   6  50000        Male        7 - 10           No Qualification
    ## 68  17   6  75000        Male        7 - 10           No Qualification
    ## 69  18   6  66000        Male        7 - 10           No Qualification
    ## 70  20   6  60000        Male        7 - 10 Postgraduate qualification
    ## 71  21   6  68000        Male        7 - 10 Postgraduate qualification
    ## 72  22   6  18000        Male        7 - 10 Postgraduate qualification
    ##    header.header.header
    ## 1               15 - 24
    ## 2               25 - 44
    ## 3               45 - 64
    ## 4               15 - 24
    ## 5               25 - 44
    ## 6               45 - 64
    ## 7                   65+
    ## 8               25 - 44
    ## 9               45 - 64
    ## 10                  65+
    ## 11              15 - 24
    ## 12              25 - 44
    ## 13              45 - 64
    ## 14                  65+
    ## 15              25 - 44
    ## 16              45 - 64
    ## 17              15 - 24
    ## 18              25 - 44
    ## 19              45 - 64
    ## 20                  65+
    ## 21              15 - 24
    ## 22              25 - 44
    ## 23              45 - 64
    ## 24                  65+
    ## 25              15 - 24
    ## 26              25 - 44
    ## 27              45 - 64
    ## 28                  65+
    ## 29              15 - 24
    ## 30              25 - 44
    ## 31              45 - 64
    ## 32                  65+
    ## 33              15 - 24
    ## 34              25 - 44
    ## 35              45 - 64
    ## 36                  65+
    ## 37              25 - 44
    ## 38              45 - 64
    ## 39                  65+
    ## 40              15 - 24
    ## 41              25 - 44
    ## 42              45 - 64
    ## 43                  65+
    ## 44              15 - 24
    ## 45              25 - 44
    ## 46              45 - 64
    ## 47                  65+
    ## 48              15 - 24
    ## 49              25 - 44
    ## 50              45 - 64
    ## 51                  65+
    ## 52              25 - 44
    ## 53              45 - 64
    ## 54              15 - 24
    ## 55              25 - 44
    ## 56              45 - 64
    ## 57                  65+
    ## 58              15 - 24
    ## 59              25 - 44
    ## 60              45 - 64
    ## 61                  65+
    ## 62              15 - 24
    ## 63              25 - 44
    ## 64              45 - 64
    ## 65                  65+
    ## 66              15 - 24
    ## 67              25 - 44
    ## 68              45 - 64
    ## 69                  65+
    ## 70              25 - 44
    ## 71              45 - 64
    ## 72                  65+

    NNE_WSW <- datacells %>% arrange(row, col)

## SSE ESE example

    cells <- tidy_table(purpose$`SSE ESE`)

### Headers

    row_headers <-
      cells %>%
      filter(col >= 5, !is.na(chr)) %>%
      select(row, col, header = chr) %>%
      split(.$col)
    row_headers

    ## $`5`
    ## # A tibble: 20 x 3
    ##      row   col header
    ##    <int> <int> <chr>
    ##  1     1     5 15 - 24
    ##  2     2     5 25 - 44
    ##  3     3     5 45 - 64
    ##  4     4     5 65+
    ##  5     5     5 15 - 24
    ##  6     6     5 25 - 44
    ##  7     7     5 45 - 64
    ##  8     8     5 65+
    ##  9     9     5 15 - 24
    ## 10    10     5 25 - 44
    ## 11    11     5 45 - 64
    ## 12    12     5 65+
    ## 13    13     5 15 - 24
    ## 14    14     5 25 - 44
    ## 15    15     5 45 - 64
    ## 16    16     5 65+
    ## 17    17     5 15 - 24
    ## 18    18     5 25 - 44
    ## 19    19     5 45 - 64
    ## 20    20     5 65+
    ##
    ## $`6`
    ## # A tibble: 5 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     4     6 Bachelor's degree
    ## 2     8     6 Certificate
    ## 3    12     6 Diploma
    ## 4    16     6 No Qualification
    ## 5    20     6 Postgraduate qualification

    col_headers <-
      cells %>%
      filter(row >= 21, !is.na(chr)) %>%
      select(row, col, header = chr) %>%
      split(.$row)
    col_headers

    ## $`21`
    ## # A tibble: 4 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1    21     1 0 - 6
    ## 2    21     2 7 - 10
    ## 3    21     3 0 - 6
    ## 4    21     4 7 - 10
    ##
    ## $`22`
    ## # A tibble: 2 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1    22     2 Female
    ## 2    22     4 Male

### Data

    datacells <-
      cells %>%
      filter(row <= 20, col <= 4, !is.na(chr)) %>%
      mutate(value = as.integer(chr)) %>%
      select(row, col, value)
    datacells

    ## # A tibble: 72 x 3
    ##      row   col value
    ##    <int> <int> <int>
    ##  1     1     1  7000
    ##  2     2     1 12000
    ##  3     3     1 10000
    ##  4     5     1 29000
    ##  5     6     1 34000
    ##  6     7     1 30000
    ##  7     8     1 12000
    ##  8    10     1 10000
    ##  9    11     1  6000
    ## 10    12     1  5000
    ## # ... with 62 more rows

### Join headers to data

    datacells <-
      datacells %>%
      SSE(col_headers[[2]]) %>%
      S(col_headers[[1]]) %>%
      ESE(row_headers[[2]]) %>%
      E(row_headers[[1]])

### Result

    datacells %>% as.data.frame

    ##    row col  value header.data header.header           header.data.data
    ## 1    1   1   7000      Female         0 - 6          Bachelor's degree
    ## 2    2   1  12000      Female         0 - 6          Bachelor's degree
    ## 3    3   1  10000      Female         0 - 6          Bachelor's degree
    ## 4    5   1  29000      Female         0 - 6                Certificate
    ## 5    6   1  34000      Female         0 - 6                Certificate
    ## 6    7   1  30000      Female         0 - 6                Certificate
    ## 7    8   1  12000      Female         0 - 6                Certificate
    ## 8   10   1  10000      Female         0 - 6                    Diploma
    ## 9   11   1   6000      Female         0 - 6                    Diploma
    ## 10  12   1   5000      Female         0 - 6                    Diploma
    ## 11  13   1  10000      Female         0 - 6           No Qualification
    ## 12  14   1  11000      Female         0 - 6           No Qualification
    ## 13  15   1  19000      Female         0 - 6           No Qualification
    ## 14  16   1  16000      Female         0 - 6           No Qualification
    ## 15  18   1   5000      Female         0 - 6 Postgraduate qualification
    ## 16  19   1   6000      Female         0 - 6 Postgraduate qualification
    ## 17   1   2  27000      Female        7 - 10          Bachelor's degree
    ## 18   2   2 137000      Female        7 - 10          Bachelor's degree
    ## 19   3   2  64000      Female        7 - 10          Bachelor's degree
    ## 20   4   2  18000      Female        7 - 10          Bachelor's degree
    ## 21   5   2 161000      Female        7 - 10                Certificate
    ## 22   6   2 179000      Female        7 - 10                Certificate
    ## 23   7   2 210000      Female        7 - 10                Certificate
    ## 24   8   2  77000      Female        7 - 10                Certificate
    ## 25   9   2  14000      Female        7 - 10                    Diploma
    ## 26  10   2  66000      Female        7 - 10                    Diploma
    ## 27  11   2  68000      Female        7 - 10                    Diploma
    ## 28  12   2  41000      Female        7 - 10                    Diploma
    ## 29  13   2  43000      Female        7 - 10           No Qualification
    ## 30  14   2  36000      Female        7 - 10           No Qualification
    ## 31  15   2  91000      Female        7 - 10           No Qualification
    ## 32  16   2 118000      Female        7 - 10           No Qualification
    ## 33  17   2   6000      Female        7 - 10 Postgraduate qualification
    ## 34  18   2  86000      Female        7 - 10 Postgraduate qualification
    ## 35  19   2  55000      Female        7 - 10 Postgraduate qualification
    ## 36  20   2  13000      Female        7 - 10 Postgraduate qualification
    ## 37   2   3   9000        Male         0 - 6          Bachelor's degree
    ## 38   3   3   7000        Male         0 - 6          Bachelor's degree
    ## 39   4   3   7000        Male         0 - 6          Bachelor's degree
    ## 40   5   3  30000        Male         0 - 6                Certificate
    ## 41   6   3  31000        Male         0 - 6                Certificate
    ## 42   7   3  23000        Male         0 - 6                Certificate
    ## 43   8   3   8000        Male         0 - 6                Certificate
    ## 44   9   3   9000        Male         0 - 6                    Diploma
    ## 45  10   3   8000        Male         0 - 6                    Diploma
    ## 46  11   3   5000        Male         0 - 6                    Diploma
    ## 47  12   3   1000        Male         0 - 6                    Diploma
    ## 48  13   3  12000        Male         0 - 6           No Qualification
    ## 49  14   3  21000        Male         0 - 6           No Qualification
    ## 50  15   3  17000        Male         0 - 6           No Qualification
    ## 51  16   3   9000        Male         0 - 6           No Qualification
    ## 52  18   3   7000        Male         0 - 6 Postgraduate qualification
    ## 53  19   3   6000        Male         0 - 6 Postgraduate qualification
    ## 54   1   4  13000        Male        7 - 10          Bachelor's degree
    ## 55   2   4  81000        Male        7 - 10          Bachelor's degree
    ## 56   3   4  66000        Male        7 - 10          Bachelor's degree
    ## 57   4   4  17000        Male        7 - 10          Bachelor's degree
    ## 58   5   4 190000        Male        7 - 10                Certificate
    ## 59   6   4 219000        Male        7 - 10                Certificate
    ## 60   7   4 199000        Male        7 - 10                Certificate
    ## 61   8   4 107000        Male        7 - 10                Certificate
    ## 62   9   4  11000        Male        7 - 10                    Diploma
    ## 63  10   4  47000        Male        7 - 10                    Diploma
    ## 64  11   4  58000        Male        7 - 10                    Diploma
    ## 65  12   4  34000        Male        7 - 10                    Diploma
    ## 66  13   4  37000        Male        7 - 10           No Qualification
    ## 67  14   4  50000        Male        7 - 10           No Qualification
    ## 68  15   4  75000        Male        7 - 10           No Qualification
    ## 69  16   4  66000        Male        7 - 10           No Qualification
    ## 70  18   4  60000        Male        7 - 10 Postgraduate qualification
    ## 71  19   4  68000        Male        7 - 10 Postgraduate qualification
    ## 72  20   4  18000        Male        7 - 10 Postgraduate qualification
    ##    header.header.header
    ## 1               15 - 24
    ## 2               25 - 44
    ## 3               45 - 64
    ## 4               15 - 24
    ## 5               25 - 44
    ## 6               45 - 64
    ## 7                   65+
    ## 8               25 - 44
    ## 9               45 - 64
    ## 10                  65+
    ## 11              15 - 24
    ## 12              25 - 44
    ## 13              45 - 64
    ## 14                  65+
    ## 15              25 - 44
    ## 16              45 - 64
    ## 17              15 - 24
    ## 18              25 - 44
    ## 19              45 - 64
    ## 20                  65+
    ## 21              15 - 24
    ## 22              25 - 44
    ## 23              45 - 64
    ## 24                  65+
    ## 25              15 - 24
    ## 26              25 - 44
    ## 27              45 - 64
    ## 28                  65+
    ## 29              15 - 24
    ## 30              25 - 44
    ## 31              45 - 64
    ## 32                  65+
    ## 33              15 - 24
    ## 34              25 - 44
    ## 35              45 - 64
    ## 36                  65+
    ## 37              25 - 44
    ## 38              45 - 64
    ## 39                  65+
    ## 40              15 - 24
    ## 41              25 - 44
    ## 42              45 - 64
    ## 43                  65+
    ## 44              15 - 24
    ## 45              25 - 44
    ## 46              45 - 64
    ## 47                  65+
    ## 48              15 - 24
    ## 49              25 - 44
    ## 50              45 - 64
    ## 51                  65+
    ## 52              25 - 44
    ## 53              45 - 64
    ## 54              15 - 24
    ## 55              25 - 44
    ## 56              45 - 64
    ## 57                  65+
    ## 58              15 - 24
    ## 59              25 - 44
    ## 60              45 - 64
    ## 61                  65+
    ## 62              15 - 24
    ## 63              25 - 44
    ## 64              45 - 64
    ## 65                  65+
    ## 66              15 - 24
    ## 67              25 - 44
    ## 68              45 - 64
    ## 69                  65+
    ## 70              25 - 44
    ## 71              45 - 64
    ## 72                  65+

    SSE_ESE <- datacells %>% arrange(row, col)

## SSW ENE example

    cells <- tidy_table(purpose$`SSW ENE`)

### Headers (same as SSE ESE)

    row_headers <-
      cells %>%
      filter(col >= 5, !is.na(chr)) %>%
      select(row, col, header = chr) %>%
      split(.$col)
    row_headers

    ## $`5`
    ## # A tibble: 20 x 3
    ##      row   col header
    ##    <int> <int> <chr>
    ##  1     1     5 15 - 24
    ##  2     2     5 25 - 44
    ##  3     3     5 45 - 64
    ##  4     4     5 65+
    ##  5     5     5 15 - 24
    ##  6     6     5 25 - 44
    ##  7     7     5 45 - 64
    ##  8     8     5 65+
    ##  9     9     5 15 - 24
    ## 10    10     5 25 - 44
    ## 11    11     5 45 - 64
    ## 12    12     5 65+
    ## 13    13     5 15 - 24
    ## 14    14     5 25 - 44
    ## 15    15     5 45 - 64
    ## 16    16     5 65+
    ## 17    17     5 15 - 24
    ## 18    18     5 25 - 44
    ## 19    19     5 45 - 64
    ## 20    20     5 65+
    ##
    ## $`6`
    ## # A tibble: 5 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     1     6 Bachelor's degree
    ## 2     5     6 Certificate
    ## 3     9     6 Diploma
    ## 4    13     6 No Qualification
    ## 5    17     6 Postgraduate qualification

    col_headers <-
      cells %>%
      filter(row >= 21, !is.na(chr)) %>%
      select(row, col, header = chr) %>%
      split(.$row)
    col_headers

    ## $`21`
    ## # A tibble: 4 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1    21     1 0 - 6
    ## 2    21     2 7 - 10
    ## 3    21     3 0 - 6
    ## 4    21     4 7 - 10
    ##
    ## $`22`
    ## # A tibble: 2 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1    22     1 Female
    ## 2    22     3 Male

### Data (same as SSE ESE)

    datacells <-
      cells %>%
      filter(row <= 20, col <= 4, !is.na(chr)) %>%
      mutate(value = as.integer(chr)) %>%
      select(row, col, value)
    datacells

    ## # A tibble: 72 x 3
    ##      row   col value
    ##    <int> <int> <int>
    ##  1     1     1  7000
    ##  2     2     1 12000
    ##  3     3     1 10000
    ##  4     5     1 29000
    ##  5     6     1 34000
    ##  6     7     1 30000
    ##  7     8     1 12000
    ##  8    10     1 10000
    ##  9    11     1  6000
    ## 10    12     1  5000
    ## # ... with 62 more rows

### Join headers to data

    datacells <-
      datacells %>%
      SSW(col_headers[[2]]) %>% # Different from SSE ESE
      S(col_headers[[1]]) %>% # Same as SSE ESE
      ENE(row_headers[[2]]) %>% # Different from SSE ESE
      E(row_headers[[1]]) # Same as SSE ESE

### Result

    datacells %>% as.data.frame

    ##    row col  value header.data header.header           header.data.data
    ## 1    1   1   7000      Female         0 - 6          Bachelor's degree
    ## 2    2   1  12000      Female         0 - 6          Bachelor's degree
    ## 3    3   1  10000      Female         0 - 6          Bachelor's degree
    ## 4    5   1  29000      Female         0 - 6                Certificate
    ## 5    6   1  34000      Female         0 - 6                Certificate
    ## 6    7   1  30000      Female         0 - 6                Certificate
    ## 7    8   1  12000      Female         0 - 6                Certificate
    ## 8   10   1  10000      Female         0 - 6                    Diploma
    ## 9   11   1   6000      Female         0 - 6                    Diploma
    ## 10  12   1   5000      Female         0 - 6                    Diploma
    ## 11  13   1  10000      Female         0 - 6           No Qualification
    ## 12  14   1  11000      Female         0 - 6           No Qualification
    ## 13  15   1  19000      Female         0 - 6           No Qualification
    ## 14  16   1  16000      Female         0 - 6           No Qualification
    ## 15  18   1   5000      Female         0 - 6 Postgraduate qualification
    ## 16  19   1   6000      Female         0 - 6 Postgraduate qualification
    ## 17   1   2  27000      Female        7 - 10          Bachelor's degree
    ## 18   2   2 137000      Female        7 - 10          Bachelor's degree
    ## 19   3   2  64000      Female        7 - 10          Bachelor's degree
    ## 20   4   2  18000      Female        7 - 10          Bachelor's degree
    ## 21   5   2 161000      Female        7 - 10                Certificate
    ## 22   6   2 179000      Female        7 - 10                Certificate
    ## 23   7   2 210000      Female        7 - 10                Certificate
    ## 24   8   2  77000      Female        7 - 10                Certificate
    ## 25   9   2  14000      Female        7 - 10                    Diploma
    ## 26  10   2  66000      Female        7 - 10                    Diploma
    ## 27  11   2  68000      Female        7 - 10                    Diploma
    ## 28  12   2  41000      Female        7 - 10                    Diploma
    ## 29  13   2  43000      Female        7 - 10           No Qualification
    ## 30  14   2  36000      Female        7 - 10           No Qualification
    ## 31  15   2  91000      Female        7 - 10           No Qualification
    ## 32  16   2 118000      Female        7 - 10           No Qualification
    ## 33  17   2   6000      Female        7 - 10 Postgraduate qualification
    ## 34  18   2  86000      Female        7 - 10 Postgraduate qualification
    ## 35  19   2  55000      Female        7 - 10 Postgraduate qualification
    ## 36  20   2  13000      Female        7 - 10 Postgraduate qualification
    ## 37   2   3   9000        Male         0 - 6          Bachelor's degree
    ## 38   3   3   7000        Male         0 - 6          Bachelor's degree
    ## 39   4   3   7000        Male         0 - 6          Bachelor's degree
    ## 40   5   3  30000        Male         0 - 6                Certificate
    ## 41   6   3  31000        Male         0 - 6                Certificate
    ## 42   7   3  23000        Male         0 - 6                Certificate
    ## 43   8   3   8000        Male         0 - 6                Certificate
    ## 44   9   3   9000        Male         0 - 6                    Diploma
    ## 45  10   3   8000        Male         0 - 6                    Diploma
    ## 46  11   3   5000        Male         0 - 6                    Diploma
    ## 47  12   3   1000        Male         0 - 6                    Diploma
    ## 48  13   3  12000        Male         0 - 6           No Qualification
    ## 49  14   3  21000        Male         0 - 6           No Qualification
    ## 50  15   3  17000        Male         0 - 6           No Qualification
    ## 51  16   3   9000        Male         0 - 6           No Qualification
    ## 52  18   3   7000        Male         0 - 6 Postgraduate qualification
    ## 53  19   3   6000        Male         0 - 6 Postgraduate qualification
    ## 54   1   4  13000        Male        7 - 10          Bachelor's degree
    ## 55   2   4  81000        Male        7 - 10          Bachelor's degree
    ## 56   3   4  66000        Male        7 - 10          Bachelor's degree
    ## 57   4   4  17000        Male        7 - 10          Bachelor's degree
    ## 58   5   4 190000        Male        7 - 10                Certificate
    ## 59   6   4 219000        Male        7 - 10                Certificate
    ## 60   7   4 199000        Male        7 - 10                Certificate
    ## 61   8   4 107000        Male        7 - 10                Certificate
    ## 62   9   4  11000        Male        7 - 10                    Diploma
    ## 63  10   4  47000        Male        7 - 10                    Diploma
    ## 64  11   4  58000        Male        7 - 10                    Diploma
    ## 65  12   4  34000        Male        7 - 10                    Diploma
    ## 66  13   4  37000        Male        7 - 10           No Qualification
    ## 67  14   4  50000        Male        7 - 10           No Qualification
    ## 68  15   4  75000        Male        7 - 10           No Qualification
    ## 69  16   4  66000        Male        7 - 10           No Qualification
    ## 70  18   4  60000        Male        7 - 10 Postgraduate qualification
    ## 71  19   4  68000        Male        7 - 10 Postgraduate qualification
    ## 72  20   4  18000        Male        7 - 10 Postgraduate qualification
    ##    header.header.header
    ## 1               15 - 24
    ## 2               25 - 44
    ## 3               45 - 64
    ## 4               15 - 24
    ## 5               25 - 44
    ## 6               45 - 64
    ## 7                   65+
    ## 8               25 - 44
    ## 9               45 - 64
    ## 10                  65+
    ## 11              15 - 24
    ## 12              25 - 44
    ## 13              45 - 64
    ## 14                  65+
    ## 15              25 - 44
    ## 16              45 - 64
    ## 17              15 - 24
    ## 18              25 - 44
    ## 19              45 - 64
    ## 20                  65+
    ## 21              15 - 24
    ## 22              25 - 44
    ## 23              45 - 64
    ## 24                  65+
    ## 25              15 - 24
    ## 26              25 - 44
    ## 27              45 - 64
    ## 28                  65+
    ## 29              15 - 24
    ## 30              25 - 44
    ## 31              45 - 64
    ## 32                  65+
    ## 33              15 - 24
    ## 34              25 - 44
    ## 35              45 - 64
    ## 36                  65+
    ## 37              25 - 44
    ## 38              45 - 64
    ## 39                  65+
    ## 40              15 - 24
    ## 41              25 - 44
    ## 42              45 - 64
    ## 43                  65+
    ## 44              15 - 24
    ## 45              25 - 44
    ## 46              45 - 64
    ## 47                  65+
    ## 48              15 - 24
    ## 49              25 - 44
    ## 50              45 - 64
    ## 51                  65+
    ## 52              25 - 44
    ## 53              45 - 64
    ## 54              15 - 24
    ## 55              25 - 44
    ## 56              45 - 64
    ## 57                  65+
    ## 58              15 - 24
    ## 59              25 - 44
    ## 60              45 - 64
    ## 61                  65+
    ## 62              15 - 24
    ## 63              25 - 44
    ## 64              45 - 64
    ## 65                  65+
    ## 66              15 - 24
    ## 67              25 - 44
    ## 68              45 - 64
    ## 69                  65+
    ## 70              25 - 44
    ## 71              45 - 64
    ## 72                  65+

    SSW_ENE <- datacells %>% arrange(row, col)

## Check that all sheets are parsed correctly

    identical(NNW_WNW, NNE_WSW)

    ## [1] TRUE

    identical(SSW_ENE, SSE_ESE)

    ## [1] TRUE

    identical(NNW_WNW[, -1:-2], SSW_ENE[, -1:-2])

    ## [1] TRUE

## ABOVE LEFT example

    cells <- tidy_table(purpose$`ABOVE LEFT`)

### Headers

    row_headers <-
      cells %>%
      filter(col <= 2, !is.na(chr)) %>%
      select(row, col, header = chr) %>%
      split(.$col)
    row_headers

    ## $`1`
    ## # A tibble: 2 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     5     1 Female
    ## 2    10     1 Male
    ##
    ## $`2`
    ## # A tibble: 10 x 3
    ##      row   col header
    ##    <int> <int> <chr>
    ##  1     3     2 Bachelor's degree
    ##  2     4     2 Certificate
    ##  3     5     2 Diploma
    ##  4     6     2 No Qualification
    ##  5     7     2 Postgraduate qualification
    ##  6     8     2 Bachelor's degree
    ##  7     9     2 Certificate
    ##  8    10     2 Diploma
    ##  9    11     2 No Qualification
    ## 10    12     2 Postgraduate qualification

    col_headers <-
      cells %>%
      filter(row <= 2, !is.na(chr)) %>%
      select(row, col, header = chr) %>%
      split(.$row)
    col_headers

    ## $`1`
    ## # A tibble: 2 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     1     4 0 - 6
    ## 2     1     7 7 - 10
    ##
    ## $`2`
    ## # A tibble: 6 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     2     3 15 - 24
    ## 2     2     4 25 - 44
    ## 3     2     5 45 - 64
    ## 4     2     6 15 - 24
    ## 5     2     7 25 - 44
    ## 6     2     8 45 - 64

### Data

    datacells <-
      cells %>%
      filter(row >= 3, col >= 3, !is.na(chr)) %>%
      mutate(value = as.integer(chr)) %>%
      select(row, col, value)
    datacells

    ## # A tibble: 55 x 3
    ##      row   col value
    ##    <int> <int> <int>
    ##  1     3     3  7000
    ##  2     4     3 29000
    ##  3     6     3 10000
    ##  4     9     3 30000
    ##  5    10     3  9000
    ##  6    11     3 12000
    ##  7     3     4 12000
    ##  8     4     4 34000
    ##  9     5     4 10000
    ## 10     6     4 11000
    ## # ... with 45 more rows

### Join headers to data

    datacells <-
      datacells %>%
      ABOVE(col_headers[[1]]) %>% # Different from SSE ESE
      N(col_headers[[2]]) %>% # Same as SSE ESE
      LEFT(row_headers[[1]]) %>% # Different from SSE ESE
      W(row_headers[[2]]) # Same as SSE ESE

### Result

    datacells %>% as.data.frame

    ##    row col  value header.data header.header header.data.data
    ## 1    3   3   7000       0 - 6       15 - 24           Female
    ## 2    4   3  29000       0 - 6       15 - 24           Female
    ## 3    6   3  10000       0 - 6       15 - 24           Female
    ## 4    9   3  30000       0 - 6       15 - 24             Male
    ## 5   10   3   9000       0 - 6       15 - 24             Male
    ## 6   11   3  12000       0 - 6       15 - 24             Male
    ## 7    3   4  12000       0 - 6       25 - 44           Female
    ## 8    4   4  34000       0 - 6       25 - 44           Female
    ## 9    5   4  10000       0 - 6       25 - 44           Female
    ## 10   6   4  11000       0 - 6       25 - 44           Female
    ## 11   7   4   5000       0 - 6       25 - 44           Female
    ## 12   8   4   9000       0 - 6       25 - 44             Male
    ## 13   9   4  31000       0 - 6       25 - 44             Male
    ## 14  10   4   8000       0 - 6       25 - 44             Male
    ## 15  11   4  21000       0 - 6       25 - 44             Male
    ## 16  12   4   7000       0 - 6       25 - 44             Male
    ## 17   3   5  10000       0 - 6       45 - 64           Female
    ## 18   4   5  30000       0 - 6       45 - 64           Female
    ## 19   5   5   6000       0 - 6       45 - 64           Female
    ## 20   6   5  19000       0 - 6       45 - 64           Female
    ## 21   7   5   6000       0 - 6       45 - 64           Female
    ## 22   8   5   7000       0 - 6       45 - 64             Male
    ## 23   9   5  23000       0 - 6       45 - 64             Male
    ## 24  10   5   5000       0 - 6       45 - 64             Male
    ## 25  11   5  17000       0 - 6       45 - 64             Male
    ## 26  12   5   6000       0 - 6       45 - 64             Male
    ## 27   3   6  27000      7 - 10       15 - 24           Female
    ## 28   4   6 161000      7 - 10       15 - 24           Female
    ## 29   5   6  14000      7 - 10       15 - 24           Female
    ## 30   6   6  43000      7 - 10       15 - 24           Female
    ## 31   7   6   6000      7 - 10       15 - 24           Female
    ## 32   8   6  13000      7 - 10       15 - 24             Male
    ## 33   9   6 190000      7 - 10       15 - 24             Male
    ## 34  10   6  11000      7 - 10       15 - 24             Male
    ## 35  11   6  37000      7 - 10       15 - 24             Male
    ## 36   3   7 137000      7 - 10       25 - 44           Female
    ## 37   4   7 179000      7 - 10       25 - 44           Female
    ## 38   5   7  66000      7 - 10       25 - 44           Female
    ## 39   6   7  36000      7 - 10       25 - 44           Female
    ## 40   7   7  86000      7 - 10       25 - 44           Female
    ## 41   8   7  81000      7 - 10       25 - 44             Male
    ## 42   9   7 219000      7 - 10       25 - 44             Male
    ## 43  10   7  47000      7 - 10       25 - 44             Male
    ## 44  11   7  50000      7 - 10       25 - 44             Male
    ## 45  12   7  60000      7 - 10       25 - 44             Male
    ## 46   3   8  64000      7 - 10       45 - 64           Female
    ## 47   4   8 210000      7 - 10       45 - 64           Female
    ## 48   5   8  68000      7 - 10       45 - 64           Female
    ## 49   6   8  91000      7 - 10       45 - 64           Female
    ## 50   7   8  55000      7 - 10       45 - 64           Female
    ## 51   8   8  66000      7 - 10       45 - 64             Male
    ## 52   9   8 199000      7 - 10       45 - 64             Male
    ## 53  10   8  58000      7 - 10       45 - 64             Male
    ## 54  11   8  75000      7 - 10       45 - 64             Male
    ## 55  12   8  68000      7 - 10       45 - 64             Male
    ##          header.header.header
    ## 1           Bachelor's degree
    ## 2                 Certificate
    ## 3            No Qualification
    ## 4                 Certificate
    ## 5                     Diploma
    ## 6            No Qualification
    ## 7           Bachelor's degree
    ## 8                 Certificate
    ## 9                     Diploma
    ## 10           No Qualification
    ## 11 Postgraduate qualification
    ## 12          Bachelor's degree
    ## 13                Certificate
    ## 14                    Diploma
    ## 15           No Qualification
    ## 16 Postgraduate qualification
    ## 17          Bachelor's degree
    ## 18                Certificate
    ## 19                    Diploma
    ## 20           No Qualification
    ## 21 Postgraduate qualification
    ## 22          Bachelor's degree
    ## 23                Certificate
    ## 24                    Diploma
    ## 25           No Qualification
    ## 26 Postgraduate qualification
    ## 27          Bachelor's degree
    ## 28                Certificate
    ## 29                    Diploma
    ## 30           No Qualification
    ## 31 Postgraduate qualification
    ## 32          Bachelor's degree
    ## 33                Certificate
    ## 34                    Diploma
    ## 35           No Qualification
    ## 36          Bachelor's degree
    ## 37                Certificate
    ## 38                    Diploma
    ## 39           No Qualification
    ## 40 Postgraduate qualification
    ## 41          Bachelor's degree
    ## 42                Certificate
    ## 43                    Diploma
    ## 44           No Qualification
    ## 45 Postgraduate qualification
    ## 46          Bachelor's degree
    ## 47                Certificate
    ## 48                    Diploma
    ## 49           No Qualification
    ## 50 Postgraduate qualification
    ## 51          Bachelor's degree
    ## 52                Certificate
    ## 53                    Diploma
    ## 54           No Qualification
    ## 55 Postgraduate qualification

    ABOVE_LEFT <- datacells %>% arrange(row, col)

## BELOW RIGHT example

    cells <- tidy_table(purpose$`BELOW RIGHT`)

### Headers

    row_headers <-
      cells %>%
      filter(col >= 7, !is.na(chr)) %>%
      select(row, col, header = chr) %>%
      split(.$col)
    row_headers

    ## $`7`
    ## # A tibble: 10 x 3
    ##      row   col header
    ##    <int> <int> <chr>
    ##  1     1     7 Bachelor's degree
    ##  2     2     7 Certificate
    ##  3     3     7 Diploma
    ##  4     4     7 No Qualification
    ##  5     5     7 Postgraduate qualification
    ##  6     6     7 Bachelor's degree
    ##  7     7     7 Certificate
    ##  8     8     7 Diploma
    ##  9     9     7 No Qualification
    ## 10    10     7 Postgraduate qualification
    ##
    ## $`8`
    ## # A tibble: 2 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     3     8 Female
    ## 2     8     8 Male

    col_headers <-
      cells %>%
      filter(row >= 11, !is.na(chr)) %>%
      select(row, col, header = chr) %>%
      split(.$row)
    col_headers

    ## $`11`
    ## # A tibble: 6 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1    11     1 15 - 24
    ## 2    11     2 25 - 44
    ## 3    11     3 45 - 64
    ## 4    11     4 15 - 24
    ## 5    11     5 25 - 44
    ## 6    11     6 45 - 64
    ##
    ## $`12`
    ## # A tibble: 2 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1    12     2 0 - 6
    ## 2    12     5 7 - 10

### Data

    datacells <-
      cells %>%
      filter(row <= 10, col <= 6, !is.na(chr)) %>%
      mutate(value = as.integer(chr)) %>%
      select(row, col, value)
    datacells

    ## # A tibble: 55 x 3
    ##      row   col value
    ##    <int> <int> <int>
    ##  1     1     1  7000
    ##  2     2     1 29000
    ##  3     4     1 10000
    ##  4     7     1 30000
    ##  5     8     1  9000
    ##  6     9     1 12000
    ##  7     1     2 12000
    ##  8     2     2 34000
    ##  9     3     2 10000
    ## 10     4     2 11000
    ## # ... with 45 more rows

### Join headers to data

    datacells <-
      datacells %>%
      BELOW(col_headers[[2]]) %>%
      S(col_headers[[1]]) %>%
      RIGHT(row_headers[[2]]) %>%
      E(row_headers[[1]])

### Result

    datacells %>% as.data.frame

    ##    row col  value header.data header.header header.data.data
    ## 1    1   1   7000       0 - 6       15 - 24           Female
    ## 2    2   1  29000       0 - 6       15 - 24           Female
    ## 3    4   1  10000       0 - 6       15 - 24           Female
    ## 4    7   1  30000       0 - 6       15 - 24             Male
    ## 5    8   1   9000       0 - 6       15 - 24             Male
    ## 6    9   1  12000       0 - 6       15 - 24             Male
    ## 7    1   2  12000       0 - 6       25 - 44           Female
    ## 8    2   2  34000       0 - 6       25 - 44           Female
    ## 9    3   2  10000       0 - 6       25 - 44           Female
    ## 10   4   2  11000       0 - 6       25 - 44           Female
    ## 11   5   2   5000       0 - 6       25 - 44           Female
    ## 12   6   2   9000       0 - 6       25 - 44             Male
    ## 13   7   2  31000       0 - 6       25 - 44             Male
    ## 14   8   2   8000       0 - 6       25 - 44             Male
    ## 15   9   2  21000       0 - 6       25 - 44             Male
    ## 16  10   2   7000       0 - 6       25 - 44             Male
    ## 17   1   3  10000       0 - 6       45 - 64           Female
    ## 18   2   3  30000       0 - 6       45 - 64           Female
    ## 19   3   3   6000       0 - 6       45 - 64           Female
    ## 20   4   3  19000       0 - 6       45 - 64           Female
    ## 21   5   3   6000       0 - 6       45 - 64           Female
    ## 22   6   3   7000       0 - 6       45 - 64             Male
    ## 23   7   3  23000       0 - 6       45 - 64             Male
    ## 24   8   3   5000       0 - 6       45 - 64             Male
    ## 25   9   3  17000       0 - 6       45 - 64             Male
    ## 26  10   3   6000       0 - 6       45 - 64             Male
    ## 27   1   4  27000      7 - 10       15 - 24           Female
    ## 28   2   4 161000      7 - 10       15 - 24           Female
    ## 29   3   4  14000      7 - 10       15 - 24           Female
    ## 30   4   4  43000      7 - 10       15 - 24           Female
    ## 31   5   4   6000      7 - 10       15 - 24           Female
    ## 32   6   4  13000      7 - 10       15 - 24             Male
    ## 33   7   4 190000      7 - 10       15 - 24             Male
    ## 34   8   4  11000      7 - 10       15 - 24             Male
    ## 35   9   4  37000      7 - 10       15 - 24             Male
    ## 36   1   5 137000      7 - 10       25 - 44           Female
    ## 37   2   5 179000      7 - 10       25 - 44           Female
    ## 38   3   5  66000      7 - 10       25 - 44           Female
    ## 39   4   5  36000      7 - 10       25 - 44           Female
    ## 40   5   5  86000      7 - 10       25 - 44           Female
    ## 41   6   5  81000      7 - 10       25 - 44             Male
    ## 42   7   5 219000      7 - 10       25 - 44             Male
    ## 43   8   5  47000      7 - 10       25 - 44             Male
    ## 44   9   5  50000      7 - 10       25 - 44             Male
    ## 45  10   5  60000      7 - 10       25 - 44             Male
    ## 46   1   6  64000      7 - 10       45 - 64           Female
    ## 47   2   6 210000      7 - 10       45 - 64           Female
    ## 48   3   6  68000      7 - 10       45 - 64           Female
    ## 49   4   6  91000      7 - 10       45 - 64           Female
    ## 50   5   6  55000      7 - 10       45 - 64           Female
    ## 51   6   6  66000      7 - 10       45 - 64             Male
    ## 52   7   6 199000      7 - 10       45 - 64             Male
    ## 53   8   6  58000      7 - 10       45 - 64             Male
    ## 54   9   6  75000      7 - 10       45 - 64             Male
    ## 55  10   6  68000      7 - 10       45 - 64             Male
    ##          header.header.header
    ## 1           Bachelor's degree
    ## 2                 Certificate
    ## 3            No Qualification
    ## 4                 Certificate
    ## 5                     Diploma
    ## 6            No Qualification
    ## 7           Bachelor's degree
    ## 8                 Certificate
    ## 9                     Diploma
    ## 10           No Qualification
    ## 11 Postgraduate qualification
    ## 12          Bachelor's degree
    ## 13                Certificate
    ## 14                    Diploma
    ## 15           No Qualification
    ## 16 Postgraduate qualification
    ## 17          Bachelor's degree
    ## 18                Certificate
    ## 19                    Diploma
    ## 20           No Qualification
    ## 21 Postgraduate qualification
    ## 22          Bachelor's degree
    ## 23                Certificate
    ## 24                    Diploma
    ## 25           No Qualification
    ## 26 Postgraduate qualification
    ## 27          Bachelor's degree
    ## 28                Certificate
    ## 29                    Diploma
    ## 30           No Qualification
    ## 31 Postgraduate qualification
    ## 32          Bachelor's degree
    ## 33                Certificate
    ## 34                    Diploma
    ## 35           No Qualification
    ## 36          Bachelor's degree
    ## 37                Certificate
    ## 38                    Diploma
    ## 39           No Qualification
    ## 40 Postgraduate qualification
    ## 41          Bachelor's degree
    ## 42                Certificate
    ## 43                    Diploma
    ## 44           No Qualification
    ## 45 Postgraduate qualification
    ## 46          Bachelor's degree
    ## 47                Certificate
    ## 48                    Diploma
    ## 49           No Qualification
    ## 50 Postgraduate qualification
    ## 51          Bachelor's degree
    ## 52                Certificate
    ## 53                    Diploma
    ## 54           No Qualification
    ## 55 Postgraduate qualification

    BELOW_RIGHT <- datacells %>% arrange(row, col)

## Check that all sheets are parsed correctly

    identical(ABOVE_LEFT[, -1:-2], BELOW_RIGHT[, -1:-2])

    ## [1] TRUE

## ABOVE LEFT border example

    spreadsheet <- system.file("extdata/purpose.xlsx", package = "unpivotr")
    cells <- tidy_xlsx(spreadsheet, "ABOVE LEFT border")$data[[1]]

    ## Warning: 'tidy_xlsx()' is deprecated.
    ## Use 'xlsx_cells()' or 'xlsx_formats()' instead.

### Headers

    # Same as ABOVE LEFT without borders
    row_headers <-
      cells %>%
      filter(col <= 3, !is_blank) %>%
      select(row, col, header = character) %>%
      split(.$col)
    row_headers

    ## $`2`
    ## # A tibble: 2 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     5     2 Female
    ## 2    10     2 Male
    ##
    ## $`3`
    ## # A tibble: 10 x 3
    ##      row   col header
    ##    <int> <int> <chr>
    ##  1     4     3 Bachelor's degree
    ##  2     5     3 Certificate
    ##  3     6     3 Diploma
    ##  4     7     3 No Qualification
    ##  5     8     3 Postgraduate qualification
    ##  6     9     3 Bachelor's degree
    ##  7    10     3 Certificate
    ##  8    11     3 Diploma
    ##  9    12     3 No Qualification
    ## 10    13     3 Postgraduate qualification

    col_headers <-
      cells %>%
      filter(row <= 3, !is_blank) %>%
      select(row, col, header = character) %>%
      split(.$row)
    col_headers

    ## $`2`
    ## # A tibble: 2 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     2     6 0 - 6
    ## 2     2     8 7 - 10
    ##
    ## $`3`
    ## # A tibble: 6 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     3     4 15 - 24
    ## 2     3     5 25 - 44
    ## 3     3     6 45 - 64
    ## 4     3     7 15 - 24
    ## 5     3     8 25 - 44
    ## 6     3     9 45 - 64

### Data

    # Same as ABOVE LEFT without borders
    datacells <-
      cells %>%
      filter(row >= 4, col >= 4, !is_blank) %>%
      mutate(content = ifelse(is.na(character), numeric, NA)) %>%
      mutate(value = as.integer(content)) %>%
      select(row, col, value)
    datacells

    ## # A tibble: 55 x 3
    ##      row   col  value
    ##    <int> <int>  <int>
    ##  1     4     4   7000
    ##  2     4     5  12000
    ##  3     4     6  10000
    ##  4     4     7  27000
    ##  5     4     8 137000
    ##  6     4     9  64000
    ##  7     5     4  29000
    ##  8     5     5  34000
    ##  9     5     6  30000
    ## 10     5     7 161000
    ## # ... with 45 more rows

### Borders

    # Different from ABOVE LEFT without borders
    # Find cells with borders on the bottom, and ones with borders on the left
    formatting <- tidy_xlsx(spreadsheet)$formats

    ## Warning: 'tidy_xlsx()' is deprecated.
    ## Use 'xlsx_cells()' or 'xlsx_formats()' instead.

    left_borders <- which(!is.na(formatting$local$border$left$style))
    top_borders <- which(!is.na(formatting$local$border$top$style))

    left_border_cells <-
      cells %>%
      filter(row == 2, local_format_id %in% left_borders) %>%
      select(row, col)
    top_border_cells <-
      cells %>%
      filter(col == 2, local_format_id %in% top_borders) %>%
      select(row, col)

### Join headers to data

    datacells <-
      datacells %>%
      ABOVE(col_headers[[1]], left_border_cells) %>% # Different from ABOVE LEFT
      N(col_headers[[2]]) %>% # Same as ABOVE LEFT
      LEFT(row_headers[[1]], top_border_cells) %>% # Different from ABOVE LEFT
      W(row_headers[[2]]) # Same as ABOVE LEFT

### Result

    datacells %>% as.data.frame

    ##    row col  value header.data header.header header.data.data
    ## 1    4   4   7000       0 - 6       15 - 24           Female
    ## 2    4   5  12000       0 - 6       25 - 44           Female
    ## 3    4   6  10000       0 - 6       45 - 64           Female
    ## 4    4   7  27000      7 - 10       15 - 24           Female
    ## 5    4   8 137000      7 - 10       25 - 44           Female
    ## 6    4   9  64000      7 - 10       45 - 64           Female
    ## 7    5   4  29000       0 - 6       15 - 24           Female
    ## 8    5   5  34000       0 - 6       25 - 44           Female
    ## 9    5   6  30000       0 - 6       45 - 64           Female
    ## 10   5   7 161000      7 - 10       15 - 24           Female
    ## 11   5   8 179000      7 - 10       25 - 44           Female
    ## 12   5   9 210000      7 - 10       45 - 64           Female
    ## 13   6   5  10000       0 - 6       25 - 44           Female
    ## 14   6   6   6000       0 - 6       45 - 64           Female
    ## 15   6   7  14000      7 - 10       15 - 24           Female
    ## 16   6   8  66000      7 - 10       25 - 44           Female
    ## 17   6   9  68000      7 - 10       45 - 64           Female
    ## 18   7   4  10000       0 - 6       15 - 24           Female
    ## 19   7   5  11000       0 - 6       25 - 44           Female
    ## 20   7   6  19000       0 - 6       45 - 64           Female
    ## 21   7   7  43000      7 - 10       15 - 24           Female
    ## 22   7   8  36000      7 - 10       25 - 44           Female
    ## 23   7   9  91000      7 - 10       45 - 64           Female
    ## 24   8   5   5000       0 - 6       25 - 44           Female
    ## 25   8   6   6000       0 - 6       45 - 64           Female
    ## 26   8   7   6000      7 - 10       15 - 24           Female
    ## 27   8   8  86000      7 - 10       25 - 44           Female
    ## 28   8   9  55000      7 - 10       45 - 64           Female
    ## 29   9   5   9000       0 - 6       25 - 44             Male
    ## 30   9   6   7000       0 - 6       45 - 64             Male
    ## 31   9   7  13000      7 - 10       15 - 24             Male
    ## 32   9   8  81000      7 - 10       25 - 44             Male
    ## 33   9   9  66000      7 - 10       45 - 64             Male
    ## 34  10   4  30000       0 - 6       15 - 24             Male
    ## 35  10   5  31000       0 - 6       25 - 44             Male
    ## 36  10   6  23000       0 - 6       45 - 64             Male
    ## 37  10   7 190000      7 - 10       15 - 24             Male
    ## 38  10   8 219000      7 - 10       25 - 44             Male
    ## 39  10   9 199000      7 - 10       45 - 64             Male
    ## 40  11   4   9000       0 - 6       15 - 24             Male
    ## 41  11   5   8000       0 - 6       25 - 44             Male
    ## 42  11   6   5000       0 - 6       45 - 64             Male
    ## 43  11   7  11000      7 - 10       15 - 24             Male
    ## 44  11   8  47000      7 - 10       25 - 44             Male
    ## 45  11   9  58000      7 - 10       45 - 64             Male
    ## 46  12   4  12000       0 - 6       15 - 24             Male
    ## 47  12   5  21000       0 - 6       25 - 44             Male
    ## 48  12   6  17000       0 - 6       45 - 64             Male
    ## 49  12   7  37000      7 - 10       15 - 24             Male
    ## 50  12   8  50000      7 - 10       25 - 44             Male
    ## 51  12   9  75000      7 - 10       45 - 64             Male
    ## 52  13   5   7000       0 - 6       25 - 44             Male
    ## 53  13   6   6000       0 - 6       45 - 64             Male
    ## 54  13   8  60000      7 - 10       25 - 44             Male
    ## 55  13   9  68000      7 - 10       45 - 64             Male
    ##          header.header.header
    ## 1           Bachelor's degree
    ## 2           Bachelor's degree
    ## 3           Bachelor's degree
    ## 4           Bachelor's degree
    ## 5           Bachelor's degree
    ## 6           Bachelor's degree
    ## 7                 Certificate
    ## 8                 Certificate
    ## 9                 Certificate
    ## 10                Certificate
    ## 11                Certificate
    ## 12                Certificate
    ## 13                    Diploma
    ## 14                    Diploma
    ## 15                    Diploma
    ## 16                    Diploma
    ## 17                    Diploma
    ## 18           No Qualification
    ## 19           No Qualification
    ## 20           No Qualification
    ## 21           No Qualification
    ## 22           No Qualification
    ## 23           No Qualification
    ## 24 Postgraduate qualification
    ## 25 Postgraduate qualification
    ## 26 Postgraduate qualification
    ## 27 Postgraduate qualification
    ## 28 Postgraduate qualification
    ## 29          Bachelor's degree
    ## 30          Bachelor's degree
    ## 31          Bachelor's degree
    ## 32          Bachelor's degree
    ## 33          Bachelor's degree
    ## 34                Certificate
    ## 35                Certificate
    ## 36                Certificate
    ## 37                Certificate
    ## 38                Certificate
    ## 39                Certificate
    ## 40                    Diploma
    ## 41                    Diploma
    ## 42                    Diploma
    ## 43                    Diploma
    ## 44                    Diploma
    ## 45                    Diploma
    ## 46           No Qualification
    ## 47           No Qualification
    ## 48           No Qualification
    ## 49           No Qualification
    ## 50           No Qualification
    ## 51           No Qualification
    ## 52 Postgraduate qualification
    ## 53 Postgraduate qualification
    ## 54 Postgraduate qualification
    ## 55 Postgraduate qualification

    ABOVE_LEFT_borders <- datacells %>% arrange(row, col)

## BELOW RIGHT border example

    cells <- tidy_xlsx(spreadsheet, "BELOW RIGHT border")$data[[1]]

    ## Warning: 'tidy_xlsx()' is deprecated.
    ## Use 'xlsx_cells()' or 'xlsx_formats()' instead.

### Headers

    # Same as BELOW RIGHT without borders
    row_headers <-
      cells %>%
      filter(col >= 10, !is_blank) %>%
      select(row, col, header = character) %>%
      split(.$col)
    row_headers

    ## $`10`
    ## # A tibble: 10 x 3
    ##      row   col header
    ##    <int> <int> <chr>
    ##  1     4    10 Bachelor's degree
    ##  2     5    10 Certificate
    ##  3     6    10 Diploma
    ##  4     7    10 No Qualification
    ##  5     8    10 Postgraduate qualification
    ##  6     9    10 Bachelor's degree
    ##  7    10    10 Certificate
    ##  8    11    10 Diploma
    ##  9    12    10 No Qualification
    ## 10    13    10 Postgraduate qualification
    ##
    ## $`11`
    ## # A tibble: 2 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1     7    11 Female
    ## 2    10    11 Male

    col_headers <-
      cells %>%
      filter(row >= 14, !is_blank) %>%
      select(row, col, header = character) %>%
      split(.$row)
    col_headers

    ## $`14`
    ## # A tibble: 6 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1    14     4 15 - 24
    ## 2    14     5 25 - 44
    ## 3    14     6 45 - 64
    ## 4    14     7 15 - 24
    ## 5    14     8 25 - 44
    ## 6    14     9 45 - 64
    ##
    ## $`15`
    ## # A tibble: 2 x 3
    ##     row   col header
    ##   <int> <int> <chr>
    ## 1    15     6 0 - 6
    ## 2    15     7 7 - 10

### Data

    # Same as BELOW RIGHT without borders
    datacells <-
      cells %>%
      filter(row <= 13, col <= 9, !is_blank) %>%
      mutate(content = ifelse(is.na(character), numeric, NA)) %>%
      mutate(value = as.integer(content)) %>%
      select(row, col, value)
    datacells

    ## # A tibble: 55 x 3
    ##      row   col  value
    ##    <int> <int>  <int>
    ##  1     4     4   7000
    ##  2     4     5  12000
    ##  3     4     6  10000
    ##  4     4     7  27000
    ##  5     4     8 137000
    ##  6     4     9  64000
    ##  7     5     4  29000
    ##  8     5     5  34000
    ##  9     5     6  30000
    ## 10     5     7 161000
    ## # ... with 45 more rows

### Borders

    # Different from BELOW RIGHT without borders
    # Find cells with borders on the bottom, and ones with borders on the left
    formatting <- tidy_xlsx(spreadsheet)$formats

    ## Warning: 'tidy_xlsx()' is deprecated.
    ## Use 'xlsx_cells()' or 'xlsx_formats()' instead.

    left_borders <- which(!is.na(formatting$local$border$left$style))
    top_borders <- which(!is.na(formatting$local$border$top$style))

    left_border_cells <-
      cells %>%
      filter(row == 15, local_format_id %in% left_borders) %>%
      select(row, col)
    top_border_cells <-
      cells %>%
      filter(col == 11, local_format_id %in% top_borders) %>%
      select(row, col)

### Join headers to data

    datacells <-
      datacells %>%
      BELOW(col_headers[[2]], left_border_cells) %>% # Different from BELOW RIGHT
      S(col_headers[[1]]) %>% # Same as BELOW RIGHT
      RIGHT(row_headers[[2]], top_border_cells) %>% # Different from BELOW RIGHT
      E(row_headers[[1]]) # Same as BELOW RIGHT

### Result

    datacells %>% as.data.frame

    ##    row col  value header.data header.header header.data.data
    ## 1    4   4   7000       0 - 6       15 - 24           Female
    ## 2    4   5  12000       0 - 6       25 - 44           Female
    ## 3    4   6  10000       0 - 6       45 - 64           Female
    ## 4    4   7  27000      7 - 10       15 - 24           Female
    ## 5    4   8 137000      7 - 10       25 - 44           Female
    ## 6    4   9  64000      7 - 10       45 - 64           Female
    ## 7    5   4  29000       0 - 6       15 - 24           Female
    ## 8    5   5  34000       0 - 6       25 - 44           Female
    ## 9    5   6  30000       0 - 6       45 - 64           Female
    ## 10   5   7 161000      7 - 10       15 - 24           Female
    ## 11   5   8 179000      7 - 10       25 - 44           Female
    ## 12   5   9 210000      7 - 10       45 - 64           Female
    ## 13   6   5  10000       0 - 6       25 - 44           Female
    ## 14   6   6   6000       0 - 6       45 - 64           Female
    ## 15   6   7  14000      7 - 10       15 - 24           Female
    ## 16   6   8  66000      7 - 10       25 - 44           Female
    ## 17   6   9  68000      7 - 10       45 - 64           Female
    ## 18   7   4  10000       0 - 6       15 - 24           Female
    ## 19   7   5  11000       0 - 6       25 - 44           Female
    ## 20   7   6  19000       0 - 6       45 - 64           Female
    ## 21   7   7  43000      7 - 10       15 - 24           Female
    ## 22   7   8  36000      7 - 10       25 - 44           Female
    ## 23   7   9  91000      7 - 10       45 - 64           Female
    ## 24   8   5   5000       0 - 6       25 - 44           Female
    ## 25   8   6   6000       0 - 6       45 - 64           Female
    ## 26   8   7   6000      7 - 10       15 - 24           Female
    ## 27   8   8  86000      7 - 10       25 - 44           Female
    ## 28   8   9  55000      7 - 10       45 - 64           Female
    ## 29   9   5   9000       0 - 6       25 - 44             Male
    ## 30   9   6   7000       0 - 6       45 - 64             Male
    ## 31   9   7  13000      7 - 10       15 - 24             Male
    ## 32   9   8  81000      7 - 10       25 - 44             Male
    ## 33   9   9  66000      7 - 10       45 - 64             Male
    ## 34  10   4  30000       0 - 6       15 - 24             Male
    ## 35  10   5  31000       0 - 6       25 - 44             Male
    ## 36  10   6  23000       0 - 6       45 - 64             Male
    ## 37  10   7 190000      7 - 10       15 - 24             Male
    ## 38  10   8 219000      7 - 10       25 - 44             Male
    ## 39  10   9 199000      7 - 10       45 - 64             Male
    ## 40  11   4   9000       0 - 6       15 - 24             Male
    ## 41  11   5   8000       0 - 6       25 - 44             Male
    ## 42  11   6   5000       0 - 6       45 - 64             Male
    ## 43  11   7  11000      7 - 10       15 - 24             Male
    ## 44  11   8  47000      7 - 10       25 - 44             Male
    ## 45  11   9  58000      7 - 10       45 - 64             Male
    ## 46  12   4  12000       0 - 6       15 - 24             Male
    ## 47  12   5  21000       0 - 6       25 - 44             Male
    ## 48  12   6  17000       0 - 6       45 - 64             Male
    ## 49  12   7  37000      7 - 10       15 - 24             Male
    ## 50  12   8  50000      7 - 10       25 - 44             Male
    ## 51  12   9  75000      7 - 10       45 - 64             Male
    ## 52  13   5   7000       0 - 6       25 - 44             Male
    ## 53  13   6   6000       0 - 6       45 - 64             Male
    ## 54  13   8  60000      7 - 10       25 - 44             Male
    ## 55  13   9  68000      7 - 10       45 - 64             Male
    ##          header.header.header
    ## 1           Bachelor's degree
    ## 2           Bachelor's degree
    ## 3           Bachelor's degree
    ## 4           Bachelor's degree
    ## 5           Bachelor's degree
    ## 6           Bachelor's degree
    ## 7                 Certificate
    ## 8                 Certificate
    ## 9                 Certificate
    ## 10                Certificate
    ## 11                Certificate
    ## 12                Certificate
    ## 13                    Diploma
    ## 14                    Diploma
    ## 15                    Diploma
    ## 16                    Diploma
    ## 17                    Diploma
    ## 18           No Qualification
    ## 19           No Qualification
    ## 20           No Qualification
    ## 21           No Qualification
    ## 22           No Qualification
    ## 23           No Qualification
    ## 24 Postgraduate qualification
    ## 25 Postgraduate qualification
    ## 26 Postgraduate qualification
    ## 27 Postgraduate qualification
    ## 28 Postgraduate qualification
    ## 29          Bachelor's degree
    ## 30          Bachelor's degree
    ## 31          Bachelor's degree
    ## 32          Bachelor's degree
    ## 33          Bachelor's degree
    ## 34                Certificate
    ## 35                Certificate
    ## 36                Certificate
    ## 37                Certificate
    ## 38                Certificate
    ## 39                Certificate
    ## 40                    Diploma
    ## 41                    Diploma
    ## 42                    Diploma
    ## 43                    Diploma
    ## 44                    Diploma
    ## 45                    Diploma
    ## 46           No Qualification
    ## 47           No Qualification
    ## 48           No Qualification
    ## 49           No Qualification
    ## 50           No Qualification
    ## 51           No Qualification
    ## 52 Postgraduate qualification
    ## 53 Postgraduate qualification
    ## 54 Postgraduate qualification
    ## 55 Postgraduate qualification

    BELOW_RIGHT_borders <- datacells %>% arrange(row, col)

## Check that all sheets are parsed correctly

    identical(ABOVE_LEFT[, -1:-2], BELOW_RIGHT[, -1:-2])

    ## [1] TRUE

    identical(ABOVE_LEFT[, -1:-2], ABOVE_LEFT_borders[, -1:-2])

    ## [1] TRUE

    identical(ABOVE_LEFT[, -1:-2], BELOW_RIGHT_borders[, -1:-2])

    ## [1] TRUE