Results 1 to 6 of 6

Thread: Budget Help - Populate column based on text in another column

  1. #1

    Budget Help - Populate column based on text in another column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm fairly new to Excel aside from basic stuff and have been trying to find the answer to this for days now. (I'm on my phone so I can't upload the file) I have 2 columns; A:A will be the category I assign to expenses and B:B is the actual expense description. B1 shows "deposit" and B2 shows "Paycheck number xxx-xxxx" with the X's representing weekly changing numbers. I would like a formula along the lines of =if (b:b="income" (or) "paycheck", "income", "")

    It seems that the (or) is the part Im having trouble with. The first cell (B1 showing Deposit) fills A1 with Income but the next row does nothing. Also if I put the Paycheck description first it gives a "False" because it seems to be looking for the entore cell contents, which change with each entry. Theart that is common is they all will say PAYCHECK. I've heard that it is possible to create available table on another sheet and just reference it, but at this point finding the right formula is personal. I can't let it win! Any help would be greatly appreciated.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,464
    Articles
    0
    Excel Version
    Excel 2016
    Do you mean something like:

    =if (OR(B1="income",B1="paycheck"), "income", "")

    assuming first entry is in B1.


  3. #3
    Thank you very much for your response. This works perfectly for the cell containing DEPOSIT as that's the only thing in the cell. The cell that contains the PAYCHECK information, however, does not seem to work. I'm assuming it's because there is other text in the cell. Is there a way (within the solution you've posted) to have it search for just the one word and ignore the rest? Also, (last question, I promise!) how many different terms could I nest in this formula? Again, thank you so much for your time.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,464
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =IF(OR(ISNUMBER(SEARCH({"income","paycheck"},B1))),"income","")

    this will allow you to add many keywords between the { } brackets.


  5. #5
    You are a God among mere mortals! That worked perfectly! I have to admit, I had seen the ISNUMBER command but figured it had to do with,wait for it....numbers. Seems that 99.99% of the Excel searches I did came up with numbers somewhere in the cells, which I'm not experienced enough to deduce to what I need.

    Again, thank you so much for all your help!

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,464
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by Anderson620 View Post
    ... I have to admit, I had seen the ISNUMBER command but figured it had to do with,wait for it....numbers. Seems that 99.99% of the Excel searches I did came up with numbers somewhere in the cells, ...
    In this case the ISNUMBER is checking that the results of the SEARCH function is returning numbers (referring to position numbers)... SEARCH returns a position number within the string where the search string is found, and it returns a #VALUE! error if it doesn't find it. So ISNUMBER checks that a position number was returned, meaning the text was found somewhere in the cell....


    ... and you're welcome


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •