Excel - Working with Merged Cells Dynamically

0
votes

Good afternoon, hope you're all doing well.

Our Data Warehouse is exporting the data in kind of a weird format, using merged cells, which we have to clean weekly so we can work with it.

Sample Data

In the screenshot you can find the setup, which relies on merged cells, instead of copying the partner, dates, brand & zone code.

I've created a formula that specifies that if the row above is blank, then, it finds the next cell further on top which has text and copies it, but it's a little unreliable, it goes like this:

=IF([@Date]="",B23,[@Date])

=IF([@Date]="",B24,[@Date])

Since B23 is not blank, then it assumes its value. Then, the next one, since B24 is blank, then it copies B23. Then, since B25 is blank, it copies B24, which already has the date value.

It works, but it seems like there must be a better way to achieve this dynamically, for the dates and the other merged columns, which have the same pattern with the merged cells. I have explored some Power Query solutions, but yielding no results. Note that when importing this data with Power Query, the merged cells simply come as "null".

2
excelexcel-formulapowerquery
If you haven't already, I would explore all the options for the data warehouse export to make sure there isn't a "data only" option. This would be the most efficient path. Most exports have a ".csv" or other data only option. Otherwise I don't know of a better way for filling in the data unless you want to set something up in VBA. - K753
Unfortunately, we have no such option to export this kind of data, at this point. I can work with VBA, but only at a very basic level. I'm able to understand the code, but writing it takes me quite a long time, at this point. So I'm not sure I would want to go through that path, do you have some specific example source for the code I could use? - John ED
No, I don't have any examples for this scenario and it would take me a while to put something together. Hopefully someone else has a solution. - K753

2 Answers

0
votes

Assuming you have your data in an Excel structured table (which seems like because the formula you posted) look in power query for the command transform and fill down.

I currently only have installed the spanish version, but you can look for the commands in English.

enter image description here

Here is a tutorial I found: https://www.myexcelonline.com/blog/fill-values-using-power-query/

0
votes

A basic VBA approach:

Sub UnMerge()

    Dim c As Range, m As Range, v

    Application.ScreenUpdating = False
    For Each c In ActiveSheet.UsedRange.Cells
        If c.MergeCells Then
            v = c.Value
            Set m = c.MergeArea
            c.UnMerge
            m.Value = v
        End If
    Next c
    Application.ScreenUpdating = True

End Sub