1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter data to another sheet by cell value

Discussion in 'Ask an Excel Question' started by JAlandhar 201718, May 25, 2018.

  1. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    Hello! ,

    I just want to filter a data to another sheet, i tried VBA Codes/ autofilter / advance filter options but none of that helped me, can you please suggest me the solution ASAP, that how can i filter my data to another sheet by entering the cell value, that is already in the table..
    i filtered the data just to show you some same values from all table
    1.jpg

    like if the C1 is the place where i have to enter the value, and the table is on another sheet i.e. actually a .db file, i need to access some values from it..(Column A = Number, Column G = "LEDCode" which is repetitive). I just want to enter the "LEDCODE" in C1, and i want to get all the numbers which have the same code.

    an Example below :
    2.jpg
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Go through this link to understand how the Excel Advanced Filter feature works.

    https://www.ablebits.com/office-addins-blog/2016/09/07/excel-advanced-filter/

    The criteria range has to be two cells one below the other , with the criterion field name in the top cell , and the criterion value in the cell below that.

    A horizontal criteria range with the criterion field name in one cell , and the criterion value in the cell to its right will not work.

    Narayan
    Thomas Kuriakose likes this.
  3. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    can you please do that for me, if I provide you the whole worksheet as I tried using it below, but nothing happened
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Please upload your workbook with all of the data in it.

    Narayan
  5. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    sure, Please check the attached file, actual data is of 9 mb as it is connected with .db file, i just gave you a little data. Please help me to get a good solution for it.

    thanks

    Attached Files:

  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    What is to be done with duplicate voucher numbers ?

    Should they appear , or should only unique voucher numbers be displayed ?

    Narayan
  7. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    need the duplicate voucher no's too, as i need to look up amount for all the vouchers to be shown in another sheet
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    See the attached file for a formula-based solution.

    If this makes your working file sluggish , we can then think of a VBA-based solution using the Advanced Filter feature.

    Narayan

    Attached Files:

  9. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    THANKYOU SOOOO MUCH FOR YOUR KIND RESULT, LET ME attach it with my db file and work on it.


    thankyou soooo much for your help, can i message you again if i face any problem.



    regards
    Anshum Manocha
    Hoshiarpur(Punjab)
  10. AliGW

    AliGW Active Member

    Messages:
    288
    LED Code:

    =IF(B6="","",Entered_LEDCODE)

    VOUNO:

    =IFERROR(INDEX(DB!A:A,AGGREGATE(15,6,ROW(FDDR[VOUNO])/(FDDR[LEDCODE]=Entered_LEDCODE),ROWS($1:1))),"")
  11. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    Dear AligW,

    can you please explain me, how to apply codes to another excel sheet
  12. AliGW

    AliGW Active Member

    Messages:
    288
    How am I supposed to do that without seeing the other Excel sheet? Can you attach it?
  13. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    it is the same sheet, i can show you the screenshots as the file is more than 9MB
  14. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    It is the same sheet as I got the reply, my database has more entries than the file given, you can see the TEST file please
  15. AliGW

    AliGW Active Member

    Messages:
    288
    The TEST file is a different solution offered by a different member. Do you mean the DATA file?

    Why are you making this so difficult? Provide a sample file that is properly reflective of your real data to avoid wasting peoples' time.
  16. AliGW

    AliGW Active Member

    Messages:
    288
    LED Code (in A6):

    =IF(B6="","",Entered_LEDCODE)

    VOUNO (in B6):

    =IFERROR(INDEX(DB!A:A,AGGREGATE(15,6,ROW(FDDR[VOUNO])/(FDDR[LEDCODE]=Entered_LEDCODE),ROWS($1:1))),"")

    Once copied into those cells, drag copy down.
  17. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    I'm not wasting anyone's time, I just need to apply all the same codes and want the same result to my sheet, as the data of my sheet has more entries than the sample file.( the text.xls is the sample file of my sheet) the data in FDDR table in TEST.xls has only 100 entries, whereas my data contains 15000 entries
  18. AliGW

    AliGW Active Member

    Messages:
    288
    I can't tell you any more than I have based on the sample file provided. I do not understand what the problem is. If the table in your real data file has the same layout and field names, then the formula will work exactly the same as it does in your sample file. What exactly are you expecting?
  19. AliGW

    AliGW Active Member

    Messages:
    288
    I am attaching your data file with my formulae applied. I cannot do any more than this for you.

    Attached Files:

  20. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    thanks, I got the solution
  21. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,009
    1] Here is the screenshot of the OP's query : "he entered Mr. Narayan's formula in B6 and copied down, but from B7 and in downward returned #NUM ? "

    upload_2018-6-2_18-46-16.png

    2] The error is caused by :
    Mr. Narayan's formula (post #8) in B6 is an array formula, which need to press Ctrl+Shift+Enter 3 keystrokes together instead of just Enter.

    3] I think the OP's query is solved (as per post #20 replied).

    Regard
    Bosco
  22. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    yes Sir, that query is solved , now can you help me more related to this test file only??


    as first it was like related to LEDCODE and VOUNO, now what if I want to update the sheet more with the rest of the things, such as VOU_TYPE,VOU_DATE, VOU_TAG, AMOUNT, and other columns with the same field(LEDCODE)
  23. AliGW

    AliGW Active Member

    Messages:
    288
    Yes, as I suggested before, the OP was wasting my time. I've made a mental note not to bother trying to help him in future. I was pestered via private message to help, gave him a working solution in a duplicate thread, which was closed, then repeated it here along with an example file and you can see in post #20 what thanks I got for my efforts. Never again!
    Marc L likes this.
  24. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    For your kind info, i was not wasting your time, I just asked you about the solution a lot of time, you were just coping the formulas and trying to show me that I solved the thing. I was just asking that how to apply this on another sheet. you were just wasting my time by copy and paste the code. it was not working so i was asking again... and sorry to disturb you and you're mean you just wanted to have some fame so only you said, that "Can't reply on personal conversation." if you really wanted to help, then you must have replied there also, and for your kind info, "BOSCO" replied me just in a minute after I talked to him in personal conversation.
  25. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    I need one more help in this sheet, can you please help me again.

    as all the things you have told me worked good for me, now what I need is I need to bifurcate more data from this. I attached the file again, updating a Column in Table. Please check and Let me know that can it be more filtered differently

    Attached Files:

Share This Page