+ Reply to Thread
Results 1 to 5 of 5

BLANKS (Not Really Blanks) AND Rearrange ignoring blanks

  1. #1
    Forum Contributor
    Join Date
    02-21-2014
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    100

    BLANKS (Not Really Blanks) AND Rearrange ignoring blanks

    Hi Gurus, I have below queries in the sheet I have attached

    1. The cells were pasted with Special Paste BY VALUES which were calculated by the formula and had no values returned. They appear blank and are not really blank.
    They show counts when only blank cells are selected.
    I want to clear all the contents of these BLANK LOOKING cells in the array to enable correct counting of the blanks and the ones with the values. I HAVE FOUND A FIX FOR THIS WITH CTRL+F and Selecting "match Entire Cell Contents" does a fantastic job.


    2. In the Sheet, I have Vendors in the First Column and have many Entries with Values as determined by formula and blanks cells in between. Can I get these Row
    Entries serially by ignoring blanks? (As shown in the "Required" Sheet

    Thanks in advance

    Shiva
    Attached Files Attached Files
    Last edited by shivspatil; 02-01-2018 at 09:09 AM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: BLANKS (Not Really Blanks) AND Rearrange ignoring blanks

    1. "The cells...they appear blank"

    WHAT CELLS ARE YOU TALKING ABOUT?
    NAME A CELL REFERENCE THAT APPEARS BLANK BUT IS NOT.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: BLANKS (Not Really Blanks) AND Rearrange ignoring blanks

    try this aray formula, in B2, copied across and down:

    =IFERROR(INDEX(PI!2:2,SMALL(IF(PI!$B2:$DX2<>"",COLUMN(PI!$B2:$DX2)),COLUMNS($B:B))),"")

    Next time, can you post a SAMPLE sheet with 10-20 rows/columns and not your ENTIRE sheet!!!

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Glenn



  4. #4
    Forum Contributor
    Join Date
    02-21-2014
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    100

    Re: BLANKS (Not Really Blanks) AND Rearrange ignoring blanks

    The cells in the sheet PI. Select any cells and check the counts.
    In future I will attach the sample sheet Glen and thanks for your help.

    Repute added

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: BLANKS (Not Really Blanks) AND Rearrange ignoring blanks

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Access Query shows (Blanks) but no blanks in data!
    By MissDB in forum Access Tables & Databases
    Replies: 0
    Last Post: 09-08-2016, 07:16 AM
  2. VBA code finds blanks, but how to write VBA to ignore non blanks?
    By hopegriffin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2016, 10:51 AM
  3. Replies: 8
    Last Post: 06-15-2016, 09:53 AM
  4. [SOLVED] Data Cleanup - ignoring blanks, ignoring rows with string values etc.
    By Huyaku in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2015, 08:17 AM
  5. [SOLVED] How to convert a horizontal vector with blanks into a vertical one without blanks
    By luv2glyd in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-27-2014, 02:55 PM
  6. How NOT to Sort Blanks that aren't Blanks
    By Tirren in forum Excel General
    Replies: 1
    Last Post: 08-17-2009, 01:45 PM
  7. [SOLVED] Paste Special Skip Blanks not skipping blanks, but overwriting...
    By gsrosin in forum Excel General
    Replies: 0
    Last Post: 02-22-2005, 12:06 AM

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