Insert data in col d from either a or b depending on value in c

[Supersadie]

I have columns ABCD.
A and B contain data
C contains numbers 0-9 - randomly - sometimes there will be duplicates
Rules:
Enter data into D from A until number in C=4 (trigger value)
AFTER this point, begin inserting data from B
When data in C=5 (trigger value), AFTER this point, begin inserting data from A again.
Example follows. NB Data between trigger values will continue to be taken from either A or B until the next trigger.
Many thanks.

A AA 7 A
B BB 2 B
C CC 5 C
D DD 5 D
E EE 6 E
F FF 5 F
G GG 4 G
H HH 4 HH
I II 3 II
J JJ 2 JJ
K KK 6 KK
L LL 2 LL
M MM 3 MM
N NN 3 NN
O OO 4 OO
P PP 5 PP
Q QQ 5 Q
R RR 5 R
S SS 4 S
T TT 7 TT
U UU 6 UU
V VV 5 VV
W WW 4 W
X XX 3 X

[kersplash]

According to your rule then always take value from A unless column C=4 (when it will use B)?

[Supersadie]

Values are taken from A until C=4, then we begin to take values from B until C=5 and so forth, switching between columns. So the data between the trigger points will come from one or other column. Not as easy as it looks!

[adagio]

The below should work;

Please Login or Register  to view this content.

=TRIM(REPT(B1,(C1=4))&REPT(A1,(C1>=5))&REPT(A1,(C1<4)))

Please Login or Register  to view this content.

[Supersadie]

Thanks for the thought. Only partially successful - it's the cell AFTER the trigger that changes columns so the cell itself should be consistent with the ones before it. Hope that makes sense.