Solved

Use one table as qriteria to filter another

Posted on 2014-01-02
4
159 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 31

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

705 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

13 Experts available now in Live!

Get 1:1 Help Now