+ Reply to Thread
Results 1 to 11 of 11

Extract multiple data from multiple column with one criteria

  1. #1
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,078

    Extract multiple data from multiple column with one criteria

    hi,
    extract data from sheet(all) to sheet(invoice) based on criteria sheet(invoice").range(c18) according to sheet(all).range(a2:a1000)

    sample uploaded
    Attached Files Attached Files
    Use Code-Tags for showing your code :
    Please mark your question Solved if there has been offered a solution that works fine for you
    If You like solutions provided by anyone, feel free to add reputation using STAR *

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Extract multiple data from multiple column with one criteria

    In B26

    =IFERROR(INDEX(ALL!$B$5:$B$1000,SMALL(IF(ALL!$A$5:$A$1000=$C$18,ROW(ALL!$B$5:$B$1000)-ROW($A$5)+1,""),ROWS($A$5:A5))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Copy down

    You should now be able to complete all the other columns.

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Extract multiple data from multiple column with one criteria

    =IFERROR(AGGREGATE(15,6,ROW(ALL!$A$5:$A$8)/(ALL!$A$5:$A$8=$C$18),A26)-4,"") s and INDEX s
    Attached Files Attached Files

  4. #4
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,078

    Re: Extract multiple data from multiple column with one criteria

    Thank you Jhon
    just few questions
    1 = why you use from row 5 (ALL!$B$5:$B$1000) while i need to start from row 2 = ALL!$B$2:$B$1000,
    2 if i change range to ALL!$B$2:$B$1000 then what i change in this part -ROW($A$5)+1,""),ROWS($A$5:A5))) and what is the function of this part

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Extract multiple data from multiple column with one criteria

    should be

    =IFERROR(INDEX(ALL!$B$3:$B$1000,SMALL(IF(ALL!$A$3:$A$1000=$C$18,ROW(ALL!$B$3:$B$1000)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")

    my error!!!!

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Extract multiple data from multiple column with one criteria

    ... I noticed Tim did the same (your posted file was positioned at row 5 so we both took that as the start!)

  7. #7
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,078

    Re: Extract multiple data from multiple column with one criteria

    thank you john
    its working perfectly

    and tim you too

  8. #8
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,078

    Re: Extract multiple data from multiple column with one criteria

    Quote Originally Posted by tim201110 View Post
    =IFERROR(AGGREGATE(15,6,ROW(ALL!$A$5:$A$8)/(ALL!$A$5:$A$8=$C$18),A26)-4,"") s and INDEX s
    this is not working if i change
    range a2:a1000

  9. #9
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,078

    Re: Extract multiple data from multiple column with one criteria

    Quote Originally Posted by HaroonSid View Post
    this is not working if i change
    range a2:a1000
    working now

    thank you

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Extract multiple data from multiple column with one criteria

    With Tim's: range should be A3:A1000 not A2: all formulae need to be changed accordingly and will work.

    Row 2 is header row, not first data row,

  11. #11
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Extract multiple data from multiple column with one criteria

    Dear Harron, Firstly remove merged cell.
    In "B26"
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copy across.
    File attach.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark 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. Extract Data with multiple criteria
    By miqureshi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-04-2017, 02:17 AM
  2. Replies: 4
    Last Post: 05-31-2017, 09:41 AM
  3. [SOLVED] extract multiple data based on criteria
    By wodo5 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-11-2017, 12:05 AM
  4. [SOLVED] Using Multiple Criteria to Extract Data
    By Philipsfn in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 11-21-2016, 01:10 PM
  5. [SOLVED] Extract data based on multiple criteria
    By Thanks4helping in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-21-2016, 04:31 PM
  6. [SOLVED] Extract data from table with multiple criteria
    By weedtm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-14-2016, 01:43 PM
  7. Extract Data Based on Multiple Criteria
    By trevaaaaaaa in forum Excel General
    Replies: 2
    Last Post: 03-14-2012, 11:42 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