-1

I have 2 columns of data: 

COL_A  COL_B
AAAA   PM
BBBB   PM
CCCC   P
DDDD   M
EEEE   PM

I want to create a 3rd column by filtering Column A given that Column B = "PM." But I want to do so by only adding that one additional column (i.e., no stepwise/multi-column approach). The result would look like this:

COL_A  COL_B   COL_C
AAAA   PM      AAAA
BBBB   PM      BBBB
CCCC   P       EEEE
DDDD   M       
EEEE   PM

The post, Excel formula needed: see if a text in one column appears within a text string second column, discusses (vaguely) how to do this using multiple columns along the way.

I am looking for a (non-VBA) approach to filter A by B using only 1 line of functions (i.e., only 1 additional column in the spreadsheet).

  • In other words, I don't want to add column C with some function =___(A,B,...) and then column D with some function =if(C__...) based on the new column C, etc.

Can I do this?

theforestecologist
  • 4,667
  • 5
  • 54
  • 91
  • @pnuts my question is not a duplicate of the [one you marked](https://stackoverflow.com/q/44761525/4581200). Please look at that question closer to see they are asking about eliminating blanks in a column. I'm asking about sorting one column based on another. – theforestecologist Jun 27 '17 at 16:42
  • @pnuts I'm sure there are, but I could not find one that answered my specific question. **Closing a question as duplicate of a non-duplicate question b/c you can't find a duplicate is not appropriate.** Please reopen my question until you find a valid dupe. Thanks. – theforestecologist Jun 27 '17 at 16:46
  • @pnuts [this](https://stackoverflow.com/q/20894362/4581200) one is not, but you're getting closer with the other two. But even still, my question (and definitely the answer my question received) are clearer and more complete (i.e., no links or poor-quality images), and therefore *those* questions would be better *made a dupe of this post*. – theforestecologist Jun 27 '17 at 16:58
  • Sure ok I will. But still, please unmark my question as a duplicate of a question that is not actually a duplicate. That doesn't help anybody. Thanks. – theforestecologist Jun 27 '17 at 17:02

1 Answers1

1

In C1 enter the array formula:

=IFERROR(INDEX($A$1:$B$20,SMALL(IF($B$1:$B$20="PM",ROW($B$1:$B$20)),ROW(1:1)),1),"")

and copy down:

enter image description here

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

You can increase the range in the formula to match your table size.

EDIT#1:

The INDEX() function pulls data from a column based on row. THE SMALL() function creates an array of rows matching the desired criteria.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • This works perfectly! Exactly what I was hoping for!! Your edits help make sure I know what to do, as well ,so thanks for those. – theforestecologist Jun 27 '17 at 16:32
  • One additional request: could you walk through the logic of your function so that this post can be maximally useful/helpful to all ranges of excel users? (I.e., maybe just add a short paragraph or something at the end explaining how the function works). – theforestecologist Jun 27 '17 at 16:34
  • 1
    @theforestecologist See my **EDIT#1** – Gary's Student Jun 27 '17 at 16:40