Transform data to fix duplicate rows into columns

JeffWow 

I think we have to cycle through all columns (from column A to the last filled column) and extract the unique values and return them in one row.

Sub transform_data()

Dim i, j, k, l, m As Long
Dim var As Variant

Range("J:J").Clear

j = Cells(1, Columns.Count).End(xlToLeft).Column
k = Range("A" & Rows.Count).End(xlUp).Row
m = 1

For i = 1 To j
For l = 1 To k

var = Application.Match(Cells(l, i).Value, Columns(10), 0)

If IsError(var) Then
Cells(m, 10).Value = Cells(l, i).Value
m = m + 1

Else

End If

Next l
Next i

End Sub

This macro returns the intended result in column J in my understanding. For better visibility the result is returned within a column but this can be changed to a row.

In this example i've created records E and F and added new rows duplicating the values A1, B1, B2, C1, C2, C3, D1, D2 and D3.

Do you work with Office 365 or Excel for the web? If so a solution with LAMBDA is possible.