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?

      Thanks

      Anne

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

          Should be possible within the load filter, try something like

           

          Directory;

          LOAD Field1,

               Field2

          FROM

          Map1.xlsx

          (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,

          Stefan