[skytech]

Hi,

I have a worksheet that contains 2 columns of data. I have listed a sample of the data as below column A and column B

...............Column A....................................Column B
Row 1......blacktalldog.com..........................black tall dog
Row 2......blackhungrymouse.com................black hungry mouse
Row 3......thebigshortdog.net........................the big short dog
Row 4......bigblacktable.net..........................big black table
Row 5......bigtalltree.net...............................big tall tree

I have some questions on how to do the things I want.

1) I want to excel to search the whole document and detect rows with cells that start with the word 'black' and cells that start with the word 'big'. Delete the whole rows.

results to return will be
Row 3......thebigshortdog.net........................the big short dog

2) I want to search Column A only, delete the rows if the cells in Column A ends with '.net'

results to return will be
Row 1......blacktalldog.com..........................black tall dog
Row 2......blackhungrymouse.com................black hungry mouse

3) I want to detect cells that contain the word tall. If the word tall is detected in Column A or Column B, delete the row.

results to return will be
Row 2......blackhungrymouse.com................black hungry mouse
Row 3......thebigshortdog.net........................the big short dog
Row 4......bigblacktable.net..........................big black table


Thanks

[etaf]

will this be something you want to be able to do over again or a one off exercise ?

if once only - you could put a flag into column C and then sort by column c and delete

