0

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.

newBexcel 3724612
  • 27
  • 5
  • Do you want to do this in VBA or with a formula? If you want to do it in VBA, you can break this down to merging two lists or arrays, see for example http://stackoverflow.com/questions/1588913/how-do-i-merge-two-arrays-in-vba. If you need a formula stackoverflow.com is probably not the right place to ask this since it is not a programming question then. – chiccodoro Jun 11 '14 at 06:25
  • 1
    @chiccodoro formulas are not code? – chancea Jun 11 '14 at 06:27
  • Possible duplicate of [Excel - Combine multiple columns into one column](http://stackoverflow.com/questions/2977660/excel-combine-multiple-columns-into-one-column) While not an exact duplicate, the code contains what you need. – Eric Tobias Jun 11 '14 at 06:27
  • well either is fine as long as it works, though I have never used VBA before... – newBexcel 3724612 Jun 11 '14 at 06:29
  • @EricTobias would this still work if I don't want duplicates? Also, data in A and B would be somewhat random in quantity - that is, there may be anywhere between 10-50 cells used in one row - as I'm going to need to do this multiple times with different data sets. – newBexcel 3724612 Jun 11 '14 at 06:31
  • @chancea I would argue that no, Excel formulas are not in scope for stackoverlfow. That is also confirmed by the one close-vote that was cast so far (I did not cast any). – chiccodoro Jun 11 '14 at 06:32
  • @EricTobias - I don't think that it is a duplicate even if the title reads alike. The question you referenced explicitly says that the OP does not care about duplicates. For sure it can be a good help though. – chiccodoro Jun 11 '14 at 06:34
  • 1
    @chiccodoro while I agree the OP is lacking in showing any effort in solving the question (why I would close it) I had always thought if you had an issue with a formula in excel then stackoverflow would be a good site to ask such a question – chancea Jun 11 '14 at 06:38

2 Answers2

1

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.
Duncan
  • 984
  • 10
  • 20
  • I like the idea, but is possible to sort by using formulas alone? Basically I need to be able to automate this by inputting data without manually sorting stuff after. – newBexcel 3724612 Jun 11 '14 at 06:44
  • You could look at this: http://office.microsoft.com/en-ca/excel-help/delete-duplicate-rows-from-a-list-in-excel-HA001034626.aspx – Duncan Jun 13 '14 at 16:10
  • I don't know of any way to do it using just formulas. I think you would have to use VBA. – Duncan Jun 13 '14 at 16:10
1

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
mucio
  • 7,014
  • 1
  • 21
  • 33
  • I'll give it a try, but I won't be able to get back to you for a bit; I've never used VBA on excel – newBexcel 3724612 Jun 11 '14 at 06:59
  • 1
    You need to read this: http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select – Jean-François Corbett Jun 11 '14 at 07:37
  • @mucio Erm is this how I do it? I edited the range with educated guesses, though I'm not sure I've entered it properly (on "exports and imports test" sheet)http://wikisend.com/download/401988/Sample%20test%202.xls – newBexcel 3724612 Jun 11 '14 at 07:51
  • @newBexcel3724612 sorry, but I don't have access to wikisend from my office. Anyway if you have problem to understand the code try to record a new macro and use the Remove Duplicates functionality – mucio Jun 11 '14 at 10:46