+ Reply to Thread
Results 1 to 4 of 4

How to eliminate Space / height in the Formula

  1. #1
    Forum Contributor
    Join Date
    02-25-2012
    Location
    Coimbatore, India
    MS-Off Ver
    Excel 2010
    Posts
    445

    How to eliminate Space / height in the Formula

    Hello Friends

    Version : MS Excel 2010
    Attached File Name : How to eliminate the Space.xlsx

    My requirement is I want to plot the number of entries which comes ±5 with the Condition (Cell A3) in Quantity (in Cell C13) and the name of the entry )in cell D13).
    I myself did a formula and got the Quanity for the entries in Cell C13 and the names in Cell D13.

    But the problem is I used IF and REPT formulas (&REPT(CHAR(10),1)) when the condition ±5 is not met with entries then the REPT formula will move the pointer to next line.
    So the height is huge since condition is not matched means it is `blank' and REPT move the formula to next entry.

    Now my requirement is I want to eliminate the Height / Blank Space in Cell D13. It has to shows just A4 without Space / Height.

    thanks in advance

    thilag
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to eliminate Space / height in the Formula

    Does this array formula helps......formula needs to be confirmed with Ctrl+Shift+Enter
    Please Login or Register  to view this content.
    Is this what you want?
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Contributor
    Join Date
    02-25-2012
    Location
    Coimbatore, India
    MS-Off Ver
    Excel 2010
    Posts
    445

    Re: How to eliminate Space / height in the Formula

    Hello sktneer

    thanks for your reply.

    If I changing the values to close ±5 with B3 111.45 i.e., with ±5 with conditon value then still this formula shows `A4' only.

    Example : if I change the value B5 = 110 then in result A5 also to be included with A4, in my formula it shows A4 and A5 but your formula shows A4 only, it is not adding A5.

    My intention is if we change the value and it is within ±5 with cell B3 (111.45) then accordingly formula should change their outputs with eliminated space. My formula is working fine but the verical height / space is the problem.


    thanks again

    thilag

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to eliminate Space / height in the Formula

    One way is to place the following array formula in D13 and drag down until you get blank rows. This will list all the entries inside the condition specified. Remember to confirm the formula with Ctrl+Shift+Enter

    Please Login or Register  to view this content.
    Does that help?

+ 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. Trying to eliminate space between cells
    By Defenestrator in forum Excel General
    Replies: 4
    Last Post: 10-08-2013, 04:02 AM
  2. Replies: 17
    Last Post: 02-25-2009, 10:09 AM
  3. Replies: 2
    Last Post: 08-15-2006, 12:55 PM
  4. Macro to eliminate extra space
    By kleivakat in forum Excel General
    Replies: 3
    Last Post: 01-31-2006, 08:25 AM
  5. [SOLVED] Too much white space to the right of a pie chart! How to eliminate
    By wstu in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-02-2005, 04:01 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