Paste certain files to a sheet where cells are merged

[keis386]

How do I copy certain cells from column B (cells that have a value in column C), to sheet 2.

The difficult part is that in sheet 2 cells are merged. How will I do that?

In the attachment I made an example,

Sheet 1 contains all the information
Sheet 2 is where certain information has to be paste. (difficult part is that cells are merged here)
I only want to paste the cells from column B that contain a number in column C.

To make it more clear, I made what it should be in sheet 3.

Hope someone can help me!

ALSO A second excel sheet, with a almost working formula

[JBeaucaire]

1) unfilter the sheet1
2) Add this formula in D2 and copy down
=COUNT($C$1:$C2)

3) on Sheet2, put this formula in B2 (merged cell) and copy down:

=INDEX(Sheet1!$B:$B, MATCH(CEILING((ROW()-1)/3,1), Sheet1!$D:$D, 0))


To copy down further than needed but not get errors, use this instead:

=IFERROR(INDEX(Sheet1!$B:$B, MATCH(CEILING((ROW()-1)/3,1), Sheet1!$D:$D, 0)), "")

[keis386]

Thanks for helping!

But it works not totaly. I applied the formula but I do not get all the values. In my post I posted a second excelsheet where I applied your formjulas. Can you look at it what is missing?
Thanks very much!

[JBeaucaire]

You have to set the formula to divide by the number of merged cells.

=IFERROR(INDEX(Sheet1!$B:$B, MATCH(CEILING((ROW()-1)/3,1), Sheet1!$D:$D, 0)), "")


I presume you need to change that to 4

[keis386]

Thanks very much, my boss would be very happy with the result!