Delete rows based on multiple criterias
 

Hi there,

I have a task i have to complete weekly which is really starting to bore me!!

Basically, i receive a report in excel detailing the SMS messages everybody
in the company has sent. I.e. i have a date, senders email, destination,
first name, last name, blah blah blah and the actual message sent. Now im
only interested in the messages sent by my company and not out sister company.

What i have been doing is auto filtering on the Senders email column for the
lines im not interested in and manually delete them, thus leaving me with
just the ones i want to analyse. My question is, does anybody have an idea
how to automaticaaly do this? When the rows i DONT want are identified i
could do with the entire row being deleted, not just the email address cell.

Thanks in advanced, any further info required please let me know!

Delete rows based on multiple criterias
 

I'd keep a (growing) list of email addresses to delete. Then when you get
the weekly log, Data Filter Advanced Filter. The list range would be
your new log and the criteria would be your running list. The filter will
leave only the rows to delete visibile, so you can just select them all and
delete. As you go through the ones that remain, you'll find new addresses to
add to your 'delete list'.
HTH. --Bruce

"Benson" wrote:

Hi there,

I have a task i have to complete weekly which is really starting to bore me!!

Basically, i receive a report in excel detailing the SMS messages everybody
in the company has sent. I.e. i have a date, senders email, destination,
first name, last name, blah blah blah and the actual message sent. Now im
only interested in the messages sent by my company and not out sister company.

What i have been doing is auto filtering on the Senders email column for the
lines im not interested in and manually delete them, thus leaving me with
just the ones i want to analyse. My question is, does anybody have an idea
how to automaticaaly do this? When the rows i DONT want are identified i
could do with the entire row being deleted, not just the email address cell.

Thanks in advanced, any further info required please let me know!

Delete rows based on multiple criterias
 

Hi there,

