Filter and Search with VSTACK

A

AkelaWolf76

New Member
Joined
Aug 15, 2023
Messages
10
Office Version
  1. 365
Platform
  1. Windows
I have 2 tables on separate tabs that I would like to search for any row containing a criteria and return the whole row.

After converting the data to tables I create a new tab just for my search and am using cell B3 as my search criteria text box.

I am using the following formula and no matter the search criteria, I get the "No Result" error message returned despite copying the value from one of the table fields in the first place.

=IFERROR(FILTER(VSTACK(Table1[#ALL],Table2),ISNUMBER(SEARCH($B$3,VSTACK(Table1,Table2)))),"No Result")

When I break it down and just use the VSTACK function, this returns a combined table of all data in Table1 & Table2 but as soon as I add the filter function, it errors and the same with the search function.

I have put the formula into gptexcel and the explanation is what I am expecting but cannot get it to work.
"This formula uses the FILTER function to filter a vertical stack of two tables (Table1 and Table2) based on whether a specific text value in cell B3 is found within each row. The ISNUMBER and SEARCH functions are used to check if the text value in B3 is found in each row, and the IFERROR function is used to handle any errors that may occur. If a match is found, the filtered result is returned. If no match is found, the formula returns "No Result""

Any assistance would be greatly appreciated.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Hi & welcome to MrExcel.
Try
Excel Formula: 
=FILTER(VSTACK(Table1,Table2),ISNUMBER(SEARCH($B$3,VSTACK(Table1,Table2))),"No Result")
or a bit shorter
Excel Formula: 
=let(v,VSTACK(Table1,Table2),filter(v,ISNUMBER(SEARCH($B$3,v)),"No Result"))
 
Upvote 0
Fluff said:
Hi & welcome to MrExcel.
Try
Excel Formula: 
=FILTER(VSTACK(Table1,Table2),ISNUMBER(SEARCH($B$3,VSTACK(Table1,Table2))),"No Result")
or a bit shorter
Excel Formula: 
=let(v,VSTACK(Table1,Table2),filter(v,ISNUMBER(SEARCH($B$3,v)),"No Result"))
Click to expand...
I tried this as well but it returns a #VALVE! error and not even my no result return.
 
Upvote 0
In that case can you post some sample data along with expected results.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Fluff said:
In that case can you post some sample data along with expected results.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Click to expand...
Added the plug in but it wouldn't let be execute the mini sheet. so her is an exert from Table 1:
WO Rec'dWOTypePRJ CODEMP CodeResponsible personDriverShared/Uni
17/03/2020844039New MW link UPH300023MP2313AYCongestionUnilateral
03/11/20221388970SW UpgradePJ00015MP0899DHCongestionShared
18/06/20211318850SW UpgradePJ00015MP0899DHCongestionShared
18/08/20211325970New MW link UPPJ00233MP2726AYCustomer ExperienceShared
16/02/20231401154Link UpgradePJ00015MP1999AYCongestionShared
03/05/20221362957Link UpgradePJ00015MP1999AYCongestionShared
01/11/20221388961Link UpgradePJ00015MP1999AYCongestionShared
10/10/20221385071Link UpgradeH300075MP2652AYCongestionUnilateral
22/03/20231407135Link UpgradeH300075MP2652JHCongestionUnilateral
21/03/20221357382SW UpgradePJ00015MP0899DHCongestionShared

And table 2
WO Rec'dWOTypePRJ CODEMP CodeResponsible personDriverShared/Uni
17/03/2020844039New MW link UPH300023MP2313AYCongestionUnilateral
03/11/20221388970SW UpgradePJ00015MP0899DHCongestionShared
18/06/20211318850SW UpgradePJ00015MP0899DHCongestionShared
18/08/20211325970New MW link UPPJ00233MP2726AYCustomer ExperienceShared
16/02/20231401154Link UpgradePJ00015MP1999AYCongestionShared
03/05/20221362957Link UpgradePJ00015MP1999AYCongestionShared
01/11/20221388961Link UpgradePJ00015MP1999AYCongestionShared
10/10/20221385071Link UpgradeH300075MP2652AYCongestionUnilateral
22/03/20231407135Link UpgradeH300075MP2652JHCongestionUnilateral
21/03/20221357382SW UpgradePJ00015MP0899DHCongestionShared

And the returns:

SEARCH BOX
MP0899
=IFERROR(FILTER(VSTACK(Table1[#All],Table2),ISNUMBER(SEARCH($B$3,VSTACK(Table1,Table2)))),"No Result")
No Result
=FILTER(VSTACK(Table1,Table2),ISNUMBER(SEARCH($B$3,VSTACK(Table1,Table2))),"No Result")
#VALUE!
 
Upvote 0
As you are just using col E try
Excel Formula: 
=LET(v,VSTACK(Table1,Table2),FILTER(v,ISNUMBER(SEARCH($B$3,INDEX(v,,5))),"No Result"))
 
Upvote 0
MBNL MW Tracker 20230808.xlsx
ABCDEFGHI
1WO Rec'd MS05TNETypeWRPRJ CODEMP CodeMBNL Responsible DriverShared/Uni
221/01/20191169923New MW link UPWR211020PJ00015MP1999AYCongestionShared
301/02/20191163586Dish SwapWR301211PJ00015MP1999AYCongestionShared
405/08/20201254705SW Upgrade0PJ00015MP0899DHCongestionShared
509/05/20221352150Dish MoveWR1261830PJ00233MP2727AYCustomer ExperienceShared
606/06/20221362942SW UpgradeWR1231318EE00110MP2719DHCongestionUnilateral
703/05/20181097802SW Upgrade0PJ00015MP0899DHCongestionShared
803/05/20181105478SW Upgrade0PJ00015MP0899DHCongestionShared
903/05/20181096017SW Upgrade0PJ00015MP0899DHCongestionShared
1003/05/20181102075SW Upgrade0PJ00015MP0899DHCongestionShared
1103/05/20181096045SW Upgrade0PJ00015MP0899DHCongestionShared
1203/05/20181105194SW Upgrade0PJ00015MP0899DHCongestionShared
1325/05/20181113460SW Upgrade0PJ00015MP0899DHCongestionShared
MW Tracker
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F1Cell ValueduplicatestextNO
B:BCell ValueduplicatestextNO
B:BCell ValueduplicatestextNO




MBNL MW Tracker 20230808.xlsx
ABCDEFGHI
1WO Rec'd MS05TNETypeWRPRJ CODEMP CodeMBNL Responsible DriverShared/Uni
217/03/2020844039New MW link UPWR770914H300023MP2313AYCongestionUnilateral
303/11/20221388970SW Upgrade0PJ00015MP0899DHCongestionShared
418/06/20211318850SW Upgrade0PJ00015MP0899DHCongestionShared
518/08/20211325970New MW link UPWR2300947PJ00233MP2726AYCustomer ExperienceShared
616/02/20231401154Link UpgradeWR411045PJ00015MP1999AYCongestionShared
703/05/20221362957Link UpgradeWR1231337PJ00015MP1999AYCongestionShared
801/11/20221388961Link UpgradeWR3051726PJ00015MP1999AYCongestionShared
910/10/20221385071Link UpgradeWR2831036H300075MP2652AYCongestionUnilateral
1022/03/20231407135Link UpgradeWR810845H300075MP2652JHCongestionUnilateral
1121/03/20221357382SW Upgrade0PJ00015MP0899DHCongestionShared
1220/05/20201253643Link UpgradeWR1401729PJ00015MP1999AYCongestionShared
1320/05/20201253657Link UpgradeWR1401729PJ00015MP1999AYCongestionShared
Cancelled
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F1Cell ValueduplicatestextNO
B:BCell ValueduplicatestextNO
B:BCell ValueduplicatestextNO



MBNL MW Tracker 20230808.xlsx
AB
2SEARCH BOX
3MP0899
4
5=IFERROR(FILTER(VSTACK(Table1[#All],Table2),ISNUMBER(SEARCH($B$3,VSTACK(Table1,Table2)))),"No Result")
6No Result
7
8=FILTER(VSTACK(Table1,Table2),ISNUMBER(SEARCH($B$3,VSTACK(Table1,Table2))),"No Result")
9#VALUE!
search
Cell Formulas
RangeFormula
A6A6=IFERROR(FILTER(VSTACK(Table1[#All],Table2),ISNUMBER(SEARCH($B$3,VSTACK(Table1,Table2)))),"No Result")
A9A9=FILTER(VSTACK(Table1,Table2),ISNUMBER(SEARCH($B$3,VSTACK(Table1,Table2))),"No Result")
Named Ranges
NameRefers ToCells
'MW Tracker'!_FilterDatabase='MW Tracker'!$A$1:$BT$1477A6
Cancelled!PIV_F_C_no_header=Cancelled!$AN$2:$AN$1048576A6, A9
PIV_F_C_no_header='MW Tracker'!$AN$2:$AN$1048576A6, A9
'MW Tracker'!Print_Area='MW Tracker'!$O$24:$BE$93A6, A9
 
Upvote 0
Thanks for that, did you try the formula I suggested in post#8?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
ChooseCols with VStack and then a filter issue
Replies
1
Views
551
Fluff
Fluff
C
  • Question Question
VSTACK
Replies
1
Views
215
ExcelToDAX
E
N
  • Question Question
VSTACK data from multiple sheets and filter results for specific dates
Replies
2
Views
2K
newxl
N
H
  • Solved
Return and rank top 3 results (including ties) from VSTACK, FILTER, CHOOSECOLS, and SORT formula
Replies
8
Views
204
Peter_SSs
Peter_SSs
L
  • Solved
=unique(vstack in a table
Replies
6
Views
464
Vogateer
V

Forum statistics

Threads
1,224,814
Messages
6,181,121
Members
453,021
Latest member
Justyna P

Share this page

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