6 Replies Latest reply: Jan 9, 2012 9:42 AM by Anne Duffy RSS

    Excel load unmerge cells with new value

      IDs Merge.bmp

      Hi Guys


      I am loading over 600 excel sheets into QV ( Its a data sorting project)


      The issue is one of the columns Q ,[ Review Date],is merged ( randomly 3,2,7,8 lines)

      See sample above


      I am using either a Macro or Formula to unmerge and bring the correct values into the column,


      This is the macro


      Sub UnMergeA()

      With Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row)

      .MergeCells = False

      With .SpecialCells(xlCellTypeBlanks)

      .Value = .Value

      .FormulaR1C1 = "=R[-1]C"

      End With

      End With





      End Sub

      It unmerges the cell and if the cell is blank looks to the cells above and takes the value of that cell and populates it in, if it is not blnak it retains value.

      See Result below

      Ids Unmerge.bmp

      I dont relish doing this for 600 sheets before the load, is it possible to write this as part of the load code in QV?



        • Excel load unmerge cells with new value
          Stefan Wühl

          Should be possible within the load filter, try something like



          LOAD Field1,




          (ooxml, embedded labels, table is Table1, filters(

          Replace(2, top, StrCnd(null))



          You could easily create the correct filter with the assistant, enable transformation step, check out FILL filter option tab.


          Hope this helps,