Viewing 5 posts - 1 through 5 (of 5 total)
  • Excel pivot table help
  • eckinspain
    Free Member

    Let’s see if I can explain the problem.
    I have a table of information that I want to convert into a pivot table. It’s in the right format except that columns A, B and C don’t have information in all the cells – only in the first line of each group. eg Line 1 has info in columns A, B, C and D but then lines 2-13 only have the data in column D. Obviously I can just copy the data down in the first 3 columns but when there’s hundreds of different groups in different worksheets this gets a bit boring. How can I copy the data in columns A, B and C in line 1 into lines 2-13?

    scaredypants
    Full Member

    Let’s see if I can explain the problem.

    not sure about that !

    could you just sort col a then b then c ??

    monkfish
    Free Member

    this kind of thing ? Highlight a column at a time and that fills the blanks in a column with row above value

    Sub Fill_Empty()

    Dim oRng As Range
    Set oRng = Selection
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Font.Bold = False
    Selection.FormulaR1C1 = “=R[-1]C”
    oRng.Copy
    oRng.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    End Sub

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    StirlingCrispin
    Full Member

    Or – insert extra columns and fill with
    =IF(E2= “”,A1,E2) etc

    IF E2 is empty then give the cell the value of the cell above.

    Copy and paste as value.

    scaredypants
    Full Member

    oh, hang on – you mean you have lots of sheets, each with 13 rows and 4 cols but cols a-c only have row 1 filled, while d has all 13

    what’s in col d – do you need the contents ? If you do, is it OK for the contents of a-c to alter next to it. Seems an odd ptable to me

    what are your headings/labels ?

Viewing 5 posts - 1 through 5 (of 5 total)

The topic ‘Excel pivot table help’ is closed to new replies.