Extract data (Filter) from a table using criteria from a diferent table

Truiz

Active Member
Joined
Jul 14, 2014
Messages
339
Good Evening,

I have a table that looks like this (It's over 400 rows)

IDRequesterAgent assignedStore NumberDistrictTitleStatusPriorityCreated onUpdated OnDays Opened up to dateLast Updated (days)Follow up NotationsLabel
FD20210222366Jane DoeTom Ruiz185District 9Title 1In ProgressLow22-Feb-20218-Mar-2021960946Signage
FD20210531656John DoeGerard Gonzalez185District 9Title 2In ProgressHigh31-May-20211-Jun-2021862861Other
FL20210611028Jane DoeRaul Becks6District 3Title 3ReopenHigh11-Jun-202114-Feb-2023851238Paving
FD20210611191John DoeTom Ruiz90District 9Title 4In ProgressMedium11-Jun-202115-Jun-2021851847Glass & Windows
FL20210917983Jane DoeGerard Gonzalez7District 9Title 5In ProgressMedium17-Sep-20215-Jun-2023753127Paving
FD20211025668John DoeRaul Becks134District 6Title 6In ProgressHigh25-Oct-202125-Oct-2021715715Doors & Hardware
FL20211229579Jane DoeTom Ruiz7District 9Title 7In ProgressMedium29-Dec-202110-Jan-2023650273Paving
FD20220131841John DoeGerard Gonzalez230District 15Title 8In ProgressHigh31-Jan-202231-Jan-2022617617Glass & Windows
FD20220217753Jane DoeRaul Becks262District 12Title 9Pending Store ResponseMedium17-Feb-202210-Jan-2023600273Glass & Windows
PS20220217591John DoeTom Ruiz4District 12Title 10In ProgressLow17-Feb-20225-Sep-2022600400Order Request/Cleaning items
FD20220220199Jane DoeGerard Gonzalez4District 12Title 11In ProgressHigh20-Feb-20226-Jul-2022597461Glass & Windows
FF20220307978John DoeRaul Becks220District 14Title 12In ProgressHigh7-Mar-20229-Mar-2022582580Flooring
FE20220331374Jane DoeTom Ruiz115District 9Title 13In ProgressMedium31-Mar-202210-Jan-2023558273$3, 810Plumbing
FD20220425563John DoeGerard Gonzalez94District 10Title 14Pending Store ResponseLow25-Apr-202228-Jun-2022533469S94Order Request/Cleaning items
FS20220510282Jane DoeRaul Becks273District 14Title 15In ProgressHigh10-May-20221-Jul-2022518466Signage
FE20220624418John DoeTom Ruiz176District 17Title 16In ProgressHigh24-Jun-202230-Jun-2022473467Electrical/Lighting
FL20220627118Jane DoeGerard Gonzalez270District 14Title 17In ProgressLow27-Jun-20223-May-2023470160Landscaping
FF20220630611John DoeRaul Becks41District 2Title 18In ProgressHigh30-Jun-202213-Jul-2022467454Doors & Hardware
FE20220630899Jane DoeTom Ruiz41District 2Title 19In ProgressHigh30-Jun-202213-Feb-2023467239Electrical/Lighting
FD20220707552John DoeGerard Gonzalez76District 10Title 20In ProgressMedium7-Jul-202228-Jul-2022460439Electrical/Lighting
FE20220716459Jane DoeRaul Becks273District 14Title 21In ProgressHigh16-Jul-20224-Jan-2023451279Ask for picturesFurniture
FE20220719024John DoeTom Ruiz92District 15Title 22In ProgressMedium19-Jul-202220-Jul-2022448447Fire Safety
FD20220802444Jane DoeGerard Gonzalez19District 1Title 23In ProgressHigh2-Aug-202216-Nov-2022434328Doors & Hardware
FD20220802399John DoeRaul Becks31District 1Title 24In ProgressHigh2-Aug-20225-Aug-2022434431Other
FF20220802359Jane DoeTom Ruiz31District 1Title 25ReopenHigh2-August-20222-October-20234348Flooring


On a different sheet I have another table that looks like this:
Key Focus Tickets
Doors & HardwareElectrical/LightingFlooringGlass & WindowsLandscapingRoof leaksSignage
District 1655--6--8
District 2--7----------
District 3--------5----
District 4--5----------
District 65----------5
District 8------------5
District 9----------65
District 116------------
District 15------5------
District 17--10------65


What I'm trying to accomplish is to have on a third sheet a table were the tickets displayed will only be the ones from the districts and labels show on the second table so for example it will show the 6 tickets for doors and hardware from District 1 but no doors & hardware tickets for District 2.


I Tried using the Filter Formula but can't get to nest all the criteria, also using the 2nd table is not strictly neccesary since that table is build using the following criteria: If a lebal has more than 5 tickets in the same District is added to the table


thanks
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
I think this should get you headed in the right direction...
Book1
ABCDEFGHIJKLMNOPQR
1IDRequesterAgent assignedStore NumberDistrictTitleStatusPriorityCreated onUpdated OnDays Opened up to dateLast Updated (days)Follow up NotationsLabelDistrict 1Doors & Hardware
2FD20210222366Jane DoeTom Ruiz185District 9Title 1In ProgressLow4424944263960946SignageDistrict 10Electrical/Lighting
3FD20210531656John DoeGerard Gonzalez185District 9Title 2In ProgressHigh4434744348862861OtherDistrict 12Fire Safety
4FL20210611028Jane DoeRaul Becks6District 3Title 3ReopenHigh4435844971851238PavingDistrict 14Flooring
5FD20210611191John DoeTom Ruiz90District 9Title 4In ProgressMedium4435844362851847Glass & WindowsDistrict 15Furniture
6FL20210917983Jane DoeGerard Gonzalez7District 9Title 5In ProgressMedium4445645082753127PavingDistrict 17Glass & Windows
7FD20211025668John DoeRaul Becks134District 6Title 6In ProgressHigh4449444494715715Doors & HardwareDistrict 2Landscaping
8FL20211229579Jane DoeTom Ruiz7District 9Title 7In ProgressMedium4455944936650273PavingDistrict 3Order Request/Cleaning items
9FD20220131841John DoeGerard Gonzalez230District 15Title 8In ProgressHigh4459244592617617Glass & WindowsDistrict 6Other
10FD20220217753Jane DoeRaul Becks262District 12Title 9Pending Store ResponseMedium4460944936600273Glass & WindowsDistrict 9Paving
11PS20220217591John DoeTom Ruiz4District 12Title 10In ProgressLow4460944809600400Order Request/Cleaning itemsPlumbing
12FD20220220199Jane DoeGerard Gonzalez4District 12Title 11In ProgressHigh4461244748597461Glass & WindowsSignage
13FF20220307978John DoeRaul Becks220District 14Title 12In ProgressHigh4462744629582580Flooring
14FE20220331374Jane DoeTom Ruiz115District 9Title 13In ProgressMedium4465144936558273$3, 810Plumbing
15FD20220425563John DoeGerard Gonzalez94District 10Title 14Pending Store ResponseLow4467644740533469S94Order Request/Cleaning items
16FS20220510282Jane DoeRaul Becks273District 14Title 15In ProgressHigh4469144743518466Signage
17FE20220624418John DoeTom Ruiz176District 17Title 16In ProgressHigh4473644742473467Electrical/Lighting
18FL20220627118Jane DoeGerard Gonzalez270District 14Title 17In ProgressLow4473945049470160Landscaping
19FF20220630611John DoeRaul Becks41District 2Title 18In ProgressHigh4474244755467454Doors & Hardware
20FE20220630899Jane DoeTom Ruiz41District 2Title 19In ProgressHigh4474244970467239Electrical/Lighting
21FD20220707552John DoeGerard Gonzalez76District 10Title 20In ProgressMedium4474944770460439Electrical/Lighting
22FE20220716459Jane DoeRaul Becks273District 14Title 21In ProgressHigh4475844930451279Ask for picturesFurniture
23FE20220719024John DoeTom Ruiz92District 15Title 22In ProgressMedium4476144762448447Fire Safety
24FD20220802444Jane DoeGerard Gonzalez19District 1Title 23In ProgressHigh4477544881434328Doors & Hardware
25FD20220802399John DoeRaul Becks31District 1Title 24In ProgressHigh4477544778434431Other
26FF20220802359Jane DoeTom Ruiz31District 1Title 25ReopenHigh44775452014348Flooring
Sheet1
Cell Formulas
RangeFormula
Q1:Q10Q1=SORT(UNIQUE(Table1[District]))
R1:R12R1=SORT(UNIQUE(Table1[Label]))
Dynamic array formulas.

Book1
ABCDEFGHIJKLMN
1DistrictLabel
2District 9Paving
3
4IDRequesterAgent assignedStore NumberDistrictTitleStatusPriorityCreated onUpdated OnDays Opened up to dateLast Updated (days)Follow up NotationsLabel
5FL20210917983Jane DoeGerard Gonzalez7District 9Title 5In ProgressMedium9/17/20216/5/20237531270Paving
6FL20211229579Jane DoeTom Ruiz7District 9Title 7In ProgressMedium12/29/20211/10/20236502730Paving
7
8
9
10
11
12
13
Sheet2
Cell Formulas
RangeFormula
A5:N6A5=IF(AND($A$2="",$B$2=""),Table1,FILTER(Table1,(IF($A$2="",TRUE,Table1[District]=Sheet2!$A$2))*(IF($B$2="",TRUE,Table1[Label]=Sheet2!$B$2)),"none found"))
Dynamic array formulas.

I added some dropdowns on the second sheet so you can select a District and Label. It will filter the results and show them below your selections. You can leave the District blank to see results for all Districts. Same for the Labels.

Hope that helps,

Doug
 
Upvote 0
Thanks,

I think however this would only show me the Paving Tickets for Distrit 9 What I'm looking for based on my example is a way to show:

All the Doors & Hardware Electrical/Lighting Flooring Landscaping Signage From district 1
The Electrical/Lighting from District 2
Tha Landscaping tickets from District 3
The Electrical/Lighting from District 4
etc etc etc
 
Upvote 0
On sheet 2 of my example you can change cells A2 and B2 via drop down to view any combination of district and label.

Doug
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top