Forum Discussion
PapaGoose
Feb 04, 2025Copper Contributor
Excel - Vstack help.
I feel like I'm missing something simple. but here goes. I'm using the VStack function to search multiple tables on separate sheets. Its working wonderfully except as it runs through each filter function, If a table does not return results, it creates a blank row before running the next filter function on the row below. See below.
Most of the data is irrelevant for the build. The first column contains the Filter criteria. The second column is a reference column that tells me which sheet the column is coming from so i can keep up with them inside the functions. My entire formula is below.
=IFERROR(VSTACK(FILTER(Narkiewiecz[[House Code]:[Invoice Paid?]], Narkiewiecz[House Code] = Access!$B$4, ""),FILTER(Table47[[House Code]:[Invoice Paid?]], Table47[House Code] = Access!$B$4, ""),FILTER(Table48[[House Code]:[Invoice Paid?]], Table48[House Code] = Access!$B$4, ""),FILTER(Table49[[House Code]:[Invoice Paid?]], Table49[House Code] = Access!$B$4, ""),FILTER(Table410[[House Code]:[Invoice Paid?]], Table410[House Code] = Access!$B$4, "")),"")
I've written in for each filter to return blank if no results are found, so I get that's why the columns are blank. Is there another [if_empty] I could write in that would allow it to stack each found result seamlessly while ignoring empty searches?
=LET(result, VSTACK(FILTER(Narkiewiecz[[House Code]:[Invoice Paid?]], Narkiewiecz[House Code] = Access!$B$4, ""),FILTER(Table47[[House Code]:[Invoice Paid?]], Table47[House Code] = Access!$B$4, ""),FILTER(Table48[[House Code]:[Invoice Paid?]], Table48[House Code] = Access!$B$4, ""),FILTER(Table49[[House Code]:[Invoice Paid?]], Table49[House Code] = Access!$B$4, ""),FILTER(Table410[[House Code]:[Invoice Paid?]], Table410[House Code] = Access!$B$4, "")), FILTER(result, CHOOSECOLS(result, 1)<>""))