Specific Search in spreadsheet

Discussion in 'Mac Apps and Mac App Store' started by GerritV, Jul 11, 2019.

  1. GerritV macrumors 65816

    Joined:
    May 11, 2012
    #1
    Hello,

    Say I have a number of data in columns I-J.
    I is an index, J is a text.
    What I want is to search all lines that have "A" in column I, and have the adjacent text from column J to appear in column L, while skipping the lines that have "B".
    Anyone knows a formula for this that I can enter in column L ?
    I wouldn't mind the intermediate column K.

    Screenshot 2019-07-11 at 16.25.49.png
     
  2. liya1201 macrumors regular

    Joined:
    Sep 8, 2010
    #2
    A quick thought: could you select column I and J and "sort" column I alphabetically?
     
  3. GerritV thread starter macrumors 65816

    Joined:
    May 11, 2012
    #3
    Thanks for the suggestion.
    I'll elaborate a little more. This is a shared google spreadsheet, and when I perform any sort action on a tab, the others will see it the same way. So I was thinking of creating a second tab where there's some triage going on for one team member only. I hope I'm clear enough here.
     
  4. Moakesy macrumors regular

    Moakesy

    Joined:
    Mar 1, 2013
    Location:
    UK
    #4
    This will work in Excel, but I don't know Google well enough to be sure if it will work in there.

    Copy and paste the formula below, paste it into cell 'L1', then copy it down into all those cells I've shown in green. Put the letter 'A' in cell K1 (as highlighted yellow in my example). If you change the value in cell K1, then the results change.

    =IFERROR(INDEX($J$1:$J$10,SMALL(IF(K$1=$I$1:$I$10,ROW($I$1:$I$10)- MIN(ROW($I$1:$I$10))+1,""), ROW())),"")



    upload_2019-7-18_22-29-51.png
     

    Attached Files:

  5. Moakesy macrumors regular

    Moakesy

    Joined:
    Mar 1, 2013
    Location:
    UK
    #5
    Or thinking about it, if Google Docs does pivot tables, you could just create one of those. Much simpler!!
     
  6. GerritV thread starter macrumors 65816

    Joined:
    May 11, 2012
    #6
    Wow, thanks for this formula!
    I managed to translate it to Dutch, and so far I get "ONE" in cell L1. After copying down, the other cells in column L remain empty. And when I type "B" in cell K1, column L is empty as well.
    I'll hopefully figure it out.
    --- Post Merged, Jul 19, 2019 at 11:47 PM ---
    Agree, but I thought a pivot table doesn't like empty cells in it's reach (which I have a lot of).
     
  7. Moakesy macrumors regular

    Moakesy

    Joined:
    Mar 1, 2013
    Location:
    UK
    #7
    Try putting “A” in cell K2,K3 etc, as you may have accidentally deleted a $ sign during translation.

    I’m trying to work out why changing the value in K1 doesn’t work. A common reason is because there is a ‘space’ there. So the cell entry is ‘B’+space or space+B instead. The easiest way to check is to use the formula =LEN(cell ref). This counts the number of characters in the cell. So B is one character, but B+space will be two characters.
     

Share This Page

6 July 11, 2019