pull data from list

[abrook1308]

Hi everyone

I'm very much a beginner with excel and I'm in need of some help.

I'm currently using an excel sheet for work that pulls data from various other excel sheets and i wanted to create a bit of a summary sheet, i currently have quite a long list of locations and using x lookup its pulling what item code is in this location and if there's orders for this today as below:

I'm wanting it to show me all the "Empty" locations and all the "no order for today" locations, i know i can use filter but i want to see both lists side by side and will be adding some more in future too.

I've tried using =filter but cant seem to get it to work.

any help will be massively appreciated

[#2]

@abrook1308 

Do you work with Office 365 or Excel for the web. If so you can use HSTACK to return the FILTER results for all the "Empty" locations and all the "no order for today" locations side by side within one spilled range.

[#3]

both lists side by side？
Can you share a workbook file?

[#4]

Problem: You want to extract two specific groups of data from a larger dataset: locations with "Empty" items and locations with "No order for today" in the "Quantity" column.

Solution: We'll use the FILTER function to extract these two groups separately and then combine them into a single table.

Step-by-Step Guide
1. Prepare Your Data:

Ensure your data is in a table format. This makes it easier to work with and reference ranges. You can convert your range to a table by selecting the data and pressing Ctrl+T.
Name your table for easier reference. Let's call it "Data".
2. Create Separate Filtered Lists:

Create two new sheets or areas in your existing sheet to hold the filtered data.
In the first area, use the FILTER function to extract locations with "Empty" items:
Excel
=FILTER(Data,Data[Items]="Empty")
Use code with caution.

In the second area, use the FILTER function to extract locations with "No order for today" in the "Quantity" column:
Excel
=FILTER(Data,Data[Quantity]="No order for today")
Use code with caution.

3. Combine the Filtered Lists:

There are a few ways to combine these lists:
Concatenate with Rows: Use the CONCAT function to combine the two filtered ranges vertically.
Create a Pivot Table: Create a Pivot Table based on the two filtered ranges to summarize the data.
Manual Combination: If you prefer manual control, you can copy and paste the results from the two filtered lists into a new sheet and arrange them as needed.
Example Formulas
Assuming your data is in a table named "Data" with columns "Location", "Items", and "Quantity":

Sheet 1:

Cell A1: =FILTER(Data,Data[Items]="Empty")
Cell A10: =FILTER(Data,Data[Quantity]="No order for today")
Sheet 2: (Using CONCAT to combine)

Cell A1: =CONCAT(Sheet1!A1:C10,Sheet1!A11:C20)
This will create a single list with all "Empty" and "No order for today" locations.

Additional Tips
Error Handling: Use IFERROR to handle cases where no matches are found in the FILTER function.
Sorting: Sort the combined list by location or other criteria as needed.
Formatting: Apply formatting to the combined list to improve readability.
By following these steps, you should be able to effectively extract and combine the desired data from your Excel sheet.