Is there a way in R to unmerge spreadsheet cells and fill with values other than duplicates?

0
votes

I would like to import an Excel spreadsheet, which contains merged cells, unmerge those cells and fill each unmerged cell with the original cell's value divided by the number of unmerged cells. For example, if a merged cell, comprising 4 cells, contains the value 1000 I would like to unmerge and return 250 in each unmerged cell.

I am aware of functions like read.xlsx() where I can set fillMergedCells = FALSE; however, this fills each unmerged cell with a duplicate of the merged cell's original value.

Can this be done in R, perhaps somehow using the read.xlsx() function? Can it be done in Excel VBA? I have no knowledge of VBA so any pointers would be helpful!

2
rexcelvba
Is there data in the merged cells or are they blank? If not blank, do you have an example of the data? – Tech Commodities
@TechCommodities 1 of the cells in the merged cells contains the value I want to spread/distribute equally over the other merged cells. I have created a (very simplified) set of dummy data to illustrate here: docs.google.com/spreadsheets/d/… The sheet Merged contains before, the sheet Desired result after unmerge is what I would like to achieve. – geoffest
I'm not able to access that document. – Martin Gal

2 Answers

0
votes

Suppose your xlsx-File looks something like this:

Name
100
200
*
*
300

with * being the merged cell containing 200, so 200 spans from row 2 to row 4.

Using openxlsx we can read this file into a dataframe:

df1 <- openxlsx::read.xlsx("example.xlsx", fillMergedCells = TRUE)

#>   Name
#> 1  100
#> 2  200
#> 3  200
#> 4  200
#> 5  300

Using another package readxl we get

df2 <- readxl::read_xlsx("example.xlsx")

#> # A tibble: 5 x 1
#>    Name
#>   <dbl>
#> 1   100
#> 2   200
#> 3    NA
#> 4    NA
#> 5   300

Now we combine those two dataframes to calculate the new unmerged cell:

library(dplyr)

df1 %>% 
  rename(Name_1 = Name) %>%
  bind_cols(df2) %>%
  group_by(check = cumsum(is.na(Name_1) | !is.na(Name))) %>% 
  mutate(new_col = ifelse(any(is.na(Name)), first(Name)/n(), Name)) %>% 
  ungroup() %>% 
  select(Name = new_col)

which returns

# A tibble: 5 x 1
   Name
  <dbl>
1 100  
2  66.7
3  66.7
4  66.7
5 300

I'm fully aware that this approach isn't a very efficient solution but it's hard to determine the merged/unmerged cells, especially if there are neighbouring cells containing the same value.

0
votes

You can do it with VBA. Put this code in a regular module in your workbook and run the first sub:

Sub UnMergeWorkbook()

    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
    
        Dim Cell As Range
        For Each Cell In WS.UsedRange
        
            If TypeName(Cell.Value2) = "Double" Then
        
                UnMergeCell Cell
            
            End If
        
        Next Cell
        
    Next WS
    
End Sub

Sub UnMergeCell(Cell As Range)

    Dim RowsCount As Long
    Dim ColsCount As Long
    
    With Cell.MergeArea
        RowsCount = .Rows.Count
        ColsCount = .Columns.Count
    End With
    
    With Cell
        .UnMerge
        .Resize(RowsCount, ColsCount).Value2 = .Value2 / (RowsCount * ColsCount)
    End With
    
End Sub