title: collapse a number of column IDs into a single ID column
body: <p>I am trying to merge some data. I have some data which contains IDs. I want to merge each of the columns into a new column <code>finalID</code> which collapses all the other columns down into a single column.</p>
  
  <p>For many of the column IDs they will be the same however one or two columns might be different. If they are different then I would like it such that it adds the second ID into a new column <code>finalID_WithDoubleResults</code> for example.</p>
  
  <p>How can I first, collapse the columns into a single ID column
  Then, if the columns contain different IDs Split them into two or more newer columns.</p>
  
  <p>Data:</p>
  
  <pre><code>structure(list(sabiUniqueID.x = c(74L, 82L, 129L, 153L, 179L, 
  203L, 211L, 220L, 223L, 231L, 237L, 243L, 259L, 260L, 275L, 280L, 
  290L, 292L, 297L, 300L), sabiUniqueID.y = c(74L, 82L, 129L, NA, 
  NA, NA, 211L, NA, 223L, 231L, 237L, NA, NA, NA, NA, NA, 290L, 
  NA, 297L, NA), sabiUniqueID.x.x = c(74L, 82L, 129L, 153L, NA, 
  NA, 211L, NA, 223L, 231L, 237L, NA, NA, 260L, 275L, NA, 290L, 
  292L, 297L, NA), sabiUniqueID.y.y = c(NA, NA, 129L, NA, 179L, 
  203L, 211L, NA, 223L, NA, NA, NA, 259L, 260L, 275L, NA, 290L, 
  292L, NA, NA), sabiUniqueID.x.x.x = c(NA, NA, NA, NA, NA, NA, 
  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 292L, NA, NA), sabiUniqueID.y.y.y = c(NA, 
  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 260L, 275L, NA, 
  290L, NA, NA, NA), sabiUniqueID.x.x.x.x = c(74L, 82L, NA, NA, 
  NA, NA, NA, NA, NA, NA, NA, 243L, NA, NA, NA, NA, NA, 292L, NA, 
  NA), sabiUniqueID.y.y.y.y = c(74L, 82L, NA, NA, NA, NA, NA, NA, 
  NA, NA, NA, NA, NA, 260L, 275L, NA, 290L, NA, NA, NA), sabiUniqueID.x.x.x.x.x = c(NA, 
  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 290L, 
  NA, NA, 300L), sabiUniqueID.y.y.y.y.y = c(74L, 82L, 129L, NA, 
  179L, 203L, NA, NA, 223L, NA, NA, 243L, NA, 260L, NA, NA, 290L, 
  292L, NA, 300L), sabiUniqueID.x.x.x.x.x.x = c(74L, 82L, 129L, 
  153L, 179L, 203L, 211L, 220L, 223L, 231L, 237L, 243L, 259L, 260L, 
  275L, 280L, 290L, 292L, 297L, 300L), sabiUniqueID.y.y.y.y.y.y = c(NA, 
  82L, NA, NA, 179L, 203L, NA, NA, NA, NA, NA, 243L, 259L, 260L, 
  NA, 280L, 290L, 292L, NA, 300L), sabiUniqueID = c(NA, 82L, NA, 
  NA, 179L, 203L, NA, NA, NA, NA, NA, 243L, 259L, 260L, NA, 280L, 
  290L, 292L, NA, 300L)), row.names = c(NA, -20L), groups = structure(list(
      sabiUniqueID.x = c(74L, 82L, 129L, 153L, 179L, 203L, 211L, 
      220L, 223L, 231L, 237L, 243L, 259L, 260L, 275L, 280L, 290L, 
      292L, 297L, 300L), sabiUniqueID.y = c(74L, 82L, 129L, NA, 
      NA, NA, 211L, NA, 223L, 231L, 237L, NA, NA, NA, NA, NA, 290L, 
      NA, 297L, NA), sabiUniqueID.x.x = c(74L, 82L, 129L, 153L, 
      NA, NA, 211L, NA, 223L, 231L, 237L, NA, NA, 260L, 275L, NA, 
      290L, 292L, 297L, NA), sabiUniqueID.y.y = c(NA, NA, 129L, 
      NA, 179L, 203L, 211L, NA, 223L, NA, NA, NA, 259L, 260L, 275L, 
      NA, 290L, 292L, NA, NA), sabiUniqueID.x.x.x = c(NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 292L, 
      NA, NA), sabiUniqueID.y.y.y = c(NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, 260L, 275L, NA, 290L, NA, NA, NA), 
      sabiUniqueID.x.x.x.x = c(74L, 82L, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, 243L, NA, NA, NA, NA, NA, 292L, NA, NA), sabiUniqueID.y.y.y.y = c(74L, 
      82L, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 260L, 275L, 
      NA, 290L, NA, NA, NA), sabiUniqueID.x.x.x.x.x = c(NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 290L, 
      NA, NA, 300L), sabiUniqueID.y.y.y.y.y = c(74L, 82L, 129L, 
      NA, 179L, 203L, NA, NA, 223L, NA, NA, 243L, NA, 260L, NA, 
      NA, 290L, 292L, NA, 300L), sabiUniqueID.x.x.x.x.x.x = c(74L, 
      82L, 129L, 153L, 179L, 203L, 211L, 220L, 223L, 231L, 237L, 
      243L, 259L, 260L, 275L, 280L, 290L, 292L, 297L, 300L), sabiUniqueID.y.y.y.y.y.y = c(NA, 
      82L, NA, NA, 179L, 203L, NA, NA, NA, NA, NA, 243L, 259L, 
      260L, NA, 280L, 290L, 292L, NA, 300L), .rows = structure(list(
          1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 
          14L, 15L, 16L, 17L, 18L, 19L, 20L), ptype = integer(0), class = c("vctrs_list_of", 
      "vctrs_vctr"))), row.names = c(NA, 20L), class = c("tbl_df", 
  "tbl", "data.frame"), .drop = TRUE), class = c("grouped_df", 
  "tbl_df", "tbl", "data.frame"))
  </code></pre>
  
  <p>EDIT:</p>
  
  <p>Smaller data</p>
  
  <pre><code>structure(list(sabiUniqueID.x = c(NA, NA, NA, NA, 820L), sabiUniqueID.y = c(1301L, 
                                                                              NA, NA, NA, 820L), sabiUniqueID.x.x = c(9999L, NA, 1176L, NA, 
                                                                                                                      820L), sabiUniqueID.y.y = c(NA, NA, NA, 1537L, 820L), sabiUniqueID.x.x.x = c(NA, 
                                                                                                                                                                                                   NA, NA, NA, 820L)), row.names = c(NA, -5L), class = c("tbl_df", 
                                                                                                                                                                                                                                                         "tbl", "data.frame"))
  </code></pre>
  
  <p>Which looks like:</p>
  
  <pre><code>    sabiUniqueID.x sabiUniqueID.y sabiUniqueID.x.x sabiUniqueID.y.y sabiUniqueID.x.x.x
             &lt;int&gt;          &lt;int&gt;            &lt;int&gt;            &lt;int&gt;              &lt;int&gt;
  1             NA           1301             9999               NA                 NA
  2             NA             NA               NA               NA                 NA
  3             NA             NA             1176               NA                 NA
  4             NA             NA               NA             1537                 NA
  5            820            820              820              820                820
  </code></pre>
  
  <p>Where now I have added the result <code>9999</code> and it has two IDs for this observation (the other observations have duplicated IDs).</p>
  
  <p>So the expected output might look like:</p>
  
  <pre><code>finalID  finalID_WithDoubleResults
  1301         99999
  NA            NA
  1176          NA
  1537          NA
  820           NA
  </code></pre>
  
  <p>EDIT2:</p>
  
  <pre><code>df %&gt;% 
    pivot_longer(cols = c(sabiUniqueID.x:sabiUniqueID.x.x.x), names_to = "id_type", values_to = "id_
  
  value") %&gt;% 
    group_by(id_type) %&gt;% 
    arrange(desc(id_value)) %&gt;% 
    distinct() %&gt;% 
    #select(-c(id_type)) %&gt;% 
    mutate(rowID = row_number()) %&gt;% 
    pivot_wider(names_from = id_type, values_from = id_value)
  </code></pre>