Search multiples and return unique value

**pleiadeez7** · 10-19-2011, 02:46 PM

In the attached spreadsheet I have a list of customers. A customer may have the same customer number and location code, but do business in more than one business unit (CCN).

What I am trying to determine is which customer files can be archived. If a company has done business in any CCN since 2006 the file stays.

For instance, in the example below, the file for company number 1234 will stay because they did business with us in 2010. The file for company number 4567 will go because no business has been done in any CCN since 2006.

Please Login or Register  to view this content.

How do I make the attached spreadsheet sort this out in a way that is helpful to the project?

**Leith Ross** · 10-19-2011, 04:38 PM

Hello pleiadeez7,

The easiest method is to simply filter the data. You can apply the autofilter to the header row. Filter on "Last Invc Year" by clicking the drop down button then choosing "(custom...)" in the filter drop down. Set the custom filter to is greater than 2005.

**pleiadeez7** · 10-21-2011, 09:37 AM

Thanks for the reply. Using the custom filter will show me that customer 1234-222 has not done business with us since 2004, but what it's not showing me is that customer 1234-333 "is" actively doing business in another business unit. What I need the data to tell me is which company numbers have done no business with any business unit (CCN) since 2006.