+ Reply to Thread
Results 1 to 13 of 13

Excel 2007 : Copy column of data to new location ignoring blank cells

  1. #1
    Registered User
    Join Date
    04-29-2010
    Location
    essex england
    MS-Off Ver
    Excel 2007
    Posts
    6

    Question Copy column of data to new location ignoring blank cells

    This is my first post in these forums.
    I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
    I am sure there is a relativley simple answer but it is driving me mad!
    Hope you "Guru's" can help

    Thanks

    Added example spreadsheet to aid assistance.
    Attached Files Attached Files
    Last edited by Phydough; 04-30-2010 at 10:16 AM. Reason: Added example spreadsheet

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: Copy column of data to new location ignoring blank cells

    you can just select the data then delete the blanks
    from the home tab
    click find&select
    click go to special
    click blanks
    click ok
    on the same tab click the drop down arrow next to delete
    click delete cells
    choose the "shift cells" up option
    click ok
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    04-29-2010
    Location
    essex england
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Copy column of data to new location ignoring blank cells

    Yes that would work however it requires manual intervention, is there a formula that con automate this?

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: Copy column of data to new location ignoring blank cells

    just record a macro doing the same thing then.
    tools /macro/ record new macro
    then copy range to new location
    do the steps above then stop recording
    Last edited by martindwilson; 05-04-2010 at 06:12 AM.

  5. #5
    Registered User
    Join Date
    04-29-2010
    Location
    essex england
    MS-Off Ver
    Excel 2007
    Posts
    6

    Unhappy Re: Copy column of data to new location ignoring blank cells

    Unfortunately the macro wont work in the actual spreadsheet as the cells I am copying data from all have a formula in them so they are not seen as blank albeit they have no numeric values.
    I have attached the actual sheet for reference.
    Any further ideas?
    Attached Files Attached Files

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: Copy column of data to new location ignoring blank cells

    this is how you do it with function
    the function is array entered see my sig. "how to enter array formula"
    Last edited by martindwilson; 05-04-2010 at 03:45 PM.

  7. #7
    Registered User
    Join Date
    04-29-2010
    Location
    essex england
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Copy column of data to new location ignoring blank cells

    Quote Originally Posted by martindwilson View Post
    this is how you do it with function
    the function is array entered see my sig. "how to enter array formula"
    Sorry, was there supposed to be an attachment?
    If not I dont understand your reply.
    Please elaborate.

    Thanks for your help.

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: Copy column of data to new location ignoring blank cells

    hmm where's my attachment!
    Attached Files Attached Files
    Last edited by martindwilson; 05-07-2010 at 11:18 AM.

  9. #9
    Registered User
    Join Date
    04-29-2010
    Location
    essex england
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Copy column of data to new location ignoring blank cells

    Thanks, you really are the "guru"
    All I need to do now is figure out how to create arrays for myself!
    I shall have to study the details via your link.

    Thanks once again.

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: Copy column of data to new location ignoring blank cells

    to be honest i just adapted it from here!
    http://www.cpearson.com/EXCEL/noblanks.htm

  11. #11
    Registered User
    Join Date
    12-27-2013
    Location
    Serbia
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Excel 2007 : Copy column of data to new location ignoring blank cells

    @martindwilson thank you very much! You saved my life!

  12. #12
    Registered User
    Join Date
    03-04-2015
    Location
    Howell, NJ
    MS-Off Ver
    Microsot Office 2010
    Posts
    2

    Re: Excel 2007 : Copy column of data to new location ignoring blank cells

    I am having a similar issue but I can't seem to understand the wording in the link you provided. I have an inventory table listing equipment for different areas. I also have a Control Log for each area that is pulling the data from the Inventory Table. Each area has its own set of equipment (i.e. 1-A, 1-B, 1-C...) but certain equipment is either missing (designated by a blank cell) or is damaged (designated by an X). How can I get the Area Control Logs to list all the available equipment in a column while skipping over the blanks and the X's?

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    45,079

    Re: Excel 2007 : Copy column of data to new location ignoring blank cells

    Ricknik15 welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.

+ 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