Prefix/Suffix Data Validation List

[jezvanderbrown]

I'm finding it really difficult to understand how to prefix or suffix a set of words based on a what I have chosen in a 'Data Validation List'.
CLICK HERE TO SEE/DOWNLOAD AN EXAMPLE SPREADSHEET
I have a list of words in column A and a list of words in column B.
In Cell C1 i have a data validation list and the options are 'Suffix' or 'Prefix'
What i would like to happen in (Column D), is if i choose Suffix to place all words in column B after of all words in column A. And if I choose Prefix to place all words in column B before all words in column A.
FYI - I'm using Excel 2013 and also there can be any number of words in column A and B.
I've tried using concatenate formulas but populating a list of words based on what is selected from a data validation list is way beyond my knowledge.
Can anyone help?
Thanks in advance

 

[oldbrewer]

[TABLE="width: 572"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD]orange[/TD]
[TD]yoghurt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]prefix[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]icecream[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD]milkshake[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]drink[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]this macro does it for prefix in cell F1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yoghurtorange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yoghurtapple[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]you will need to write the same code again (nearly)[/TD]
[/TR]
[TR]
[TD]yoghurtbanana[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]for F1 = suffix[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]icecreamorange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]icecreamapple[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sum = 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]icecreambanana[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] n = Cells(1, 6)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]milkshakeorange[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] If n = "suffix" Then GoTo 200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]milkshakeapple[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] For j = 1 To 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]milkshakebanana[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] For k = 1 To 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]drinkorange[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Sum = Sum + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]drinkapple[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"] Cells(Sum, 1) = Cells(j, 2) & Cells(k, 1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]drinkbanana[/TD]
[TD][/TD]
[TD][/TD]
[TD] Next k[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]200 temp = 999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]