How to merge two or many columns of same name in excel-vba

[ABDUL SHIYAS A]

Hi,
I am working in a dataset which contains columns of same names. They are filled so that in one row, only one among them will have data. So I want to merge them and create a new column in the last. As those columns can appear anywhere(not fixed column order) and can appear any no. of times(not fixed count), we have to use column name only. Suppose the column name be "Preference".
We can merge columns which contains _reference_ as no other columns got same name.

What I have done so far is

Private Sub CommandButton1_Click()

Dim i As Integer
Dim MyWorksheetLastRow As Byte
Dim MyWorksheetLastColumn As Byte

MyWorksheetLastRow = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row
MyWorksheetLastColumn = Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

Worksheets(1).Cells(1, MyWorksheetLastColumn + 1).Value = "PAID/NON PAID"
For i = 2 To MyWorksheetLastRow
If Cells(i, Application.WorksheetFunction.Match("Preference", Range("1:1"), 0)).Value = "Paid" Then
Cells(i, MyWorksheetLastColumn + 1).Value = Cells(i, Application.WorksheetFunction.Match("Preference", Range("1:1"), 0)).Value
Else: Cells(i, MyWorksheetLastColumn + 1).Value = Cells(i, 1 + Application.WorksheetFunction.Match("Preference", Range("1:1"), 0)).Value
End If
Next i

End Sub

By this we can get the first datas from first "Preference"column only.
Pls sort this and help me. I am struck