• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I need a formula to recognise if a word is in a cell containting a para, then copy row to sheet 2

SLS100

New Member
I'm new here (and to any forum) so apologies in advance if I am not clear or my title isn't correct! I am desperate for help.

I have Sheet 1 with paragraphs of text (I work for lawyers!), if cells in column F contain the word 'payment' I need that row from A1:F1 to be copied to Sheet 2.

The cell in F will contain various words such as payment, contract .... etc.

Any ideas? I have tried the =IF(ISNUMBER(SEARCH("payment",F5)),Sheet!2B2,"")

Which is along the correct lines but I need it to copy the full row not just one cell....
 
One possible...
1) write to Sheet2 Cell[F2] =IF(Sheet1!F2="payment";Sheet1!F2;"")
2) write to Sheet2 Cell[A2] =IF($F2<>"";Sheet1!A2;"")
3) copy previous formula also to cells B2 to E2
4) if You need more rows then copy those formulas as need
Note! If You need to use ',' instead of ';' then modify those.
 
One possible...
1) write to Sheet2 Cell[F2] =IF(Sheet1!F2="payment";Sheet1!F2;"")
2) write to Sheet2 Cell[A2] =IF($F2<>"";Sheet1!A2;"")
3) copy previous formula also to cells B2 to E2
4) if You need more rows then copy those formulas as need
Note! If You need to use ',' instead of ';' then modify those.
Thanks however, this doesn't appear to work. Also Cell F2 will have a paragraph of text in that one cell and I need it to only copy the row if it contains the word 'payment' ..

This formula just keeps telling me that the formula is incorrect...
 
Point ONE:
always Upload a Sample File to get a quicker response
then no need to try to guess Your hidden visions
as You know (You work for lawyers).
We need facts ...
if You are really desperate.
btw Your formula is correct ... but where it could use ...?
 
Apologies, I realised you need facts, I tried explaining without the example file only because, working for lawyers, I can't send a copy of what it is that I am working on and to create a 'sample' of the same will take me a moment and I was travelling from Munich at the time of writing.

Thank you.
 
Ok, back in London and I have done a little mock up (attached). I need the formula to look at row F and wherever it sees the word 'payment' for example, I need that whole row copying to Sheet2. The word payment will sometimes be alongside other words. It then has to do the same for every row on sheet 1....

Thank you in advance if anyone can help! It will make them very happy.
 

Attachments

  • TestFile.xlsx
    17 KB · Views: 4
... London gives another case ...
Press [payment]-button and ...
is this something that You ...
 

Attachments

  • TestFile.xlsb
    22 KB · Views: 8
Exactly!!!! I do have an issue with macros, we are completely locked down on them! but I can deal with his. Thank you so much.

How can I achieve this myself?
 
Two possibilities:
1) Copy from Your original file that sheet's data, NOT rows 1-4,
(which is just like Your sample sheet)
to that TestFile.xlsb Sheet1 to cell[A5].
Of course, Macros have to be enabled!
2) Copy that Macro to Your file,
modify Sheet names and
save it as *.xlsb-format.
Data Layout have to be same!
 
Thank you. The problem I have is that I can't see the macro to edit when I select Macros. I have changed all the security settings enabling macros but still cannot see it. Attached show what I mean ..... normally I can see macros I create myself.
 

Attachments

  • Macro1.jpg
    Macro1.jpg
    48.6 KB · Views: 2
  • Macro2.jpg
    Macro2.jpg
    70.2 KB · Views: 4
Ouch!
Move Your mouse over that button and activate it with RIGHT click
as below ...
Screen Shot 2017-01-12 at 12.16.11.png
Select 'Assign Macro...'
Next press [Edit]-button ...
Okay?
 
Back
Top