+ Reply to Thread
Results 1 to 4 of 4

Search from filtered row returning values of not blank cell

  1. #1
    Registered User
    Join Date
    09-10-2019
    Location
    Erie, PA
    MS-Off Ver
    365
    Posts
    3

    Search from filtered row returning values of not blank cell

    I am trying to make a Search sheet that searches a Master list and returns a list of items that person has.
    Ideally this would return the quantity in the column combined with the header row.

    Ref attached. As you can seen when the basket number is entered, it populates the name of the basket holder; I've got the filtering figured out, its just the rest. I want it would fill down the rows rather than across columns (I was able to do that), as I would intend this to be printable on a regular portrait orientation. The original workbook has 33 "fruit" choices so it could get unwieldy

    I have tried to cobble together a few things I have found in search but the combinations don't work. Maybe its not possible in which case I'll figure something else out.

    Thanks for any help.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Search from filtered row returning values of not blank cell

    Please take a momewnt NOW to update your profile. You CAN NOT still be using Excel 2010... if you are using FILTER.

    D2:
    =IFERROR(FILTER(MASTER!A2:A7,MASTER!B2:B7=C1),"")

    D4:
    =LET(r,INDEX(MASTER!D2:I7,Search!C1,),TRANSPOSE(FILTER(r,r>0)&" "&FILTER(MASTER!D1:I1,r>0)))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    09-10-2019
    Location
    Erie, PA
    MS-Off Ver
    365
    Posts
    3

    Re: Search from filtered row returning values of not blank cell

    Thank you Glenn! (I corrected my profile, good note)
    Finally getting back around to trying to make this work.

    When I expand the range in my actual document from:
    =LET(r,INDEX(MASTER!D2:I7,Search!C1,),TRANSPOSE(FILTER(r,r>0)&" "&FILTER(MASTER!D1:I1,r>0)))

    To:
    =LET(r,INDEX(MASTER!AB2:BQ500,Fielded!C1,),TRANSPOSE(FILTER(r,r>0)&" "&FILTER(MASTER!AB1:BQ1,r>0)))

    I get a #CALC! error stating an empty array. Is my array to large? Do I need to fill empties with a "0" value?

    Thanks for any help.

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Search from filtered row returning values of not blank cell

    Power Query

    NAME
    Basket
    Shoping Date
    Apples
    Oranges
    Bananas
    Mango
    Pineapple
    Guava
    ID
    NAME Fruits
    Adam
    1
    1
    3
    2
    2
    Boy 2 Bananas, 4 Oranges, 6 Guava, 7 Mango
    Boy
    2
    4
    2
    7
    6
    Charles
    3
    01/01/2021
    1
    1
    1
    1
    David
    4
    2
    5
    4
    4
    Edward
    5
    2
    9
    Charles
    6


    Please Login or Register  to view this content.

+ 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. Replies: 8
    Last Post: 09-05-2021, 04:50 AM
  2. Replies: 5
    Last Post: 09-24-2015, 03:15 PM
  3. [SOLVED] Returning a blank cell if no values entered into Columns D or E
    By moosetales in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-09-2014, 07:44 AM
  4. Returning the values of filtered items on another sheet
    By tangcla in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-11-2013, 10:46 PM
  5. Replies: 4
    Last Post: 05-29-2013, 04:18 AM
  6. Excel 2007 : Returning filtered data into new cell
    By haleyt in forum Excel General
    Replies: 1
    Last Post: 05-21-2012, 12:01 PM
  7. search cell if blank delete cell shift values up
    By randell.graybill in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2009, 10:01 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