hallpett
asked on
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
The List sheet is where the user register which structuritems he wants in his materiallist. Could be one or many structuritems.
Then I want to create a new table, a kind of materiallist, with all items belonging to the given structuritems. Like this:
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.
The sheet DataItems show Structuritems and items organized under a structuritem
The List sheet is where the user register which structuritems he wants in his materiallist. Could be one or many structuritems.
Then I want to create a new table, a kind of materiallist, with all items belonging to the given structuritems. Like this:
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.
ASKER
Here it comes.
Materiallist.xlsx
Materiallist.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
gowflow