Deleting Rows of Data that does not match certain column's description
Hello everyone,
I am stuck with an issue where:
Ex:
Column A = account number
Column B = Pet Owner Type
Column A / Column B
232 NA
232 NA
232 CAT
232 NA
414 NA
414 NA
414 NA
414 NA
999 NA
999 NA
999 NA
999 NA
I want to delete all the accounts that does not own a CAT, so for instance, I would only have the following accounts remaining after filtering out.
Account # Pet Owner
232 NA
232 NA
232 CAT
232 NA
Helps is greatly appreciated, I've been stuck on this for a while. Thanks in advance!!
232 CAT owns a CAT, so why is/not deleted?
You have only one CAT on your data. Do you want to delete if a cell that does not have CAT, or has CAT?
With an helpcolumn.
Filter on column D on #N/A and delete that rows.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi,
I want to keep the account that has CAT, so, if account 232 had at least one CAT amongst the 4 x 232 accounts, all 232 accounts would be kept, whereas accounts like 414 and 999 which has no CAT at all, should all be deleted.
Unfortunately, I have a lot of columns and cannot afford to create a helpcolumn, is it possible to create an array that stores that data for each account and delete if the threshold is not satisfied?
Hello everyone,
I would like to delete all other accounts that does not have certain characteristics.
For instance
Account # Pet Owned
1 N/A
1 N/A
1 N/A
1 N/A
2 N/A
2 CAT
2 N/A
2 N/A
3 N/A
3 N/A
3 N/A
3 N/A
4 N/A
4 N/A
4 CAT
4 N/A
So, in this case, all other accounts (1 and 4) would be deleted, and since one of the account 2 has CAT owned, all of the account# 2 should remain like so:
Account # Pet Owned
2 N/A
2 CAT
2 N/A
2 N/A
4 N/A
4 N/A
4 CAT
4 N/A
I believe I need to use an array, if not I would greatly appreciate any help in going about this issue.
Last edited by exlgh91; 05-13-2013 at 01:59 PM.
Try This.
Let me know if this Is ok??
Thank You,
Deep
Cheers!
Deep Dave
Sorry but could you walk me through the code? I want to know what's being done here to apply to my actual file, I'd greatly appreciate it.
This is an duplicated post!
Please read the forumrules.
http://www.excelforum.com/excel-prog...scription.html
@exlgh91:
When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.
(link above in the menu bar)
===================================
Your duplicate post does not comply with Rule 5 of our forum rules. Your two threads have been merged.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Sorry everyone!
I was rushed near the deadlinesorry!!
@exlgh91
Here I am going through each cell in the used range in B Column. Where ever a Cell that is not N/A is found, the Value next to it in Column A is being stored in a Variable "i".
Then I apply a Autofilter and Filter only "I". I then copy the visible cells and Paste it to a different location.
Hope this helps..
Cheers!
Deep
Test.xlsHello everyone, it is still unsolved at this point.
In this file, I am trying to:
1. For the accounts with <10 tradevolume (column F) filtered, delete groups of tradeidentifier (column J) row without a Pro account (column G)
Thus, the result would be deleting everything else listed in the filter except the 4 rows with tradeidentifier (column J) 305948.
2. Filter back out, and add whats left over to the rest of the group with >10 tradevolume (column K)
Help is greatly appreciated, I will vouch someone who solves this till the end of time.
If you're in a hurry, why don't you use the already offered solutions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks