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.

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.

share|improve this question
1  
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

2 Answers 2

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
1  
It’s a bit late now, but since tagged excel-2007/2010 as well as just excel might as well take advantage of the ‘new’ =IFERROR in place of =IF(ISERROR…: =IFERROR(INDEX(A$1:A$5,MATCH($D1,A$1:A$5,0)),"") –  pnuts Jul 13 '12 at 16:07

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.

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

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.