Solved

Use one table as qriteria to filter another

Posted on 2014-01-02
4
193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 33

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EXCEL Name Matching 13 45
Formula fix for vlookup and iserror 3 28
Tricky Shapes formula part 4 4 14
Excel Formula to Iterate 4 12
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

733 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