IF statements and getting excel to skip rows and leave no blanks or falses

I have two spead sheets one named "2nd" and one named "sheet 1". on the first sheet which is named 2nd I have a list of names in column A and in column C I have a letter: "D"= which mean day shift "E"= mean evenign shift and "N"= which means night shift. on the second sheet" sheet 1" I would like it to read column C in speed sheet "2nd" and if column C has a "D" I would like it to push out the name that is in column A in speed "2nd". Now I here is the formula that I am using to do that: = IF('2nd'!C:C= "D", '2nd'!A:A) This forumla is doing what I want it to do but every time it reads a column that does not have a "D" it continues to push out the word "False". I would like it to skip to the next row and continue reading the rows until it finds a nother "D". I do not want any blank row in "sheet 1" how do I do this. I have attached my excel document so that you can understand what I am saying.


Thanks for your help,
Chanelle

This is an array formula. You'll need to adapt the "ranges" within the formula if you increase the number of rows on the "2nd" sheet. Do not try to use them with whole columns. In Sheet1 cell B10 put this array formula:

=IF(ROW(A1)>COUNTIF('2nd'!$C$1:$C$11, "D"), "", INDEX('2nd'!$A$1:$A$11,SMALL(IF('2nd'!$C$1:$C$11="D",ROW('2nd'!$C$1:$C$11),""),ROW(A1))))

...and confirm the formula by pressing CTRL-SHIFT-ENTER to activate the array. When the first name appears, you can copy that cell dpwn through B20.

The reference "A1" marked in RED in that formula is a trick to pull the FIRST value that has a "D", so when you copy that formula into B21 to start the next group, you will need to make sure that is set back to A1 and the "D" is changed to "E" or "N" or whatever group you are collecting.

In your file on Sheet1 enter this array formula** in B10 and copy down until you get blanks:

=IFERROR(INDEX('2nd'!A:A,SMALL(IF('2nd'!C$1:C$11="D",ROW('2nd'!C$1:C$11)),ROWS(B$10:B10))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

this, longer, non-array formula in Sheet1!B10 will produce the same result:

Please Login or Register  to view this content.

if you were to capture D or E or P in another cell, such as Sheet1!B9, then the formula could be (formula is not case-sensitive):

Please Login or Register  to view this content.

Oe way...

=IFERROR(INDEX('2nd'!A:A,SMALL(IF('2nd'!C$1:C$11={"D","A"},ROW('2nd'!C$1:C$11)),ROWS(B$10:B15))),"")

Still array entered!