Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all:

    I've seen this before here some time ago, but for the life of me I can't find it through search. I have several thousand rows of the type:

    NAME DEPT AREA OPTION
    Fred // Dept A // Cardiff // Choice 1
    Blank cells - - - - - - - - - - Choice 2
    Blank cells - - - - - - - - - - Choice 3
    Jim // Dept B // Swansea // Choice 1
    Blank cells - - - - - - - - - - Choice 2
    Blank cells - - - - - - - - - - Choice 3
    Blank cells - - - - - - - - - - Choice 4
    Mary // Dept B // Cardiff // Choice 1
    Blank cells - - - - - - - - - - Choice 2

    What is the trick to quickly convert it to the form:

    NAME DEPT AREA OPTION
    Fred // Dept A // Cardiff // Choice 1
    Fred // Dept A // Cardiff // Choice 2
    Fred // Dept A // Cardiff // Choice 3
    Jim // Dept B // Swansea // Choice 1
    Jim // Dept B // Swansea // Choice 2
    Jim // Dept B // Swansea // Choice 3
    Jim // Dept B // Swansea // Choice 4
    Mary // Dept B // Cardiff // Choice 1
    Mary // Dept B // Cardiff // Choice 2

    I can't remember whether the technique involved VBA or was done with formulae?

    Many thanks for any help.

    Tony
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Select the data range:
    Edit - Goto - [Special]
    Select "Blanks"
    [OK]

    Now the blank cells will be selected. In the active cell, enter an equal sign(=) and then select the cell above it, putting in a formula. Instead of [Enter] to confirm it, use Ctrl-Enter and this will put the formula in all the cells, filling all the blanks with a reference to the cell above it.

    Select all the cells
    edit - copy
    edit - pastespecial - values
    to convert the formulas to values...

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='793084' date='12-Sep-2009 14:00']Select the data range:
    Edit - Goto - [Special]
    Select "Blanks"
    [OK]

    Now the blank cells will be selected. In the active cell, enter an equal sign(=) and then select the cell above it, putting in a formula. Instead of [Enter] to confirm it, use Ctrl-Enter and this will put the formula in all the cells, filling all the blanks with a reference to the cell above it.

    Select all the cells
    edit - copy
    edit - pastespecial - values
    to convert the formulas to values...

    Steve[/quote]
    Thanks Steve - this is just what I remembered, but couldn't find!

    Of course, being 2007, the Goto command is in the Find & Select group of the Editing Tab - but I used F5 anyway.

    Many thanks for your prompt help.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •