+ Reply to Thread
Results 1 to 10 of 10

Eliminate blank rows when pulling criteria based data from one spreadsheet to another

  1. #1
    Registered User
    Join Date
    04-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Eliminate blank rows when pulling criteria based data from one spreadsheet to another

    I am trying to pull 3 cells of data (out of an entire row of data)based on one criteria (>0) from one spreadsheet onto another spreadsheet. I have used a basic IF formula but that results in blank lines when the critera is false. How do I write the formula so when the criteria is true the 3 cells fill in on consecutive rows? Thanks

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Eliminate blank rows when pulling criteria based data from one spreadsheet to another

    See if you can adapt the technique used here:

    Lookup with multiple instances of the lookup value

    If not, then you'll have to post a SMALL sample file so we can see what you're trying to do.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

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

    Re: Eliminate blank rows when pulling criteria based data from one spreadsheet to another

    Share the formula you are using. Better if you upload a sample workbook with enough sample data in it with the formula applied and show the desired output.
    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.

  4. #4
    Registered User
    Join Date
    04-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Eliminate blank rows when pulling criteria based data from one spreadsheet to another

    Here is the formula I'm using. My problem is the blank lines that result if the statement is false. I am telling it to return "" if the statement is false but that results in a blank line. I want the data that is true to enter on consecutive lines not leave any blank lines. Can you help?
    =IF('Cash & Bank'!F3>0,'Cash & Bank'!A3,"")

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

    Re: Eliminate blank rows when pulling criteria based data from one spreadsheet to another

    Try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. normally when you write a formula in a cell, you confirm it by pressing Enter key, but in case of an array formula, you will need to confirm it by holding the Ctrl+Shift keys together and then pressing Enter key. Or if you paste an array formula in a cell, press F2 (function key) and hold down the Ctrl+Shift together and then press Enter. When an array formula is entered correctly, you will notice in the formula bar that the formula gets surrounded by the Curly Brackets that tells you that you have confirmed the array formula correctly.)
    Please Login or Register  to view this content.
    and then dopy down until you get blank cells.

    Is this what you are trying to achieve?

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Eliminate blank rows when pulling criteria based data from one spreadsheet to another

    Quote Originally Posted by Terisammis View Post
    =IF('Cash & Bank'!F3>0,'Cash & Bank'!A3,"")
    Try this...

    Cash & Bank
    A
    B
    C
    D
    E
    F
    2
    -----
    -----
    -----
    -----
    -----
    -----
    3
    Data1
    42
    4
    Data2
    0
    5
    Data3
    0
    6
    Data4
    81
    7
    Data5
    98
    8
    Data6
    -10
    9
    Data7
    52
    10
    Data8
    0
    11
    Data9
    26
    12
    Data10
    0
    13
    Data11
    29
    14
    Data12
    0
    15
    Data13
    5
    16


    On Sheet2...

    Sheet2
    A
    1
    2
    Data1
    3
    Data4
    4
    Data5
    5
    Data7
    6
    Data9
    7
    Data11
    8
    Data13
    9


    This array formula** entered in A2:

    =IFERROR(INDEX('Cash & Bank'!A:A,SMALL(IF('Cash & Bank'!F$3:F$15>0,ROW('Cash & Bank'!F$3:F$15)),ROWS(A$2:A2))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.

  7. #7
    Registered User
    Join Date
    04-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Eliminate blank rows when pulling criteria based data from one spreadsheet to another

    It worked!!! Thanks so much. Now I have that part under control my next hurdle is a couple cells away - I am using the same formula but am trying to add a second criteria so I tucked in an AND to have it look at a second cell as well. Unfortunately it is not working so I'm stuck again. Cell K3 is the first cell where I'm using this formula, I tried changing the last statement ROWS(K$1:K1))),"") to say ROWS(K$3:K3))),"") but that didn't seem to help. Any suggestions?

    =IFERROR(INDEX('Cash & Bank'!$G$3:$G$40,SMALL(IF(AND('Cash & Bank'!$G$3:$G$40>0,'Cash & Bank'!$B$3:$B$40="ge"),ROW('Cash & Bank'!$G$3:$G$40)-ROW('Cash & Bank'!$G$3)+1),ROWS(K$1:K1))),"")

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Eliminate blank rows when pulling criteria based data from one spreadsheet to another

    Try it like this...

    =IFERROR(INDEX('Cash & Bank'!G:G,SMALL(IF('Cash & Bank'!G$3:G$40>0,IF('Cash & Bank'!B$3:B$40="ge",ROW('Cash & Bank'!G$3:G$40))),ROWS(K$3:K3))),"")

    Still array entered.

  9. #9
    Registered User
    Join Date
    04-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Eliminate blank rows when pulling criteria based data from one spreadsheet to another

    Thanks so much, I am good to go now!!!

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Eliminate blank rows when pulling criteria based data from one spreadsheet to another

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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. Pulling rows of data based on criteria in one column
    By Ezekedes in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-19-2013, 10:54 AM
  2. Pulling data from one spreadsheet to another on a set criteria
    By michaelcouch87 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-09-2013, 04:50 AM
  3. Pulling data from one worksheet into another based on several criteria
    By kguenzel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2012, 12:37 PM
  4. Pulling data lines based on criteria - VBA
    By jamesh777 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-18-2012, 05:06 AM
  5. Replies: 1
    Last Post: 01-05-2009, 01:08 PM

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