Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|