Thanks for the quick response....but i dont understand :-( Where would i
keep the list of email addresses to delete? I have tried a custom filter on
auto filter but it only allows you to specify 3 addresses to filter out!

Can you explain in a little more detail please? Thanks again!

"bpeltzer" wrote:

I'd keep a (growing) list of email addresses to delete. Then when you get
the weekly log, Data Filter Advanced Filter. The list range would be
your new log and the criteria would be your running list. The filter will
leave only the rows to delete visibile, so you can just select them all and
delete. As you go through the ones that remain, you'll find new addresses to
add to your 'delete list'.
HTH. --Bruce

"Benson" wrote:

Hi there,

I have a task i have to complete weekly which is really starting to bore me!!

Basically, i receive a report in excel detailing the SMS messages everybody
in the company has sent. I.e. i have a date, senders email, destination,
first name, last name, blah blah blah and the actual message sent. Now im
only interested in the messages sent by my company and not out sister company.

What i have been doing is auto filtering on the Senders email column for the
lines im not interested in and manually delete them, thus leaving me with
just the ones i want to analyse. My question is, does anybody have an idea
how to automaticaaly do this? When the rows i DONT want are identified i
could do with the entire row being deleted, not just the email address cell.

Thanks in advanced, any further info required please let me know!

Delete rows based on multiple criterias
 

Keep the 'delete table' separate (probably in a separate workbook). And it's
not a customer filter but an Advanced Filter. For that, you have to specify
the criteria range, which would be this separate table. The key to make that
work is that the column headers in your criteria table must match the column
headers in your log.
Ex: if your log has columns including 'Date' 'Sender' 'Text, ... and you
only want to filter out based on 'Sender', your 'delete table' would have one
column with the header 'Sender', followed by all the senders you want to
delete.
You'd highlight your new weekly log, then go to the menu: Data Filter
Advanced Filter. Excel will default to the highlighted range as the List
Range. This is correct, so click in the Criteria Range text box, click the
spreadsheet icon and select your 'delete table' (including the header!).
Click OK and only the entries matching your delete list will remain visible.
Delete those then turn off the filter.

"Benson" wrote:

Hi there,

Thanks for the quick response....but i dont understand :-( Where would i
keep the list of email addresses to delete? I have tried a custom filter on
auto filter but it only allows you to specify 3 addresses to filter out!

Can you explain in a little more detail please? Thanks again!

"bpeltzer" wrote:

I'd keep a (growing) list of email addresses to delete. Then when you get
the weekly log, Data Filter Advanced Filter. The list range would be
your new log and the criteria would be your running list. The filter will
leave only the rows to delete visibile, so you can just select them all and
delete. As you go through the ones that remain, you'll find new addresses to
add to your 'delete list'.
HTH. --Bruce

"Benson" wrote:

Hi there,

I have a task i have to complete weekly which is really starting to bore me!!

Basically, i receive a report in excel detailing the SMS messages everybody
in the company has sent. I.e. i have a date, senders email, destination,
first name, last name, blah blah blah and the actual message sent. Now im
only interested in the messages sent by my company and not out sister company.

What i have been doing is auto filtering on the Senders email column for the
lines im not interested in and manually delete them, thus leaving me with
just the ones i want to analyse. My question is, does anybody have an idea
how to automaticaaly do this? When the rows i DONT want are identified i
could do with the entire row being deleted, not just the email address cell.

Thanks in advanced, any further info required please let me know!

Delete rows based on multiple criterias
 

That works a treat :-)

Thanks very much for your help....one thing i have changed from what you
suggested though. Instead of doing a list of not allowed email addresses i
have created one for "allowed addresses". This then leaves a list of the SMS
messages i want so i can Ctrl+* then Alt+; to select the values "on top" then
copy them out!

"bpeltzer" wrote:

Keep the 'delete table' separate (probably in a separate workbook). And it's
not a customer filter but an Advanced Filter. For that, you have to specify
the criteria range, which would be this separate table. The key to make that
work is that the column headers in your criteria table must match the column
headers in your log.
Ex: if your log has columns including 'Date' 'Sender' 'Text, ... and you
only want to filter out based on 'Sender', your 'delete table' would have one
column with the header 'Sender', followed by all the senders you want to
delete.
You'd highlight your new weekly log, then go to the menu: Data Filter
Advanced Filter. Excel will default to the highlighted range as the List
Range. This is correct, so click in the Criteria Range text box, click the
spreadsheet icon and select your 'delete table' (including the header!).
Click OK and only the entries matching your delete list will remain visible.
Delete those then turn off the filter.

"Benson" wrote:

Hi there,

Thanks for the quick response....but i dont understand :-( Where would i
keep the list of email addresses to delete? I have tried a custom filter on
auto filter but it only allows you to specify 3 addresses to filter out!

Can you explain in a little more detail please? Thanks again!

"bpeltzer" wrote:

I'd keep a (growing) list of email addresses to delete. Then when you get
the weekly log, Data Filter Advanced Filter. The list range would be
your new log and the criteria would be your running list. The filter will
leave only the rows to delete visibile, so you can just select them all and
delete. As you go through the ones that remain, you'll find new addresses to
add to your 'delete list'.
HTH. --Bruce

"Benson" wrote:

Hi there,

I have a task i have to complete weekly which is really starting to bore me!!

Basically, i receive a report in excel detailing the SMS messages everybody
in the company has sent. I.e. i have a date, senders email, destination,
first name, last name, blah blah blah and the actual message sent. Now im
only interested in the messages sent by my company and not out sister company.

What i have been doing is auto filtering on the Senders email column for the
lines im not interested in and manually delete them, thus leaving me with
just the ones i want to analyse. My question is, does anybody have an idea
how to automaticaaly do this? When the rows i DONT want are identified i
could do with the entire row being deleted, not just the email address cell.

Thanks in advanced, any further info required please let me know!

Delete rows based on multiple criterias
 

Happy to help. Just recognize that if your list is 'allowed addresses', it
will be on you to know when you have to add to the list (you'll never see the
new addresses because they'll get filtered out before your copy operation).
--Bruce

"Benson" wrote:

That works a treat :-)

Thanks very much for your help....one thing i have changed from what you
suggested though. Instead of doing a list of not allowed email addresses i
have created one for "allowed addresses". This then leaves a list of the SMS
messages i want so i can Ctrl+* then Alt+; to select the values "on top" then
copy them out!

"bpeltzer" wrote:

Keep the 'delete table' separate (probably in a separate workbook). And it's
not a customer filter but an Advanced Filter. For that, you have to specify
the criteria range, which would be this separate table. The key to make that
work is that the column headers in your criteria table must match the column
headers in your log.
Ex: if your log has columns including 'Date' 'Sender' 'Text, ... and you
only want to filter out based on 'Sender', your 'delete table' would have one
column with the header 'Sender', followed by all the senders you want to
delete.
You'd highlight your new weekly log, then go to the menu: Data Filter
Advanced Filter. Excel will default to the highlighted range as the List
Range. This is correct, so click in the Criteria Range text box, click the
spreadsheet icon and select your 'delete table' (including the header!).
Click OK and only the entries matching your delete list will remain visible.
Delete those then turn off the filter.

"Benson" wrote:

Hi there,

Thanks for the quick response....but i dont understand :-( Where would i
keep the list of email addresses to delete? I have tried a custom filter on
auto filter but it only allows you to specify 3 addresses to filter out!

Can you explain in a little more detail please? Thanks again!

"bpeltzer" wrote:

I'd keep a (growing) list of email addresses to delete. Then when you get
the weekly log, Data Filter Advanced Filter. The list range would be
your new log and the criteria would be your running list. The filter will
leave only the rows to delete visibile, so you can just select them all and
delete. As you go through the ones that remain, you'll find new addresses to
add to your 'delete list'.
HTH. --Bruce

"Benson" wrote:

Hi there,

I have a task i have to complete weekly which is really starting to bore me!!

Basically, i receive a report in excel detailing the SMS messages everybody
in the company has sent. I.e. i have a date, senders email, destination,
first name, last name, blah blah blah and the actual message sent. Now im
only interested in the messages sent by my company and not out sister company.

What i have been doing is auto filtering on the Senders email column for the
lines im not interested in and manually delete them, thus leaving me with
just the ones i want to analyse. My question is, does anybody have an idea
how to automaticaaly do this? When the rows i DONT want are identified i
could do with the entire row being deleted, not just the email address cell.

Thanks in advanced, any further info required please let me know!

Delete rows based on multiple criterias
 

Good point...but im pretty confident that there will be no new addresses.

One more question...can i have 2 advanced filters running in parallel?

Basically ive done what we have discussed...the next thing i need to do is
only show unique rows (i.e. Data Filter Advanced filter unique records
only). But when i do the unique records filter, it cancells the previous
filter i have done.

Any ideas?? Cheers.

"bpeltzer" wrote:

Happy to help. Just recognize that if your list is 'allowed addresses', it
will be on you to know when you have to add to the list (you'll never see the
new addresses because they'll get filtered out before your copy operation).
--Bruce

"Benson" wrote:

That works a treat :-)

Thanks very much for your help....one thing i have changed from what you
suggested though. Instead of doing a list of not allowed email addresses i
have created one for "allowed addresses". This then leaves a list of the SMS
messages i want so i can Ctrl+* then Alt+; to select the values "on top" then
copy them out!

"bpeltzer" wrote:

Keep the 'delete table' separate (probably in a separate workbook). And it's
not a customer filter but an Advanced Filter. For that, you have to specify
the criteria range, which would be this separate table. The key to make that
work is that the column headers in your criteria table must match the column
headers in your log.
Ex: if your log has columns including 'Date' 'Sender' 'Text, ... and you
only want to filter out based on 'Sender', your 'delete table' would have one
column with the header 'Sender', followed by all the senders you want to
delete.
You'd highlight your new weekly log, then go to the menu: Data Filter
Advanced Filter. Excel will default to the highlighted range as the List
Range. This is correct, so click in the Criteria Range text box, click the
spreadsheet icon and select your 'delete table' (including the header!).
Click OK and only the entries matching your delete list will remain visible.
Delete those then turn off the filter.

"Benson" wrote:

Hi there,

Thanks for the quick response....but i dont understand :-( Where would i
keep the list of email addresses to delete? I have tried a custom filter on
auto filter but it only allows you to specify 3 addresses to filter out!

Can you explain in a little more detail please? Thanks again!

"bpeltzer" wrote:

I'd keep a (growing) list of email addresses to delete. Then when you get
the weekly log, Data Filter Advanced Filter. The list range would be
your new log and the criteria would be your running list. The filter will
leave only the rows to delete visibile, so you can just select them all and
delete. As you go through the ones that remain, you'll find new addresses to
add to your 'delete list'.
HTH. --Bruce

"Benson" wrote:

Hi there,

I have a task i have to complete weekly which is really starting to bore me!!

Basically, i receive a report in excel detailing the SMS messages everybody
in the company has sent. I.e. i have a date, senders email, destination,
first name, last name, blah blah blah and the actual message sent. Now im
only interested in the messages sent by my company and not out sister company.

What i have been doing is auto filtering on the Senders email column for the
lines im not interested in and manually delete them, thus leaving me with
just the ones i want to analyse. My question is, does anybody have an idea
how to automaticaaly do this? When the rows i DONT want are identified i
could do with the entire row being deleted, not just the email address cell.

Thanks in advanced, any further info required please let me know!

Delete rows based on multiple criterias
 

I'd just apply another filter after the copy operation. You'll also have to
decide what's a unique record for your purposes... Is it a different sender,
or different text?

"Benson" wrote:

Good point...but im pretty confident that there will be no new addresses.

One more question...can i have 2 advanced filters running in parallel?

Basically ive done what we have discussed...the next thing i need to do is
only show unique rows (i.e. Data Filter Advanced filter unique records
only). But when i do the unique records filter, it cancells the previous
filter i have done.

Any ideas?? Cheers.

"bpeltzer" wrote:

Happy to help. Just recognize that if your list is 'allowed addresses', it
will be on you to know when you have to add to the list (you'll never see the
new addresses because they'll get filtered out before your copy operation).
--Bruce

"Benson" wrote:

That works a treat :-)

Thanks very much for your help....one thing i have changed from what you
suggested though. Instead of doing a list of not allowed email addresses i
have created one for "allowed addresses". This then leaves a list of the SMS
messages i want so i can Ctrl+* then Alt+; to select the values "on top" then
copy them out!

"bpeltzer" wrote:

Keep the 'delete table' separate (probably in a separate workbook). And it's
not a customer filter but an Advanced Filter. For that, you have to specify
the criteria range, which would be this separate table. The key to make that
work is that the column headers in your criteria table must match the column
headers in your log.
Ex: if your log has columns including 'Date' 'Sender' 'Text, ... and you
only want to filter out based on 'Sender', your 'delete table' would have one
column with the header 'Sender', followed by all the senders you want to
delete.
You'd highlight your new weekly log, then go to the menu: Data Filter
Advanced Filter. Excel will default to the highlighted range as the List
Range. This is correct, so click in the Criteria Range text box, click the
spreadsheet icon and select your 'delete table' (including the header!).
Click OK and only the entries matching your delete list will remain visible.
Delete those then turn off the filter.

"Benson" wrote:

Hi there,

Thanks for the quick response....but i dont understand :-( Where would i
keep the list of email addresses to delete? I have tried a custom filter on
auto filter but it only allows you to specify 3 addresses to filter out!

Can you explain in a little more detail please? Thanks again!

"bpeltzer" wrote:

I'd keep a (growing) list of email addresses to delete. Then when you get
the weekly log, Data Filter Advanced Filter. The list range would be
your new log and the criteria would be your running list. The filter will
leave only the rows to delete visibile, so you can just select them all and
delete. As you go through the ones that remain, you'll find new addresses to
add to your 'delete list'.
HTH. --Bruce

"Benson" wrote:

Hi there,

I have a task i have to complete weekly which is really starting to bore me!!

Basically, i receive a report in excel detailing the SMS messages everybody
in the company has sent. I.e. i have a date, senders email, destination,
first name, last name, blah blah blah and the actual message sent. Now im
only interested in the messages sent by my company and not out sister company.

What i have been doing is auto filtering on the Senders email column for the
lines im not interested in and manually delete them, thus leaving me with
just the ones i want to analyse. My question is, does anybody have an idea
how to automaticaaly do this? When the rows i DONT want are identified i
could do with the entire row being deleted, not just the email address cell.

Thanks in advanced, any further info required please let me know!

Delete rows based on multiple criterias
 

Different text needs to be unique. So basically the two criterias a 1.
Allowed senders only and 2. Unique text messages.

When i advanced filter out the people i dont want i then need to advanced
filter out the unique texts. But when i apply the second filter it removes
the first one so i end up with all my unique texts but showing all senders!

Thanks!

"bpeltzer" wrote:

I'd just apply another filter after the copy operation. You'll also have to
decide what's a unique record for your purposes... Is it a different sender,
or different text?

"Benson" wrote:

Good point...but im pretty confident that there will be no new addresses.

One more question...can i have 2 advanced filters running in parallel?

Basically ive done what we have discussed...the next thing i need to do is
only show unique rows (i.e. Data Filter Advanced filter unique records
only). But when i do the unique records filter, it cancells the previous
filter i have done.

Any ideas?? Cheers.

"bpeltzer" wrote:

Happy to help. Just recognize that if your list is 'allowed addresses', it
will be on you to know when you have to add to the list (you'll never see the
new addresses because they'll get filtered out before your copy operation).
--Bruce

"Benson" wrote:

That works a treat :-)

Thanks very much for your help....one thing i have changed from what you
suggested though. Instead of doing a list of not allowed email addresses i
have created one for "allowed addresses". This then leaves a list of the SMS
messages i want so i can Ctrl+* then Alt+; to select the values "on top" then
copy them out!

"bpeltzer" wrote:

Keep the 'delete table' separate (probably in a separate workbook). And it's
not a customer filter but an Advanced Filter. For that, you have to specify
the criteria range, which would be this separate table. The key to make that
work is that the column headers in your criteria table must match the column
headers in your log.
Ex: if your log has columns including 'Date' 'Sender' 'Text, ... and you
only want to filter out based on 'Sender', your 'delete table' would have one
column with the header 'Sender', followed by all the senders you want to
delete.
You'd highlight your new weekly log, then go to the menu: Data Filter
Advanced Filter. Excel will default to the highlighted range as the List
Range. This is correct, so click in the Criteria Range text box, click the
spreadsheet icon and select your 'delete table' (including the header!).
Click OK and only the entries matching your delete list will remain visible.
Delete those then turn off the filter.

"Benson" wrote:

Hi there,

Thanks for the quick response....but i dont understand :-( Where would i
keep the list of email addresses to delete? I have tried a custom filter on
auto filter but it only allows you to specify 3 addresses to filter out!

Can you explain in a little more detail please? Thanks again!

"bpeltzer" wrote:

I'd keep a (growing) list of email addresses to delete. Then when you get
the weekly log, Data Filter Advanced Filter. The list range would be
your new log and the criteria would be your running list. The filter will
leave only the rows to delete visibile, so you can just select them all and
delete. As you go through the ones that remain, you'll find new addresses to
add to your 'delete list'.
HTH. --Bruce

"Benson" wrote:

Hi there,

I have a task i have to complete weekly which is really starting to bore me!!

Basically, i receive a report in excel detailing the SMS messages everybody
in the company has sent. I.e. i have a date, senders email, destination,
first name, last name, blah blah blah and the actual message sent. Now im
only interested in the messages sent by my company and not out sister company.

What i have been doing is auto filtering on the Senders email column for the
lines im not interested in and manually delete them, thus leaving me with
just the ones i want to analyse. My question is, does anybody have an idea
how to automaticaaly do this? When the rows i DONT want are identified i
could do with the entire row being deleted, not just the email address cell.

Thanks in advanced, any further info required please let me know!