Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    One term for many (Excel 2003)

    Not sure how best to do this:I need to search for words like "refurbish" "repair" "rebuild" "rework" within descriptions (let's say in Column C cells) and in column D return one string like "Costs for refurbishment" to describe all those "re's" at once. This would then be a pivot table field total for counts and costs. I have used the Find and Search functions but am not sure which would be best, since neither one directly returns a term I want the pt report to use. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: One term for many (Excel 2003)

    I'm not sure I understand, but does this array formula in D1 referring to C1, to be confirmed with Ctrl+Shift+Enter and to be filled down as far as needed, do what you want?
    <code>
    =IF(SUM(1*NOT(ISERROR(SEARCH({"repair","rework","r ebuild","refurbish"},C1)))),"Costs for refurbishment",C1)
    </code>
    Instead of including the list of search terms in the formula, you can enter it in a range of cells, say J1:J4, and use
    <code>
    =IF(SUM(1*NOT(ISERROR(SEARCH($J$1:$J$4,C1)))),"Cos ts for refurbishment",C1)</code>

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One term for many (Excel 2003)

    Excellent. And you understood perfectly.
    Sorry this got mixed up with the other thread. I did not notice I was replying to the post instead of making a new one! However, all's well that ends well: looks as if I helped out!

Posting Permissions

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