I want to create a summary table of three sheets, but only using data that meets specific criteria.

Question

I have three excel sheets with columns A-J. I want to make a summary table that only includes columns A, F and G. Additionally, I want to only add items to my summary that have an item in Column F.

Is there a way to do this using XLOOKUP or VSTACK? The pivot table contains too much data.

Answer 1

Hi @Pey,

Thank you for posting your question in the Microsoft Q&A forum.

According to your situation, you can use VSTACK combined with CHOOSECOLS to select the specific columns that you need. You can try using this formula:

=VSTACK(FILTER(CHOOSECOLS(A1:J10,1,6,7),F1:F10<>""))

This formula will export only if the according cell in column F of that row contains data. It will also automatically update for you when you modify (add/delete) anything in column A,F,G.

Note: these formulas are using the data range in my testing worksheet. Therefore, you will need to change the data range with your current worksheet to ensure the formulas work properly.

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this.    

Thank you for your patience and your understanding. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you. 

I look forward to hearing from you.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have any extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

To create a summary table from three Excel sheets that includes only columns A, F, and G, and filters out rows where column F is empty, you can use the FILTER function in combination with VSTACK to consolidate the data from the sheets. Here’s a general approach:

1. Use FILTER to Select Data: For each sheet, use the FILTER function to select only the rows where column F is not empty. The formula would look something like this for each sheet:

      =FILTER(Sheet1!A:G, Sheet1!F:F <> "")
   

   Repeat this for Sheet2 and Sheet3.
2. Combine Data with VSTACK: After filtering the data from each sheet, you can combine the results using VSTACK. The formula would look like this:

      =VSTACK(FILTER(Sheet1!A:G, Sheet1!F:F <> ""), FILTER(Sheet2!A:G, Sheet2!F:F <> ""), FILTER(Sheet3!A:G, Sheet3!F:F <> ""))
   

3. Select Specific Columns: Since you only need columns A, F, and G, you can wrap the VSTACK function with another FILTER or INDEX to select only those columns:

      =INDEX(VSTACK(FILTER(Sheet1!A:G, Sheet1!F:F <> ""), FILTER(Sheet2!A:G, Sheet2!F:F <> ""), FILTER(Sheet3!A:G, Sheet3!F:F <> "")), {1, 6, 7})
   

   Here, {1, 6, 7} refers to the first, sixth, and seventh columns from the stacked data, which correspond to A, F, and G respectively.

This approach allows you to create a summary table that meets your criteria without using a pivot table, focusing only on the relevant data from the specified columns.

---

References:

• Analyze and visualize data in Excel
• Excel performance: Tips for optimizing performance obstructions
• DAX overview
• Table samples
• Sample macro to insert/delete rows or columns on multiple sheets in Excel

Answer 3

This would work I think, except some tabs don't have any data in the F column yet. Is there another way to do this?

Answer 4

Hi,

Try this

=filter(let(c,choosecols(vstack('sheet1:sheet3'!$A$2:$G$100),1,6,7)),choosecols(c,2)="your criteria")

If it does not work then share the download link of the MS Excel file.

Hope this helps.