+ Reply to Thread
Results 1 to 10 of 10

Extract data from table based on criteria applied to multiple columns

  1. #1
    Registered User
    Join Date
    10-29-2018
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    19

    Extract data from table based on criteria applied to multiple columns

    Hi everydody!

    I have been struggling with this for some days and do not know how to solve it.
    I need to create an automatic form where the next information is pulled from the the table. I habe several Project ID s but I need to bring the data for each project separate. The conditions to bring the information is that "Status" should be empty and "Reachable BU" should habe a date.

    The ideal solution would be the table below in the file.

    Thank you in advance for any help you can give me.

    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. I have updated it for you this time because you are a new member. --6StringJazzer
    Attached Files Attached Files
    Last edited by 6StringJazzer; 11-20-2018 at 12:04 PM. Reason: Moderator updated poor title

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Extract data from table based on criteria applied to multiple columns

    Take a look. All formulae are similar to this array formula:

    =IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$31=$C$33,IF($D$2:$D$31="",IF($F$2:$F$31>0,ROW($A$2:$A$31)))),ROWS($1:1))),"")

    I have assumed that C33 should read S1007 and not 1007. Please let me know more information about coding if this was not a mistake by you.

    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



  3. #3
    Registered User
    Join Date
    10-29-2018
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    19

    Re: Extract data from table based on criteria applied to multiple columns

    Thank you for your help.
    Yes, it was S1007 and not 1007. It seems to work but I forgot to say that for the column "Module" it is posible to have "MC", "EA", "CURT", "SP" and "SPU" and I need only the values for the first three modules "MC", "EA", "CURT". How can I add this?

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Extract data from table based on criteria applied to multiple columns

    Yes. Added. I changed C31, as a test, to exclude it from the list...
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-29-2018
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    19

    Re: Extract data from table based on criteria applied to multiple columns

    HI! and thanks again! The formula works but I am facind two problems now.
    1. It is possible that sometimes for the column part name, there are some options empty but with all the other columns complete. The problem is, that when I have two or more cells with part names empty and then for those optiones I have data in the column date or Reason, It just bring me the info for the first coincidence and it repeats it (I don't know if I am clear enough or should I upload a new file with the example? in case you can help me wiht this.)
    2. When I change S1007 for S1008 an so on to bring the information of the other products, after a couple of times, a sign appears and says: "Excel ran out of resources while attempting to calculate one of more formulas. As a result, these formulas cannot be evaluated. What can I do regarding this? is there another way of doing this? I have heard of makros, but I have no clue how to use them.
    I would apreciate so much if you can help me with this.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Extract data from table based on criteria applied to multiple columns

    Upload a sample sheet.

  7. #7
    Registered User
    Join Date
    10-29-2018
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    19

    Re: Extract data from table based on criteria applied to multiple columns

    The table below to the left would be the correct solution. The table at the right it is the solution I get.
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Extract data from table based on criteria applied to multiple columns

    You have removed the formulae. Please repost with them in place.

  9. #9
    Registered User
    Join Date
    10-29-2018
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    19

    Re: Extract data from table based on criteria applied to multiple columns

    Here the file with formulas
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Extract data from table based on criteria applied to multiple columns

    I got it (eventually) on the earlier sheet. It took me ages to spot that you had added an extra column... that caused confusion!!
    Attached Files Attached Files

+ 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] VLOOKUP OR HLOOKUP Help
    By jrtaylor in forum Excel General
    Replies: 4
    Last Post: 07-14-2012, 03:46 PM
  2. Vlookup or Hlookup
    By julhs in forum Excel General
    Replies: 4
    Last Post: 01-24-2012, 08:25 PM
  3. VLookup/HLookup or Both.. Even Possible?
    By mardon in forum Excel General
    Replies: 10
    Last Post: 05-18-2011, 11:53 AM
  4. Excel 2007 : Help with vlookup/hlookup
    By dezmond in forum Excel General
    Replies: 1
    Last Post: 08-15-2009, 06:54 AM
  5. hlookup and or vlookup
    By mkmed in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-31-2009, 01:02 AM
  6. [SOLVED] vlookup and hlookup
    By hotelmasters in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-15-2006, 03:45 PM
  7. How do I use Vlookup or Hlookup?
    By exsam21 in forum Excel General
    Replies: 2
    Last Post: 09-23-2005, 03:05 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