Copying data to another sheet based on rule, with no blank spaces

[MurdochQuill]

Hi all,

I'm trying to copy according to yes/no rules to another sheet. When I copy, I want to get rid of the blank spaces between outputs, but not have them duplicate either. Anyone know how to do this?

Here's what I have so far:

First sheet with raw data:

Book2
	A	B	C	D	E	F
1	Name	Description	Type	Code		Method
2	Saturn	Large	Gas	GLS-01		Yes
3	Ceres	Proto	Small	SM-01		No
4	Earth	Inhab	Med	EARTH-00		Yes
5	Earth 2	Uninhab	Med	EARTH-01		No
6
7
8
Sheet1

Destination sheet with outputted data:

Book2
	A	B	C	D	E	F
6	Name	Description	Type	Code		Method
7	Saturn	Large	Gas	GLS-01		Yes
8
9	Earth	Inhab	Med	EARTH-00		Yes
10
11
12
Sheet2

Cell Formulas
Range	Formula
A7:F7,A9:F12,A8:D8	A7	=IF(Sheet1!$F2 = "YES", IF((Sheet1!A2)="","",(Sheet1!A2)),"")

However I would like it to format like this:

Book2
	A	B	C	D	E	F
1	Name	Description	Type	Code		Method
2	Saturn	Large	Gas	GLS-01		Yes
3	Earth	Inhab	Med	EARTH-00		Yes
4
5
Sheet2

 

[maabadi]

Try the yellow Part formula at sheet2. Press CTRL+SHIFT+ENTER:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Description	Type	Code		Method		Name	Description	Type	Code		Method
2	Saturn	Large	Gas	GLS-01		Yes		Saturn	Large	Gas	GLS-01	0	Yes
3	Ceres	Proto	Small	SM-01		No		Earth	Inhab	Med	EARTH-00	0	Yes
4	Earth	Inhab	Med	EARTH-00		Yes
5	Earth 2	Uninhab	Med	EARTH-01		No
6
7
8
9
Sheet1

Cell Formulas
Range	Formula
H2:M5	H2	=IFERROR(INDEX(A$2:A$5,SMALL(IF($F$2:$F$5="Yes",ROW($F$2:$F$5)-ROW(INDEX($F$2:$F$5,1,1))+1),ROWS($H$2:H2))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MurdochQuill]

Excellent stuff, thank you!

 

[maabadi]

You're Welcome & Thanks for Feedback.

 

[Fluff]

As you have 365 another option is

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Description	Type	Code		Method			Name	Description	Type	Code		Method
2	Saturn	Large	Gas	GLS-01		Yes			Saturn	Large	Gas	GLS-01		Yes
3	Ceres	Proto	Small	SM-01		No			Earth	Inhab	Med	EARTH-00		Yes
4	Earth	Inhab	Med	EARTH-00		Yes
5	Earth 2	Uninhab	Med	EARTH-01		No
6
7
8
Master

Cell Formulas
Range	Formula
I2:N3	I2	=LET(fltr,FILTER(A2:F100,F2:F100="Yes"),IF(ISBLANK(fltr),"",fltr))
Dynamic array formulas.