+ Reply to Thread
Results 1 to 9 of 9

Completing partially-filled data

  1. #1
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Completing partially-filled data

    I came across this tip years ago and remember being amazed by it at the time (although now it's just part of my repertoire).

    Often you come across data where there is just one entry in a column (e.g. for a date) and the empty cells which follow are assumed to have the same value as the last filled cell above. This may occur for several columns, as the attached file demonstrates, with a second column used for Department. This may look good on a printed report, but it's not very useful if you want to analyse the data using formulae like SUMIF or SUMPRODUCT, or if you want to set up a pivot table on the data. An easy way of filling those empty cells so that we have contiguous data is as follows:

    starting with the cursor somewhere in the table of data,
    press F5 (GoTo), then click Special, then click Current Region;
    press F5 again, then click Special, then click Blanks;
    begin to enter a formula by typing "=" (without the quotes)
    click on the cell immediately above the activecell (coloured white in the highlighted block);
    then hold down the CTRL key and press Enter.

    This will have filled that formula into all the cells that were highlighted - amazing !!

    Hope this helps.

    Pete

    EDIT: As Marvin has pointed out below, if you intend to do anything with the data that involves moving the rows around (like sorting), you should fix the values using [copy], [paste special | values | OK], then press the [Esc] key.
    Attached Files Attached Files
    Last edited by Pete_UK; 07-28-2012 at 04:26 PM.

  2. #2
    Forum Contributor bonny24tycoon's Avatar
    Join Date
    04-02-2012
    Location
    Hell
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    405

    Re: Completing partially-filled data

    @Pete_UK - This was amazing to learn. Great knowledge and good work to share. Thank You!!!
    Thanks,

    Bonny Tycoon


  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Completing partially-filled data

    This is one of my favorites too. I think I learned it from DaddyLongLegs. Nice one Pete.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Completing partially-filled data

    That was really cool!
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  5. #5
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2003
    Posts
    125

    Re: Completing partially-filled data

    Very useful! Many thanks!
    B.Econ, CFA

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Completing partially-filled data

    Hi Pete
    I liked this hint but found a problem. After doing what you suggest, I then sorted your sample table by Quantity from small to large. I found there were then 4 Paintshop when their had been only 2 before sorting.

    My assumption was that after doing your table, filling in the blanks with a formula of what was above them, might keep the row data together. It DOES NOT!

    A simple additional step might be to copy and Paste VALUES ONLY over the columns A and B (in your example) that would keep the row data together.

    I still liked the trick but found it fooled me into thinking the data was a table afterwards. Beware!!
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Completing partially-filled data

    Quote Originally Posted by MarvinP View Post
    A simple additional step might be to copy and Paste VALUES ONLY over the columns A and B (in your example) that would keep the row data together.
    I agree, Marvin - if you are going to move the rows by sorting then you should fix the values first. I've amended my first post to this effect.

    Incidentally, I found where I first came across the technique. It was in one of the Step-by-Step books from MS - Excel 97 Visual Basic, by Reed Jacobson (I said it was a long time ago !!), so thanks to him for publishing it.

    Pete

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Completing partially-filled data

    or
    Please Login or Register  to view this content.



  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Completing partially-filled data

    ....or, keeping with the spirit of the formula presented, a macro to apply this posted technique for you:

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1