Formula to Copy/Paste data based on value of another cell. SKIP BLANK ROWS.

Hello.

In need a formula that looks at the value of one cell. If a condition is met then another cell is copy/pasted. This needs to continue down the worksheet but SKIP results that are blank. the pasted results must be pasted directly underneath each other row after row. No blank rows.

How do I do this? It needs to be a formula. No VBA or Copy/Paste please.

Example below:
1. On "Sheet1" I have the following data in column A and B.
2. Excel must look at column B on Sheet1. If the result is "1" then on Sheet2 paste Sheet1 column A.
3. If Column B is NOT "1" then excel needs to SKIP that row and paste the next row directly below.

Sheet1

	A	B
1	test1	1
2	test2	1
3	test3	2
4	test4	3
5	test5	1
6	test6	5
7	test7	1
8	test8	2
9	test9	1
10	test10	4

On "Sheet2" the final result needs to be:

Sheet2

	A
1	test1
2	test2
3	test5
4	test7
5	test9
6
7
8
9
10

Notice how the data is compressed. There are no blank rows in between the data. This is what I need.


Below is the result I DO NOT want:

Sheet2

	A
1	test1
2	test2
3
4
5	test5
6
7	test7
8
9	test9
10

Here is where I am at. On "Sheet2" I could copy down a simple formula such as =IF(Sheet1!C2>1,"",Sheet1!B2). The challenge is the resulting blank rows. How do I get a result that skips the blank rows?

Thanks for the help!

Originally Posted by sktneer

Try this array formula in the Cell A1 of sheet2. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. hold down the Ctrl + Shift and then press Enter)

Please Login or Register  to view this content.

For detail see the attached sheet.

That did the trick. Thanks for the help and quick reply!