Use one table as qriteria to filter another

In my workbook I have two tables with a one-to-many relationship between them. I want to merge this two so I get a new table with only the related records.

The sheet DataItems show Structuritems and items organized under a structuritem
 Structuritems with items  

The List sheet is where the user register which structuritems he wants in his materiallist. Could be one or many structuritems.
structuritems in the order
Then I want to create a new table, a kind of materiallist, with all items belonging to the given structuritems. Like this:
Materiallist
I think I could manage this by using vba. Create a code that loops trough each row in List and then loop trough each row in DataItems to find what I'm looking for. Sounds like a difficult task and would demand a lot of searching for solutions online. Could there be an easier way to do this kind of task in Excel? Appreciate any tips or hints.
hallpettAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
You can do this with the built in Advanced Filter function.

Your original list is the Data Source, your user Registered list is the Criteria.

This can filter in place or copy required entries to separate list.

Thanks
Rob H
0
 
gowflowCommented:
Could you post your workbook easier to see
gowflow
0
 
hallpettAuthor Commented:
Here it comes.
Materiallist.xlsx
0
 
hallpettAuthor Commented:
Damned, that was a good solution. Sometimes I don't see the forrest because of all the trees.
Still need some vba to create a column that shows qty * qty from the two tables, but this I can do using the macro recorder and a lookup function. 1000 times easier than writing code that loops the tables. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.