Become a MacRumors Supporter for $25/year with no ads, private forums, and more!

Specific Search in spreadsheet

GerritV

macrumors 65816
Original poster
May 11, 2012
1,282
411
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
 

liya1201

macrumors regular
Sep 8, 2010
134
16
A quick thought: could you select column I and J and "sort" column I alphabetically?
 
Comment

GerritV

macrumors 65816
Original poster
May 11, 2012
1,282
411
A quick thought: could you select column I and J and "sort" column I alphabetically?

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

Moakesy

macrumors 6502
Mar 1, 2013
447
937
UK
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
 

Attachments

  • upload_2019-7-18_22-28-30.png
    upload_2019-7-18_22-28-30.png
    121.2 KB · Views: 54
Comment

Moakesy

macrumors 6502
Mar 1, 2013
447
937
UK
Or thinking about it, if Google Docs does pivot tables, you could just create one of those. Much simpler!!
 
Comment

GerritV

macrumors 65816
Original poster
May 11, 2012
1,282
411
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())),"")

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.
[doublepost=1563605264][/doublepost]
Or thinking about it, if Google Docs does pivot tables, you could just create one of those. Much simpler!!
Agree, but I thought a pivot table doesn't like empty cells in it's reach (which I have a lot of).
 
Comment

Moakesy

macrumors 6502
Mar 1, 2013
447
937
UK
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.
 
Comment
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.