Advanced filter with 2 criteries

Tali_h · #1 07-02-2009, 02:49 PM

Hello,

I have Excel table with almost 5000 rows and 4 columns - customer name, number, date, and name of magazine. First, I need to find 06/18/2009 in date column, and if this customer has several rows with other dates delete them as well.

I tried vba - created sub to delete. But it deletes just rows with 06/18/2009 date. Tried advanced filter - it's filtering (I checked unique values), hope so. But then - I see just filtered data, that I want to delete.

If someone can help, I'll appreciate it

Thank you in advance.

Tali

kellyfspringer · #2 07-02-2009, 10:19 PM

Okay, this may be convaluted, but it will do the trick in a couple of minutes.

First of all, I use use the AutoFilter

1. Filter on 6/18/2009
2. Put a some sort of marker in an empty column (let's use 1 as the marker and say we are putting it in Column E), fill it down and remove the filter.
3. Sort all the data by Customer (or customer number) then by that row that you just created.
(this will sort the customers with the 6/18/2009 date first, then all other dates after, but it will group the customers together).
4. Then put this formula column in Column G:
=IF(F2=1,"",(IF(A2=A1,"X","")))
---This can be filled down all 5,000 lines.
5. Filter on X in column G. These are all the items that you want to delete.
Make sure to right click and delete, so you delete the entire row, rather than just clear the cells.

Here's a workbook for an example.

Tali_h · #3 07-03-2009, 10:07 AM

Thank you so much for your response. Tried to do what you suggested - confused a little bit. Filtered on 6/18/2009. Created marker column (put 1) for this date. Removed the filter.
Tried to sort ( the table is anyways sorted by customer name) - do I need to choose all the data in the table before filtering? But I think - you wanted to get it sorted by A->Z. It is.
Put the formula in additional column. Put filter on this column - but it gave mi all the dates, except of 6/18/2009.

Did I do something wrong?

Thanks again for the help

Tali

Tali_h · #4 07-03-2009, 10:43 AM

It is aldo put "X" for the customers with the same name, even if they don't have 06/18/2009 in the date column.

Thanks

kellyfspringer · #5 07-04-2009, 12:33 AM

The sort is by customer than by that marker, and yes, make sure that you have selected all the data. The marker keeps the 6/18 date at the top of the list of customers.
That way if Customer A has 3 dates, and one of them is 6/18, the 6/18 will be at the top of the list.
The formula that I made for the next column depends on this.
If you sort it correctly before you do the formula,
it should put an "X" only on lines where the date is not 6/18 AND the customer has another line with 6/18.

It shouldn't be putting an X EVERYTHING not listed as 6/18.

'Sorry if i'm not making sense!!
I updated my sheet, maybe this helps.

kellyfspringer · #6 07-04-2009, 12:43 AM

here's the attachment

Tali_h · #7 07-06-2009, 09:32 AM

Kelly, thank you so much for the explanation.
Right, it will not put "X" if customer doesn't have 6/18. If he has single row.
But it does put "X" for customers, that don't have 6/18, but have several rows

Thanks again

Welcome to the Excel Forum

Forum Statistics