Posts by Jetheat

Can you show a video of what you are doing?
I cant get it to work,

JH

yes, I am. It just doesn't filter the relevant entries. It only shows what I attached above.

Can you tell me how to make it work?

JH

Quote from royUK

Advanced Filter will work.

I have added * wildcards to your list of words, also changed the header row to match the actual header row of the main Table

From the Data Tab select Advanced from the Sort & Filter Tab and select the range of data and criteria

Thanks a lot. I tried your suggestions but it didn't work...

[ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1222961}[/ATTACH] [ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1222962}[/ATTACH]

Great, I have attached a workbook which has two sheets.

• Main List
• Filter List

I have made both lists as tables.

I would like to filter the main list if it contains any word from the rows within the filter list.

Any suggestions?

JH

I have a main list.
I want to filter this list with a bunch of words which I have listed on another sheet.
So I want the main sheet to only show rows which "contain" (not full exact match) any of the words from the other list.

How do I do that?

JH

Re: Insert Cell Contents into middle of Web Address

Great, thanks for all the help,

JH

Re: Insert Cell Contents into middle of Web Address

i just did this and it worked:

=HYPERLINK("http://www." & A1 &".blogspot.com")

I have the web address in the target cell that way.
Thanks

JH

Re: Insert Cell Contents into middle of Web Address

I can place the web address ina totally different cell but what address should I put?

the basic address would be http://www.blogspot.com but when the word is inserted in there, it will be http://www.cellA1.blogspot.com

JH

Re: Insert Cell Contents into middle of Web Address

Ok, but where does the web address go in that formula? There is no reference to that in there. Where do i enter http://www.CELLA1.blogspot.com

If I have the word potato in cell A1 and in cell A2, I have a web address, how do I insert the word from A1 into the middle of the web address in A2?

The Web address should end up like http://www.potato.blogspot.com

Any ideas?

JH

Re: Removing Empty Rows and selecting Specific Rows

Thanks Ray, all sorted,
JH

Re: Removing Empty Rows and selecting Specific Rows

I managed to do all that but now I have another problem.

Anyway, now I have a slightly different problem which hopefully you can help with.

I have 2 columns of email addresses. I have imported them from different sources.

I need to remove the duplicate entries so I placed one set under the other.

Then I tried the Filter ~ Advanced Filter trick.

It didn't remove any because I found that the 2nd set does not match the first set because it has a lot of trailing edge spaces after the email address.

What can I do to remove these trailing edge spaces and make them the same as the 1st set?

JH

Re: Removing Empty Rows and selecting Specific Rows

OK, that worked, but now all the empty rows have #VALUE! error in them.

Not only do I want that gone but I also want all the empty rows gone.
Any idea how I can get rid of all the empty and #VALUE! rows?

JH

Re: Removing Empty Rows and selecting Specific Rows

Thanks Iskao,

But how do I put a function for that cell?
Where and how do I put a function?

I have a list of email addresses in one single column.

This list is large so I need some way of shortcutting what I need to do.

This entire column of email addresses is separated by 2 empty rows between each email address.

1) Instead of clicking on each one and deleting them one by one, is there a quicker method to delete all empty rows?

2) The list of email addresses is imported.
All the emaill addresses have a space in front of them. For example " [email protected]" Do you see the extra space in front of the email address?

How do I remove that single space with some sort of shortcut for the entire column?

Appreciate the help,

JH

Thanks Seti, you're a Star!

Worked a treat.

I'll be back later you give you more headache,

Thanks again

The And portion equates to TRUE,

IF(E18="B",G18-F18,F18-G18) equates to 0

this is what it look like at one point:

=IF(TRUE,"",0)/IF(D18="JPY",1)

Looks wrong!

Ok, I figured out what it is but don't know how to correct it.

The full formula is

=IF(AND(E18<>"B",E18<>"S"),"",IF(E18="B",G18-F18,F18-G18))/IF(D18="JPY",100,1)

If the row is empty, the middle part of the formula [IF(E18="B",G18-F18,F18-G18)] = 0 and then the formula tries to use that 0 for division, so its trying to do 0/100 or 0/1 which is probably why its returning the error.

How do I chage the formula to correct this?

Thanks

Your assumption is correct.

/IF(D5="JPY",100,1)

This part is the problem.

It just says the formula contains errors.

This is the only part I added before which, it was working fine and the cells remained blank.