Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have this:

1|cherry     |apple      |banana
2|kiwi       |banana     |cantaloupe
3|peach      |grape      |peach
4|           |peach      |watermelon
5|           |strawberry |

I'd like this:

1|           |apple      |
2|           |banana     |banana
3|           |           |cantaloupe
4|cherry     |           |
5|           |grape      |
6|kiwi       |           |
7|peach      |peach      |peach
8|           |strawberry |
9|           |           |watermelon

Basically expand each column according to what's in the column, and collate the like elements on their own line. (Assume each column is sorted.)

I've been doing this manually by playing cut-paste Ginsu chef, but was wondering if there's an automated way? (I have Excel 2007 but will be getting Excel 2010 shortly.)

Alternatively, is there another tool better than Excel for this purpose?

Thanks for your time.

share|improve this question
you could probably do this with a VBA sub within Excel if you're familiar with it. Do the items needs to be sorted in any particular way when doing this? – James Jul 11 '12 at 20:48
Not necessarily. I can sort afterwards. – JW. Jul 11 '12 at 21:37
up vote 4 down vote accepted

VBA is not necessary. Create a list of all instances (say ColumnD has rows: apple, banana, cantaloupe, cherry, grape, kiwi, peach, strawberry, watermelon) then insert =IF(ISERROR(INDEX(A$1:A$5,MATCH($D1,A$1:A$5,0))),"",INDEX(A$1:A$5,MATCH($D1,A$1:A$5,0))) into E1. Drag bottom RH corner of E1 to G1. Double click on bottom RH corner of G1. Copy/Paste Values. Delete Columns A:D.

share|improve this answer
I ended up doing something like this. Thanks! – JW. Jul 12 '12 at 3:14

Pseudo-code for now as going to bed, but you could probably do it with some VBA similar to the below.

Get the 'used range' for the sheet using

Dim oSH As Worksheet
Set oSH = ActiveWorkbook.Sheets("MySheetWithList")
Dim data As range
Set data = oSH.UsedRange

Then iterate through values for columns A:C on each row within the used range, cutting-and-pasting when you match values.

    i = 2

While (i < data.Rows.Count)
    A = oSH.range("A" & i).Value2
    B = oSH.range("B" & i).Value2
    C = oSH.range("C" & i).Value2

    If (A = B) Then
     [Move the cells down in columns B and C and reset used range]
     [Perform the other checks on same row]
    End If

    i = i + 1


share|improve this answer
+1 Thank you for the answer! – JW. Jul 12 '12 at 3:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .