1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by SLS100, Jan 10, 2017.

  1. SLS100

    SLS100 New Member

    Messages:
    7
    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....
  2. vletm

    vletm Well-Known Member

    Messages:
    2,263
    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.
  3. SLS100

    SLS100 New Member

    Messages:
    7
    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...
  4. vletm

    vletm Well-Known Member

    Messages:
    2,263
    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 ...?
  5. SLS100

    SLS100 New Member

    Messages:
    7
    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.
  6. SLS100

    SLS100 New Member

    Messages:
    7
    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.

    Attached Files:

  7. vletm

    vletm Well-Known Member

    Messages:
    2,263
    ... London gives another case ...
    Press [payment]-button and ...
    is this something that You ...

    Attached Files:

  8. SLS100

    SLS100 New Member

    Messages:
    7
    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?
  9. vletm

    vletm Well-Known Member

    Messages:
    2,263
    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!
  10. SLS100

    SLS100 New Member

    Messages:
    7
    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.

    Attached Files:

  11. vletm

    vletm Well-Known Member

    Messages:
    2,263
    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?
  12. SLS100

    SLS100 New Member

    Messages:
    7
    Ah ha! I have never used this function before! Now I understand! Thank you for your help and patience!

Share This Page