Solved

Use one table as qriteria to filter another

Posted on 2014-01-02
4
171 Views
Last Modified: 2014-01-03
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.
0
Comment
Question by:hallpett
  • 2
4 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39753365
Could you post your workbook easier to see
gowflow
0
 

Author Comment

by:hallpett
ID: 39753491
Here it comes.
Materiallist.xlsx
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39753604
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
 

Author Closing Comment

by:hallpett
ID: 39753681
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now