Transpose from one sheet to the next and leave out blank cells

[Guest]

I have a table

Y A
Y B
N C
Y D

I need a formula (can't use a macro) to show only column 2 on another sheet
and have it displayed on one row, leaving out the fields with an "N" in the
first column.

A B D <--- this would be my result on the other sheet, one row and three
columns.

 

[Gord Dibben]

You could use autofilter to filter out the rows where "N" appears.

Then F5>Special>Visible Cells Only>OK

Copy and select a cell on Sheet2 then Paste Special>TransposeOK>Esc


Gord Dibben MS Excel MVP

 

[Guest]

Here's one formulas play if you would like the transpose dynamic in the other
sheet ..

Assume source data in Sheet1, cols A and B, from row1 down
Put in C1: =IF(OR(A1={"","N"}),"",ROW())
Copy C1 down to cover the max expected extent of data in col A.
Hide col C away if desired.

Then in another sheet,

Place in eg, B2
=IF(COLUMN(A1)>COUNT(Sheet1!$C:$C),"",INDEX(Sheet1!$B:$B,SMALL(Sheet1!$C:$C,COLUMN(A1))))
Copy B2 across as far as required. B2 across will return the results that
you seek, all neatly bunched to the left.