CODEWITHSUNDEEP
excelexcel-2003
newBexcel 3724612
newBexcel 3724612

Reputation: 27

Excel merging two columns

I have two columns, each with slightly different list of countries

e.g.

 A         B         C
 Japan     Japan
 US        China
 UK        Brazil
 India     Canada
 China     Mexico
 Brazil    Germany
           France
           Peru

How do I "merge" the two columns so that I can have values that exist in either column A or B in column C, without repeating the values?

i.e.

 A         B         C
 Japan     Japan     Japan
 US        China     US
 UK        Brazil    UK
 India     Canada    India
 China     Mexico    China
 Brazil    Germany   Brazil
           France    Canada
           Peru      Mexico
                     Germany
                     France
                     Peru

NOTE: I am currently using excel 2003

thanks a lot!

EDIT: Here's my current attempts, in "exports and imports test" sheet, column G, I'm trying to VLOOKUP from the source of both columns A and D and "merge" it with an OR formula, and then sort it properly on the next column. It's got an N/A error right now unfortunately...

http://wikisend.com/download/391826/Sample%20test.xls

EDIT2: I tried using the macro posted below, while editing (by guessing) it to suit my needs, and the macro became this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("G3").Select
    ActiveSheet.Paste
    Range("D3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G3").Select
    Selection.End(xlDown).Select
    Range("G30").Select
    ActiveSheet.Paste
    Range("G3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$G$3:$G$60").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

And here is the result (column G):

enter image description here

It didn’t quite work (in that duplicates exist) and I got an “Object doesn’t support support this property or method” error.

Upvotes: 0

Views: 356

Answers (2)

mucio
mucio

Reputation: 7119

WHat about a macro like this (I'm assuming data are in columns A, B, and C starting at row 2 and without gaps):

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("C2").Select
    ActiveSheet.Paste
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C2").Select
    Selection.End(xlDown).Select
    Range("C10").Select
    ActiveSheet.Paste
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$C$2:$C$16").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

Upvotes: 1

Duncan
Duncan

Reputation: 1034

This is kind of a hack, but you could do this:

  1. Paste both columns into the same column (A).
  2. Sort column A.
  3. Create a new column (B) beside it and put the formula =IF(A2=A1, "", A2), and drag down. This will print out the word only if it is different from the word above it.
  4. Copy and paste by value in column B, and then sort column B to put the empty cells on top.
  5. Delete the empty cells.

Upvotes: 1

Related Questions