Feb 02 2021 12:13 PM
Greetings all and I hope you can help.
Attach is my excel project. The goal is to execute filters on the Source tab and have the target tab reflect the results (same format) as the Source's filtered results.
For example:
1. Open attached Excel spreadsheet
2. On the "Source" datasheet is a display with filters
3. Locate and select "Instruction Name" column's drop-down box and select only "MAN 2101-01 Acceptance"
4. Locate and select "Applicability" column's drop-down box and select only "QAS"
5. The result displays 7 (seven) rows
The object is for the "Target" tab to reflect the exact display as the filtered "Source" tab.
Subsequently, future "Target" tabs will be added with different filtered results.
Help!
Software: MS Excel 2016
v/r
Redace2008
Feb 02 2021 01:05 PM
- add helper column to Source with
=AGGREGATE(3,5,B2)
- add headers to Target Sheet
- in A2
=IFERROR(
INDEX(Source!A$2:INDEX(Source!A:A, COUNTA(Source!$B:$B)+1),
AGGREGATE(15,6,1/(Source!$G$2:INDEX(Source!$G:$G, COUNTA(Source!$B:$B)+1)=1)*
ROW(Source!A$1:INDEX(Source!$B:$B, COUNTA(Source!$B:$B)+1)),
ROW()-ROW($B$1))),
"")
- drag to the right till next column
- drag entire line down till empty cells appear (with some gap)
Feb 02 2021 01:15 PM
What you're looking for is very simple with the most recent version of Excel. The solution I'm attaching takes advantage of the recently announced and delivered functions UNIQUE and FILTER. I'm pretty sure it won't work on Excel 2016, however. So here's a screen grab of the result you could have by upgrading. You'll see the main FILTER function in the function bar at the top... to get different results, you just change the entries in the yellow cells via drop-down selections. The filtered results will change accordingly. For what it's worth, that FILTER function appears in only one cell, E6. All of the results "Spill:" into adjacent rows and columns.
Feb 02 2021 02:46 PM
Feb 02 2021 03:14 PM
It works. Especially without adding third party solutions. Great answer; however, is writing a script the only way to achieve the same outcome...just asking and still I'm very appreciative and will apply what you have provided.
Thanks for your time and effort :)
Redace2008