PDA

View Full Version : [SOLVED] Generating results from different sheet by filters



sllaksvb
10-17-2017, 09:21 AM
Hi all,

I'm working on a project that requires me to return results from a master list based on the filters specified. However, I'm not sure how to go about tackling this as my knowledge is not in depth enough.

What I'm looking to do is, in Workbook REQUIREMENTS, specify all TV brands and locations that I approved (can be more than 1, as shown in example)
Then, click generate, which would prompt me to open a file (which I will use to open MASTER).
After clicking, it will compare the TV brands with the TV brands and locations in the masterlist, and return the ones that match both criteria to workbook REQUIREMENTS (Should only return results that match both criteria, as shown in example).

This macro is meant to be dynamic (Should be able to add additional criterias in both workbooks) and still function.
(i.e. If i add TV Size as a category, with 40, 60, 65, as my criteria on both workbooks, should be able to use it as a filter criteria.)

Any suggestions or guidance is greatly appreciated! Thank you.

2067820679

austenr
10-17-2017, 10:44 AM
You mentioned the ones you approved. From looking at the workbooks how does one know that? Also in the Requirements WB what are filers2, etc used for?

sllaksvb
10-17-2017, 11:09 AM
Hi austenr,

Sorry for the ambiguity. By approved I mean anything that's put in the filters.
Filters 2, 3, 4, 5, 6 is meant for additional filters. For example, I could approve all Samsung & Sony TV brands for Mexico and Canada. That would That will search the masterlist for combination of matches between Samsung/Sony and US/Canada and return it under the Results: column in REQUIREMENTS. Currently there are only 3 TV brands and 3 countries. I'm trying to make it so that I could add additional TV brands & countries (up to 6), and additional categories as well, (TV size, specifications, etc).

I've attached new files to describe what I mean. Thank you for your help!

2068220683

austenr
10-17-2017, 12:10 PM
you probably could use index/match to do what you want. dont have time to cobble it up right now. someone will probably pick it up from here.

sllaksvb
10-17-2017, 12:36 PM
austenr, do you have any suggestions on methods I should research to try to put this project together? I'm completely stuck and unsure of how to progress.

austenr
10-17-2017, 01:55 PM
so after you filter as the snippet below shows where do you want the other related data to be?

20685

austenr
10-17-2017, 01:57 PM
can you put together a requirements dummy workbook so we know what you are trying to accomplish as the finished product?

sllaksvb
10-17-2017, 02:13 PM
austenr, thank you for your reply. The finished product is presented in the second REQUIREMENTS attachment above.

For Example, if you put Sharp and MEXICO in filter 1, and SONY and CANADA in filter 2, it should search/lookup the values in the masterlist, and return the values that match both. Meaning, all cells with "Sharp" or "Sony" in masterlist that has a location of "CANADA" or "MEXICO" should be returned in "Results:" of REQUIREMENTS file.

See the pictures attached, the yellow highlights is for visuals, when there is a match (cells of TV brand and location both highlighted in the same row), the company name should be returned in "REQUIREMENTS".


2068620687

austenr
10-17-2017, 02:54 PM
use a double VLOOKUP. Have a look at these sites.

http://www.exceltactics.com/vlookup-multiple-criteria-using-index-match/

https://exceljet.net/formula/index-and-match-with-multiple-criteria

no VBA required.

sllaksvb
10-18-2017, 06:11 AM
austenr, I require VBA as it is part of a larger macro. Will do some research on double Vlookup and try to integrate it to my macro. Thank you!

sllaksvb
10-18-2017, 07:28 AM
austenr, I am unsure of how to use double lookup while setting my lookup value as a variable. Please advise