+ Reply to Thread
Results 1 to 11 of 11

Need formula that searches all cells in a column to return result

  1. #1
    Registered User
    Join Date
    07-15-2019
    Location
    Miami, FL
    MS-Off Ver
    2019
    Posts
    6

    Need formula that searches all cells in a column to return result

    Hey, I'm not sure the best way to go about this I have been experimenting with IF and Vlookup but I have failed to come up with an elegant solution (or an even remotely functional one)

    Details:
    Column A contains colors, some of which contain duplicates.
    Column B contains descriptions of the column A, some of which are blank

    Goal: I would like all unique colors in column A to have their previously listed descriptions auto-populate in column B or a new column and if the latest color is a new one something like "Actionable" to appear.


    Example (Also attached in excel file)

    Colors Taste
    Red Lame
    Blue
    Red
    Blue Smells
    Green Good
    Yellow
    Yellow Great
    White Okay
    Blue
    Red
    Black
    Attached Files Attached Files
    Last edited by Travis83; 10-14-2019 at 07:35 PM. Reason: More descriptive

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Formula Required

    Enter this in C2 and copy it down.

    HTML Code: 
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Registered User
    Join Date
    07-15-2019
    Location
    Miami, FL
    MS-Off Ver
    2019
    Posts
    6

    Re: Formula Required

    Thank you for the response. This formula works well; less one issue.

    The formula pulls data based on the first time a certain color appears. For example the first blue is blank but the 2nd one has a description. When I add another blue it lists actionable even though the 2nd blue does have a description. Also the 2nd blue says actionable even though there as already a description (because it is returning the value of the first blue)
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Travis83; 10-14-2019 at 04:06 PM. Reason: Attachments added

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Formula Required

    @ Travis83

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Dave

  5. #5
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Need formula that searches all cells in a column to return result

    Ok, so this revised formula will take care of one of the situation you described.
    When a color has blank first time but then second time a description appears, the formula will pick it up.
    If the same color appears again with a blank, the result will say "Actionable".
    So it's not perfect yet.
    I am not clear as to how this is used and why colors would appear without description.

    In other word, you want the latest description for any given color repeated until a new description appears.

    HTML Code: 

  6. #6
    Registered User
    Join Date
    07-15-2019
    Location
    Miami, FL
    MS-Off Ver
    2019
    Posts
    6

    Re: Need formula that searches all cells in a column to return result

    Thank you; that solves one of the problems perfectly.

    There is a list of "colors" that I track many of which will appear multiple times. Someone from another department, will take action to resolve the issue related to the "color" but when there are many colors per week I was looking for a quick way to confirm if the color has already been actioned.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need formula that searches all cells in a column to return result

    You could put this array* formula in cell C2:

    =IF(B2<>"",B2,IFERROR(INDEX($B:$B,MAX(IF(($A$2:$A$15=A2)*($B$2:$B$15<>""),ROW($A$2:$A$15),-1))),"actionable"))

    *An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual Enter.

    Then you can copy the formula down using your usual method(s).

    This formula will fill in Tastes from later descriptions of the same colour, so only Black Silver and Neon will be shown as actionable in your file from Post #3.

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    07-15-2019
    Location
    Miami, FL
    MS-Off Ver
    2019
    Posts
    6

    Re: Need formula that searches all cells in a column to return result

    Perhaps I'm not doing it right but this formula is still returning actionable for the 3rd blue. None of them are working properly, am I not using the array function properly?Attachment 645378

  9. #9
    Registered User
    Join Date
    07-15-2019
    Location
    Miami, FL
    MS-Off Ver
    2019
    Posts
    6

    Re: Need formula that searches all cells in a column to return result

    Another solution could be to use a formula that "looksup" from the bottom instead of the top (not including the current cell) This would return the last description since it's possible for them to be different. Is this any easy change to implement based on our current formula.

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need formula that searches all cells in a column to return result

    I've attached the workbook from Post #3 with my formula in column D, so you can compare it with what you have in column C. My thought was that if someone has allocated a Taste to the second or subsequent colour (like Blue), then you would want all of the same colours to pick up that Taste - the only actionable items are those colours which do not have a Taste allocated to them. I've added a second Neon to your list, to show that both are classed as Actionable, as expected.

    Hope this helps.

    Pete
    Attached Files Attached Files

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need formula that searches all cells in a column to return result

    By the way, if you put "horrible" in B13 against Blue, then all other Blues which do not have a Taste will show this latest value, rather than Smells from the second Blue, i.e. it will effectively take the latest definition.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 06-06-2018, 09:20 AM
  2. Replies: 3
    Last Post: 01-24-2017, 08:08 AM
  3. Replies: 6
    Last Post: 01-17-2016, 09:44 AM
  4. Multiple Vlookup Normal Formula (not array formula) or VBA Function Required
    By BoopathiK in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-17-2015, 03:06 AM
  5. [SOLVED] Simple Conditional Formating Help Required - If Blank then Red Bold text required
    By thilag in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-19-2014, 12:23 PM
  6. Formula required to hide results from another cell formula
    By 917 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-07-2013, 12:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1