Comparing canceled orders in Google Sheets

Other Questions Google Sheets
1

I’m working on a Google Sheets project and I’m stuck. I need to create a formula that checks if an order is canceled and then displays it with the related material on another sheet. Here’s what I’m trying to do:

  1. Check if an order is canceled (columns A and B)
  2. If it is, show the order and its material on a different sheet (columns C and D)

I’ve been scratching my head over this for a while now. Has anyone done something similar before? Any tips or formulas that could help me out? I’m not sure where to start with this one.

Here’s a simple example of what my data looks like:

Order Status Material
001 Canceled Item X
002 Active Item Y
003 Canceled Item Z

Any help would be really appreciated!

2

Having dealt with similar scenarios, I can suggest using an ARRAYFORMULA combined with IF statements. This approach is efficient and updates automatically.

On your new sheet, in cell A2, input this formula:

=ARRAYFORMULA(IF(Sheet1!B2:B=“Canceled”, {Sheet1!A2:A, Sheet1!B2:B, Sheet1!C2:C}, “”))

This formula checks each row in Sheet1, and if the status is “Canceled”, it pulls the corresponding order, status, and material information. The empty quotes at the end ensure blank rows aren’t displayed for non-canceled orders.

Remember to adjust ‘Sheet1’ to match your actual sheet name. This method is particularly useful for larger datasets as it doesn’t require manual updating. Let me know if you need any clarification on implementing this solution.

3

I’ve actually tackled a similar challenge in my work with inventory management. Here’s an approach that might help:

You can use a combination of FILTER and QUERY functions to accomplish this. First, set up your new sheet with headers for Order, Status, and Material. Then, in the first cell below the headers, try this formula:

=QUERY(FILTER(‘Sheet1’!A:C, ‘Sheet1’!B:B=“Canceled”), “SELECT Col1, Col2, Col3”)

This assumes your original data is on ‘Sheet1’. The FILTER part selects only the rows where the status is “Canceled”, and the QUERY part ensures you’re pulling all the relevant columns.

One thing to watch out for: make sure your column headers in the original sheet don’t interfere with the filter. You might need to adjust the range if they do.

This should give you a dynamic list that updates automatically when orders are canceled. Let me know if you need any clarification on this approach!

4

hey miar, i’ve done smth similar before. try this in ur new sheet:

=filter(Sheet1!A:C, Sheet1!B:B=“Canceled”)

it’ll grab all the canceled orders + their info. just make sure ur original sheet is named ‘Sheet1’. hope this helps! lemme know if u need anything else