List all deleted packages on separate tab

New Contributor

I have a workbook with multiple sheets with package information.  In column A is the package name.  In column D is the status.  I'm trying to compile a list on a new tab just to pull every package name that is marked deleted.



3 Replies



I can't reconcile your image with your description. Column D would (I should think) be the fourth column, yet that certainly doesn't describe status. Nor does any other column in the image.


That said, unless you can come back with a more complete description, let me refer you to the FILTER function, assuming you have the most recent version of Excel. It will do what you're seeking to have done.


Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...



Thank you for your response.  Sorry for the confusion.  We are utilizing column D to list the work type but also if the package is deleted.





In general, that's not a good idea--to mix one kind of information in with another. In data processing there's an important concept called Data Integrity. Preserving data integrity is often compromised -- sometimes with disastrous results -- by this kind of casual practice.


When it's first done, it seems so easy and inconsequential...."Oh, since it's deleted we don't care any longer what kind of package it is, so let's just note 'Deleted' here under Package Type."


But (hypothetically) let's say that two years from now management asks "Is there a pattern on which types of package get deleted more often?"   THAT'S when you realize you've actually destroyed some important information because you no longer can answer that question. The only answer from your database would be "The packages that get deleted are the packages that get deleted. We don't have any record to tell us how many 'Mechanical' or 'Hot Work' packages are deleted. Sorry."


Solution: Add another column that really is specifically to record order status (or whatever heading is most appropriate). If there only are two status levels, it could be very simple. But I can conceive of more possible status points: Pending, Wrapped, Labeled, Shipped, Returned, Deleted....


Design is important.


FILTER would still be my recommendation. But only after you've fixed your database design.


=FILTER(Datatable,Status="Deleted","none found")  with the appropriate references in for the words "Datatable" and "Status"  


I already gave you a YouTube video that explains FILTER.

Here's a website, if you prefer reading as a learning style.


I'd need to have access to your actual spreadsheet (not just an image) to write out the exact formula with the actual references in place. If you can post it on SharePoint or OneDrive or GoogleSheets, then I (or others here on this site) could take a look at it and give you more tailored answer.