How do I expand columns of items in Excel so that each item is on its own line?

I have this:

  -----A-----|-----B-----|-----C-----
1|cherry     |apple      |banana
2|kiwi       |banana     |cantaloupe
3|peach      |grape      |peach
4|           |peach      |watermelon
5|           |strawberry |

I'd like this:

  -----A-----|-----B-----|-----C-----
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.

JW.

Posted 2012-07-11T18:52:20.863

Reputation: 547

1you 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 – 2012-07-11T20:48:24.390

Not necessarily. I can sort afterwards. – JW. – 2012-07-11T21:37:18.850

Answers

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.

pnuts

Posted 2012-07-11T18:52:20.863

Reputation: 5 716

I ended up doing something like this. Thanks! – JW. – 2012-07-12T03:14:11.967

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]
    Else
     [Perform the other checks on same row]
    End If

    i = i + 1
Wend

etc.

James

Posted 2012-07-11T18:52:20.863

Reputation: 1 185