List 2 columns into one

[Solved/Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,


I hoping that someone can help me fix this.

I have two columns with possible duplicate vendor name, example

VendorName VendorID VendorName2 VendorID2
JAMS 479333 JAMES 310144
JAN MAREE 417333 JAN MARIe 309437
JANET 780198 JANIT 714348

How do I combine the columns into one so I have

VendorName VendorID
JAMS 479333
JAMES 310144
JAN MARIe 309437
JAN MAREE 417333
JANET 780198
JANIT 714348


Thank you very much in advance.

regards,

efek

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
I have given three macros . All the three macros should be copied in the module.

now run the macro "test" (only this macro)
and see sheet 2

if you want recheck again run "undo" and then run "test" and see sheet2.

Dim r As Range, c As Range, dest As Range

Sub test()
Worksheets("sheet1").Activate
Set r = Range(Range("a2"), Range("a2").End(xlDown))
For Each c In r
Range(c, c.Offset(0, 1)).Copy
copying
Range(c.Offset(0, 2), c.Offset(0, 3)).Copy
copying
Next c
Application.CutCopyMode = False
With Worksheets("sheet2")
.Range("A1") = "vendor namae"
.Range("B1") = "vendor id"
End With
End Sub


Sub copying()
With Worksheets("sheet2")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest.PasteSpecial
End With
End Sub


Sub undo()
Worksheets("sheet2").Cells.Clear
End Sub