this ishow I would do it - BUT there maybe a much simpler way
so in C I would use something like OR to test cell A and B
OR(mid(a2,1,5)="black",mid(a2,1,3="big", mid(b2,1,5)="black",mid(b2,1,3)="big)
That will test for the first condition
you can use the same idea for the .net and an IF around the lot to set the flag

If this is a oneoff upload a small sample excel spreadsheet and I can code for you - if a regular requirement then I suspect VBA would be better

[skytech]

Thanks for the reply

I need to do this over again many times. Is there other solutions?

Although I need to do this over again, I am wonder how do I apply what you have written

"this ishow I would do it - BUT there maybe a much simpler way
so in C I would use something like OR to test cell A and B
OR(mid(a2,1,5)="black",mid(a2,1,3="big", mid(b2,1,5)="black",mid(b2,1,3)="big)
That will test for the first condition
you can use the same idea for the .net and an IF around the lot to set the flag"

[etaf]

so breaking my idea down bit by bit - its not the best way to do this i'm sure one of the gurus will provide a simpler method
anyway
NOTE - This is all case sensitive as well - so would need to add something like upper to account for case too
for your 1st condition
=IF(OR(MID(A2,1,5)="black",MID(B2,1,5)="black",MID(A2,1,3)="big",MID(B2,1,3 )="big"),1,0)
for your 2nd condition
=IF((MID(A2,LEN(A2)-3,4)=".net"),1,0)
for your 3rd condition
=IF(ISERROR(FIND("tall",A2,1)),IF(ISERROR(FIND("tall",B2,1)),0,1),1)
and then to do all conditions in one go
=IF(OR(MID(A2,1,5)="black",MID(B2,1,5)="black",MID(A2,1,3)="big",MID(B2,1,3 )="big",MID(A2,LEN(A2)-3,4)=".net"),1,IF(ISERROR(FIND("tall",A2,1)),IF(ISERROR(FIND("tall",B2,1)), 0,1),1))

I have attached a sample spreadsheet

[skytech]

Thanks for the help, I appreciate it.

Ok. I get what you mean, from your sample spreadsheet, I will need to manually spot those rows that contains the 1 and delete it. This is suitable for one time use and small sample. My worksheet is large and I need to do this frequently. I think there will be other methods that is simpler.

[etaf]

yep, i agree - you can sort the spreadsheet by the 1's and it will be easy to then delete/spot the rows

to help a VBA expert - do you need to have the rows deleted seperately depending on the conditions
IE
Only delete if black and big appears
then a separate condition to only delete the .net
then a separate condition to delete the tall

OR

do you apply for all the conditions and delete everything
so black, big, tall, .net all the rows deleted as per your conditions that apply

[Keebellah]

I would suggest VBA code (macro's) and based upon a list in a separate tab (sheet) you could easily make it dynamic, the formula's as now used are very static and new values require quite a lot of changes.
If Skytech answers Etaf's last question then we could work on it and attach a sample to see if this would be what is asked for.

[skytech]

I can apply for all conditions and deleted it at once, but I need to access the code to make little changes by myself in future, for example, maybe next time I want to detect the word blue, small, or other words.

I have redo a newsample. I have attached the sample.

newsample
...............Column A....................................Column B
Row 1......blacktalldog.com..........................black tall dog
Row 2......blackhungrymouse.com..................black hungry mouse
Row 3......thebigshortdog.net.......................the big short dog
Row 4......bigblacktable.net..........................big black table
Row 5......bigtalltree.net..............................big tall tree
Row 6......myrowsixbigcats.com.....................my row six big cats
Row 7......bluetallhouseseven.net...................blue tall house seven
Row 8......eighttalltables.com........................eight tall tables
Row 9......blackninecats.com.........................black nine cats
Row 10.....tenbigdogs.org.............................ten big dogs
Row 11.....elevengooddays.com.....................eleven good days
Row 12.....twelvesleepydays.org....................twelve sleepy days
Row 13.....thirteennicetables.com..................thirteen nice tables
Row 14.....fourteendayssmallcats.com............fourteen days small cats
Row 15.....fifteendaysblackcats.com..............fifteen days black cats

I have simplified my questions criteria as below,

1) search the whole document and detect rows with cells that start with the word 'black' and cells that start with the word 'big'. Delete the whole row.

2) I want to search Column A, delete the rows if the cells in Column A ends with '.net'

3) I want to detect cells that contain the word tall. If the word tall is detected anywhere in Column A or Column B, delete the row.

4) I want to detect cells that end with 'days' and delete the rows. (in this case, I think Column B is necessary? or is there a way to do it with just column A?)


My end result should be
Row 6.......myrowsixbigcats.com...............my row six big cats
Row 10......tenbigdogs.org.......................ten big dogs
Row 13......thirteennicetables.com............thirteen nice tables
Row 14......fourteendayssmallcats.com.......fourteen days small cats
Row 15......fifteendaysblackcats.com.........fifteen days black cats

[slurpee55]

VBA gurus, perhaps a message box popping up that would then allow for the user to input what is to be looked for (and then deleted)?

[Keebellah]

I think this is a good idea, you can always have alist availiable as default and if necessary have prompt for a value.

[Keebellah]

I have attached the sample Etaf had used with some extra info.

Try and see if this is another option

[skytech]

Yes, if someone can make a message box popping or something for me to input the words that I want to filter out, that would be great. I got a minor change, instead of deleting the rows, if the criteria for the words are found, just fill the rows with grey and arrange all the grey rows to the bottom of the data (so I will see the data I want on top, and bottom of the worksheet are the greyed rows are the data that I do not want)

Keebellah, thanks for the extra info, but that is not exactly I want. It is good for a small sample, but in my real data, my worksheet have thousands of rows and I need to filter out many words. Other that filtering out words that start with 'black', 'big', I still have many other words that I want to filter out.

[slurpee55]

what about if the text begins with, say, "big" but it isn't really the word "big" like in "bigtalltree.net" but is "bigotagainstcats.net"? A LEFT() search will pick both out as items to be greyed out and moved.
Unless this is a sample of, like, chemical formulas, that you want to clean up, you will have this problem. And if it is something like that, tell us.

[skytech]

Originally Posted by slurpee55

what about if the text begins with, say, "big" but it isn't really the word "big" like in "bigtalltree.net" but is "bigotagainstcats.net"? A LEFT() search will pick both out as items to be greyed out and moved.
Unless this is a sample of, like, chemical formulas, that you want to clean up, you will have this problem. And if it is something like that, tell us.

Yes, as long as the text begins with the word 'big' is detected, all the items will be that start with the word 'big' will be greyed. I want to detect all. So in your example, "bigtalltree.net" and "bigotagainstcats.net" will be greyed out.

In my sample you see Row 4 bigblacktable.net and Row 5 bigtalltree.net are the rows that begins with the word big, so both rows should be detected and greyed out.

[Keebellah]

I don't know what you will need it to do but the sample I sent you makes it 'elastic'
I'll edit it and send you a new sample to prove my point.
A prompt could be handy but entering your data in the first row and a formula that checks it and greys it our would be handy I think.

Will the list only contain two columns or is this a part of a more extended worksheet?
Contrary to .net will you search only on tekst strings at the begening?

What if mybigtalldog: no big no tall but doie it have to be greyed out?