Taking long list in one column and separating it

**washoutinc** · 04-02-2009, 04:27 PM

I am a newbie to this forum so please excuse me if this has been covered, but I don't think it has.

I deal with long lists of words that are in one column. These lists can get up to 15,000 words.

What I would like to do is take that list and separate it into groups of 40 or 50 so I can then use my data.

Is there a formula or macro or something that can take this lists and separate them for me so I don't have to do it manually?

I really appreciate any help I can get here.

Thanks

**VBA Noob** · 04-02-2009, 04:39 PM

Maybe a macro would help

Try

Sub InsertRow()
Dim LastRow As Long
Dim i As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False
        For i = 51 To LastRow Step 51
            Cells(i, "A").Insert
        Next i
    Application.ScreenUpdating = True
    
End Sub

VBA Noob

**washoutinc** · 04-02-2009, 04:45 PM

Thats awesome.

It does work... but now you're going to kill me because I am going to make it complicated.

By the way I greatly appreciate this help.

Is there a way I can get each subgroup to move into it's own column?

**VBA Noob** · 04-02-2009, 04:52 PM

A before and after example workbook would be useful

Also may not be able to look at it now until the weekend

VBA Noob

**washoutinc** · 04-02-2009, 05:00 PM

Ok I attached a workbook.

The first worksheet is before and the next is after.

Even though the number of words in each column is different and supposed to be the same, I hope it still makes sense.

But again I was hoping there is a relatively easy macro or scrip that can separate the first list (all in one column) into multiple lists (in different columns).

Thanks again...

Dan

**VBA Noob** · 04-02-2009, 05:08 PM

Can't see a pattern

200 rows into 44,42,41,37,35 rows. 5 x 40 would be better

VBA Noob

**Mikeopolo** · 04-02-2009, 05:53 PM

Try the attached worksheet, uses a formula solution.

The formula in B1 contains the number of rows you want in each group, 50 in this example. If you require a different number, change the formula accordingly, copy it down that number of rows, then across as many columns as required (which will be x/y where x is the number of rows of data divided by y which is the number of rows in each column)

(Delete any previous formula cells from rows that you do not require)

hth
regards
Mike