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

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.

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

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

Then F5SpecialVisible Cells OnlyOK

Copy and select a cell on Sheet2 then Paste SpecialTransposeOKEsc


Gord Dibben MS Excel MVP

On Tue, 30 Jan 2007 15:44:00 -0800, notso
wrote:

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.

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

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"notso" wrote:
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.