Formula to Return Results without any Empty Rows

[HangMan]

I have the following. I want to do a VLOOKUP (or similar) that will return only the results in Column B (Value) when the Criteria (Column A) is Yes, but I need the results in Column D to have no gaps as shown, so I think it needs an addition to the VLOOKUP to achieve this, but I don't know if it can be done with just a formula or would require some VBA? If the latter, I don't know how I would achieve this?

Obviously if I do a straight VLOOKUP, the results in Column D will have blanks in it everytime there is a 'No' in Column A and I need the results to appear as per Column D below, without the blanks, is this possible to do?

I've just noticed that the text below doesn't display so well, So, I've added a sample sheet.

Many thanks

A B D
Criteria Value Result
Yes 1000 1000
Yes 2000 2000
Yes 500 500
No 100 20
No 450 21
No 750 500
Yes 20 20
Yes 21 35
Yes 500 50
No 7 1000
No 7 250
Yes 20 257
No 15
Yes 35
No 47
No 80
No 41
Yes 50
No 9
No 12
Yes 1000
Yes 250
Yes 257

[benishiryo]

hi HangMan. try this array formula:
=IFERROR(INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100="Yes",ROW($A$2:$A$100)),ROWS(C$2:C2))-ROW($A$2)+1),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Fotis1991]

Array formula in D2 and copy down

Please Login or Register  to view this content.

)

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[HangMan]

Hi benishiryo and Fotis,

I think you both had the same solution at the same time.

That works perfectly, many thanks to both of you for coming back so quickly and taking the time to reply...

[Cupsay]

Hello Benishiryo,

I adapted the array formula you gave as a solution to this thread to solve a similar problem I was trying to solve. I think this link might take you to that thread:

http://www.excelforum.com/excel-gene...t=#post4311498

I want to give you credit for your immense contribution to me finding my solution so, if you can leave a message on my thread I'll be able to do so. In fact, you might like to quickly look at my post announcing a solution and its attached excel workbook and comment, maybe even suggest a shorter/better way.

Meanwhile, thank you.

Regards,
Jon.

[FlameRetired]

Would you consider a helper column? In C2 this filled down to row 24.

Please Login or Register  to view this content.

Then this non-array formula in D2 filled down until you get blanks.

Please Login or Register  to view this content.

[Cupsay]

Hi Dave,

I tried what you suggested (making allowance for the fact I have no data in column A of either worksheet - and guessing you meant to put both these formulas on the summary worksheet - the first starting at C12 and the second one replacing the array formula starting at D12). I didn't recognize the end reference ($2:2) but left it as you put it. =IFERROR(INDEX($B$2:$B$24,SMALL($C$2:$C$24,ROWS($2:2))),"") The result was I lost the number of items in that room (in fact, it was replaced with 'Yes'.

Yes, it would be good if I could change an array formula with a shorter non-array one as any changes needed would be easier but, it still has to give me a closed up list of items and the corresponding number of those items.

Regards,
Jon.

[shukla.ankur281190]

or Try

E2

Please Login or Register  to view this content.

[Fotis1991]

Unfortunately your post(S) does(DO) not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.