Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Hi I'm not sure whether this is even possible, I have excel with two columns filled with words. In each column there is a word that is not in the other column and there are words which are in both. To illustrate, what I have:

aaa   bbb
bbb   ccc
ddd   ddd
eee   fff

and I want result like this:

aaa   
bbb   bbb
      ccc
ddd   ddd
eee   
      fff

Is this possible?

Thank you!

share|improve this question
    
using a macro, yes. using built in functionality, i am not sure. –  Xantec Mar 25 '11 at 15:03
    
well if I can't do it with buil in func I can do it in .net. I was hoping to save time doing it in excel –  Robert Mar 25 '11 at 15:11

2 Answers 2

You are probably going to need to write a macro for that.

In pseudo code I'm thinking about:

FOR EACH CELL 'C' IN COLUMN 'A'

    FIND CELL 'D' in COLUMN 'B' SO THAT VALUE('C') EQUALS VALUE('D')

    MOVE TOP ROW() OF {'C','D'} AND FOLLOWING DOWN SO THAT ROW('C') EQUALS ROW('D')
share|improve this answer

Here's one approach:

In a new sheet, copy column B underneath column A so that it is one long list. Then Remove all duplicates:

  1. Highlight the whole list
  2. Press ALT+D, F, A
  3. Select 'Copy to another location'
  4. Check 'Unique Records Only'
  5. Select a cell to copy to in the 'Copy to' field.
  6. Click Ok

In the cell to the right of your new unique list use the following formula and copy all the way to the end of the list:

=IF(ISNA(VLOOKUP(D2,$A$2:$A$10,1,0)),"",VLOOKUP(D2,$A$2:$A$10,1,0))

Where:

  1. D2 = The first cell of your unique list.
  2. $A$2:$A$1 = The first column of your original lists.

Next to the cell where you entered the above formula enter this:

=IF(ISNA(VLOOKUP(D2,$B$2:$B$10,1,0)),"",VLOOKUP(D2,$B$2:$B$10,1,0))

Where:

  1. D2 = The first cell of your unique list.
  2. $B$2:$B$1 = The second column of your original lists.

This should give you what you're looking for.

Note: I don't know what version of Excel you have. There are easier ways to do some of these steps in later versions.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.