Solved

Use one table as qriteria to filter another

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

770 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