Need to get filtered table data based on particular column's filter value

It would appear from the sample in your other thread that you already have a solution for this.

If this is correct please mark this thread as solved.

Hi,
Thanks for the quick reply.
Sorry I dint get you. what i really meant is
As shown in new attachment, In Sheet2, upon selecting the part a-01R in picker, the resulted filtered table data should populate corresponding to the selected part and similarly for other parts a-02R,a-03R.
So please suggest me the appropriate way and formula in order to get it done.

Thanks

So both threads are the same question using different words?

When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

(link above in the menu bar)

Hi jason,
Yes, posted this thread for alternate possibility of the same thread.
Ok ill read them.
Thanks

See if this helps.

Note that is uses a named range and a little bit of vba to function. You will need to 'enable macros' in any security warnings that pop up in order for it to work.

a Non-VBA solution using Data Validation to select filter

In E

=IFERROR(INDEX(Sheet1!$A$2:$H$11,SMALL(IF(INDEX(Sheet1!$A$2:$H$11,,MATCH(Sheet2!$A$2,Sheet1!$A$1:$H$1,0))<>"",ROW($A$2:$A$11)-ROW($A$2)+1,""),ROWS($A$2:A2)),MATCH(Sheet2!$A$2,Sheet1!$A$1:$H$1,0)),"")

Enter with Ctrl+Shift+Enter

in F

=IFERROR(VLOOKUP(Sheet2!E3,Sheet1!$A$2:$B$11,2),"")

in G

=IFERROR(VLOOKUP($E3,Sheet1!$A$2:$H$11,MATCH($A$2,Sheet1!$A$1:$H$1,0)+1,0),"")

I removed "-" from entries in your table on Sheet1

Data validation list in L1:L3

Just for info: you could simplify the table on Sheet1. See attached.

Hi,
Thanks for giving both VBA and Non-VBA solutions.
In VBA solution,
i'm new to VBA, so if i add few more parts like a-04R,a-05R,.. how i can make this macro work?

And in Non-VBA solution,
if i have duplicate values like 'a-942' for 'a-02R', how i can get the appropriate description and count value(sorry i realised this scenario later).

PFA!!

Hi John,
Yes, i can have different description and quantity(qty) for same parts and it should populate accordingly.

And can i have this table generated dynamically upon cell click of corresponding part cell rather than in dropdown(as shown in sheet4). If possible can you please guide me through it also so that i can extend it.


Thanks,
Veeresh

Hi John,
Thanks for the support and solutions even for extending it.

For VBA solution,
I'm getting an issue while using it from my end.And it is,
For duplicate records, if at all description is also same its returning first matched record values(description,count,etc) for both the duplicate entries which shouldn't be the case. Please provide me the solution for it.

If you have multiple entries for a given description then (I think) you will need to (a) produce a list of unique descriptions and (b) summate the data for that description )???)


Please post a file with an example of the problem and show expected results.

In G2

=IFERROR(INDEX(Sheet1!$B$2:$Z$100,SMALL(IF(INDEX(Sheet1!$A$2:$Z$100,,MATCH(SELECTION,Sheet1!$A$1:$Z$1,0)+1),ROW($A$2:$A$100)-ROW($A$2)+1,""),ROWS($A$2:A2)),MATCH(SELECTION,Sheet1!$A$1:$Z$1,0)),"")

Array confirmed with Shift Ctrl Enter.

Click Ok, then fill down.

Make a selection from column A.

Formula is from Jason.

Hi,
I have updated in other site about the cross-thread.

Thanks Jason for formula.

Jason/John,
I have implemented the suggestions/formulas and got struct at a point i.e.,i have merged the duplicate cells and so, the lookup value is taking only the first value of all duplicates. Please suggest me further.

Thanks in advance..
PFA

As far as I can see there are no duplicates in the file you provided.

Originally Posted by veeresh540

Hi,
I have updated in other site about the cross-thread.

You should also edit your original post here to include a link to the other site.

This should include any other sites that you have posted the same question, not just the one we have found.

Hi,
I have posted thread only in 2 forums.And edited original post with cross-thread link.

John,
Sorry for the change of requirement with time.
Coming to functionality, Consider e.g., p-65 which has two vendors and two pins should get displayed in 'Status' sheet ratherthan only the first matching value. Please help in getting it.

Thanks
Veeresh

Remove the merged cells, and enter the data into each individual cell.

With E7:E8 merged, the formula will see E8 as an empty cell so there is no data to return.

There are ways around merged cells, but they will not work with your sheet, the requirement is too complex.

Can u please explain why it can't be achieved.
And is it because using VBA??
For e.g.,If we keep SY-0399140R-AS and SY-0399142R-AS in a picker rather than VBA select way, Can it be possible??
If not, Is there any alternate way to achieve it?

It cannot be achieved because you have merged cells.

The method used has nothing to do with it, VBA could achieve what you want easier than a formula, so that is not the reason why it fails.

The alternate way is to stop using merged cells.

See attached.