If/Then statement to move section of column to different column and row

[everts]

Can't figure out an if statement that will allow me to move the contents of a column over to another column but down a couple of rows based on the result of a logic statement in a third row. In other words, I have a column of data in row A and B. In row C I have just one number. I would like to create a column D with the following. If the data in column A row "x" is less than the single point in column C, then 0 in row "x" column D. If the data in column A becomes equal to or greater than the the number in column C, then I would like to take all the data in column B and move it over to column D starting at row "x".

Sure would appreciate help. I've been knocking this around for a couple of weeks and can't figure out how to do it. Thanks.

[rylo]

Hi

something like

D2: =IF(A2>=$C$1,B2,0)

rylo

[everts]

Hi Rylo,

Thanks for your reply. That is what I started with but the "true" statement doesn't move the entire column over starting from the top. It looks like this
..................................D........................D
A....B....C.....=IF((A2>=$C$1,B2,0)....what I want
1....2....5.....................0.........................0
2....5...........................0.........................0
3....6...........................0.........................0
4....8...........................0.........................0
5....9...........................9.........................2
6...........................................................5
7...........................................................6
8...........................................................8
9...........................................................9

If you have any idea how to move the entire column B over to Column D starting at the "true" value, I would be grateful.

[rylo]

Hi

Not sure that I follow what you are chasing. You can't use a function to move an entire column, but you can copy the function down to all cells in the column to do the test and bring back the required value.

How about you create an example file and give details on exactly what the output should look like for the given example data, and explain exactly how each result cell is determined.

rylo

[everts]

Thank you so much for trying to help me. I really appreciate it!

Attached please find an example file. When my if/then statement for column D is true, what I want to do is copy a part of a column (the 5 lines of Column B in the example (B3 through B7), over to colmun D (in this example they should be at D7 through D11), starting at the row where A was equal to $C$3 (in this example at A7). (Column E has what I would like to have column D display).

In other words, it would be great to have a statement such as .....=IF(A7>=$C$3,"X",0)where "X" will be B1. Then in the same column with the same formula copied one row down (A8>=$C$3,"X+1",0) where "X+1" will be B2, in the next row "X+2" will be B3, etc. I can put in the statement at D7, IF (A7>= $C$1,$B$1,0), but then how do I get D8 which will have a true statement to be B2 and D9 which will have a true statement to be B3, etc. when I copy the formula down? I have to either find a formula that can handle this or manually move this column over several hundred times...

Hope this helps to clarify what I'm trying to achieve. Thanks again!

[dredwolf]

Try this in D3 :

=IF(ROWS($D$3:D3)>=MATCH($C$3,$A$3:$A$11),INDIRECT("$B"&ROWS($D$3:D3)-MATCH($C$3,$A$3:$A$11)+ROW($B$3)),0)

copy down

Hope this helps

Edit-
Meant in D3, Not C3, sorry

[rylo]

Hi

Another way
D3: =IF(A3<$C$3,0,OFFSET($B$3,COUNTIF(D$2:$D2,">0"),0))

rylo

[dredwolf]

Ooooh! I like that one !

[everts]

A HUGE thank you to both Dredwolf and Rylo for helping me with this. I really appreciate your time and help!

[dredwolf]

You are welcome !

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"