Applying IF logic as VBA down whole column for 5000 rows

Hello friends,

I need to apply what would be 10 convoluted IF statements to a whole column using VBA.

Situations is that I need to return (in column P) a concatenation of column D & " & " & column C's adjacent cells if each cell of column A is equal to up to 10 rows below (up to 5000 rows). Confused?!

Well If I was to write this in formula it would look like this:

=IF(ISBLANK(A2),""&
IF(A2=A11,C11&"X "&D11&" & "&C10&"X "&D10&" & "&C9&"X "&D9&" & "&C8&"X "&D8&" & "& C7&"X "&D7&" & "&C6&"X "&D6&" & "&C5&"X "&D5&" & "&" & "&C4&"X "&D4&" & "&C3&"X "&D3&" & "&C2&"X "&D2,
IF(A2=A10,C10&"X "&D10&" & "&C9&"X "&D9&" & "&C8&"X "&D8&" & "& C7&"X "&D7&" & "&C6&"X "&D6&" & "&C5&"X "&D5&" & "&" & "&C4&"X "&D4&" & "&C3&"X "&D3&" & "&C2&"X "&D2,
IF(A2=A9, C9&"X "&D9&" & "&C8&"X "&D8&" & "& C7&"X "&D7&" & "&C6&"X "&D6&" & "&C5&"X "&D5&" & "&" & "&C4&"X "&D4&" & "&C3&"X "&D3&" & "&C2&"X "&D2,
IF(A2=A8,C8&"X "&D8&" & "&C7&"X "&D7&" & "&C6&"X "&D6&" & "&C5&"X "&D5&" & "&" & "&C4&"X "&D4&" & "&C3&"X "&D3&" & "&C2&"X "&D2,
IF(A2=A7,C7&"X "&D7&" & "&C6&"X "&D6&" & "&C5&"X "&D5&" & "&" & "&C4&"X "&D4&" & "&C3&"X "&D3&" & "&C2&"X "&D2,
IF(A2=A6,C6&"X "&D6&" & "&C5&"X "&D5&" & "&" & "&C4&"X "&D4&" & "&C3&"X "&D3&" & "&C2&"X "&D2,
IF(A2=A5,C5&"X "&D5&" & "&" & "&C4&"X "&D4&" & "&C3&"X "&D3&" & "&C2&"X "&D2,
IF(A2=A4,C4&"X "&D4&" & "&C3&"X "&D3&" & "&C2&"X "&D2&
IF(A2=A3,C3&"X "&D3&" & "&C2&"X "&D2,C2&"X "&D2))))))))))

As I am not great with VBA but I need this done in a macro I was hoping someone can show me how it would be done as this code does not work when it is converted to VBA but does work as a formula.

Thanks,
Brendan