Removing Rows using IF CONDITION on a separate sheet

[wedzmer]

Hello excelites!

I am working on a huge number of data on excel but since such file is so big, I took out a sample from what I'm trying to achieve and post it here. My problem is, I'm trying to use an "IF" statement where if - say - a company did not "purchase" anything from us on this said year, then the other sheet should not include them in the tally.

As you can see, this is my base or reference sheet:

pic1.png

On the said sheet, on cell A3, "wonderland" did not purchase an order from our office as was indicated on cell B3.

Now on this separate sheet,

pic2.png

I want this sheet to copy all the companies that purchased from us and neglect or ignore the companies that didn't.
The statement should've been:

1. If C2 of "Orders - 2014" = Yes, then copy B2 of "Orders - 2014".
2. If C2 of "Orders - 2014" = No, then do not copy B2; instead skip to next row.

How do I formulate that in Excel?

[Kaper]

Hm, I do not think it is the best idea to do this with formula. I'd raher filter adta on column B (=yes) select all visible ctrl+A then ctrl+G -> special -> only visible cells, copy and paste in the second sheet.

[Debraj Roy]

Hi wedmzer..

I hope you will realize.. what the problem we are facing in case of picture.. post..
use this formula..
12-03-2015 21-52-16.jpg

[wedzmer]

Hi Debraj Roy,

I tried to use your formula but it doesn't work on the succeeding cells....

Here's the sheet.. I'm sorry I used screenshots...


sample sheet.xlsx

I would also like to add because I forgot to mention on the original post that there's a 3rd condition supposedly that should be:

3. If F2>5000, then it should not be included on the tally sheet as well. (even if c3="yes")

[samba_ravi]

b2=IFERROR(INDEX('Orders - 2014'!B$2:B$14,SMALL(IF('Orders - 2014'!$C$2:$C$14="Yes",ROW('Orders - 2014'!$C$2:$C$14)-MIN(ROW('Orders - 2014'!$C$2:$C$14))+1),ROW(A1))),"")
try this array formula (shift+ctrl+Enter) and copy across
since it is an array formula please confirm it with (shift+ctrl+Enter)

[wedzmer]

HELLO NFLSALES!!!

Thanks for your response.. that worked on looking up the company names that purchased. thank you!

But what about the succeeding cells? and I was looking for the third condition where I'm only going to indicate the sales that are less than 5000.

[samba_ravi]

sorry over sighted your query
try
=IFERROR(INDEX('Orders - 2014'!B$2:B$14,SMALL(IF('Orders - 2014'!$C$2:$C$14="Yes",IF('Orders - 2014'!$F$2:$F$14<=4000,ROW('Orders - 2014'!$C$2:$C$14)-MIN(ROW('Orders - 2014'!$C$2:$C$14))+1)),ROW(A1))),"")