• 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...

  • 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

J

JAlandhar 201718

Member
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
 
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
 
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
 

Attachments

  • Data.xlsx
    17.1 KB · Views: 9
Hi ,

What is to be done with duplicate voucher numbers ?

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

Narayan
 
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)
 
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))),"")
 
How am I supposed to do that without seeing the other Excel sheet? Can you attach it?
 
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.
 
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.
 
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
 
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?
 
I am attaching your data file with my formulae applied. I cannot do any more than this for you.
 

Attachments

  • Data-1 AliGW.xlsx
    18.2 KB · Views: 5
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
 
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)
 
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!
 
AliGW said:
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!
Click to expand...
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.
 
bosco_yip said:
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 ? "

View attachment 52670

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
Click to expand...
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
 

Attachments

  • TEST.xlsx
    22.1 KB · Views: 4
You must log in or register to reply here.
Back
Top